Postgres: Restricting users ability to view schemas

Question

I am currently working within Postgres, and am in the process of creating some users. Whilst creating these users and testing them I've noticed that they're able to view more Schemas than they have access to. In addition to this, they can view restricted Schemas tables, views, and functions. This isn't ideal.

When creating users and their permissions are there a way to have a user set up in such a way that they're only able to view certain Schemas and not all Schemas at large within our database?

I should also mention that these users would be viewing our Postgres database utilizing either PgAdmin or Tableau.

Answer 1

• Yes. Use the command GRANT USAGE ON [schemaname] TO [username] or REVOKE USAGE ON [schemaname] FROM [username] to control access to the Schema itself.

• You might need to do REVOKE USAGE ON [schemaname] FROM public to remove the default access permissions as well.

• I suggest reviewing https://www.postgresql.org/docs/current/static/sql-grant.html for the full set of GRANT commands available as you may need to grant/revoke read/write access on some tables as well.