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 user 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 is there a way to have a user setup 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.

Upstretched

Answer 1

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

You might need to do REVOKE USAGE ON SCHEMA [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.

Duhl

Thanks fr the comment! I am revoking usage from my test_user, but they're still able to see that the Schema that I revoked usage from within the UI of PgAdmin. The user can see it, but not select anything from it. My ultimate aim here is for them to not even be able to see that the schema is there. – Upstretched

Not sure on that. Depending on your use case, you could create separate database instances on the host, but of course then you can't easily have a master user which can see all of them at once. – Duhl

That's been the only solution I've been able to come up with thus far sadly. I know that this type of access control is possible when using a tool like SSMS, but just haven't been able to sort using PgAdmin/ Postgres. Very annoying thus far. Again, thanks for your help in looking into this! – Upstretched

@Upstretched did you ever come right with restricting user access to a specific schema? I'm also struggling with this – Oao

@Oao - have you been able to get past this problem. I having the same issue. we are on RDS, so REVOKE USAGE ON SCHEMA public FROM public errors out with insufficient privileges as on RDS there is no true superuser. – Glucoside