CODEWITHSUNDEEP
postgresqlpostgresql-9.1
Krzysztof Miksa
Krzysztof Miksa

Reputation: 1539

Allow only postgres user list roles

How to forbid non superusers to see other users in postgresql server?

ex. If currently logged in user is not a superuser then the result from

SELECT * from pg_roles;

or

\du

should be only rows with his role

Upvotes: 2

Views: 2279

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324721

You can revoke access to the authentication IDs table in the system catalogs:

REVOKE SELECT ON pg_catalog.pg_authid FROM public;
REVOKE SELECT ON pg_catalog.pg_auth_members FROM public;

Note that revoking access to pg_roles is not sufficient, as pg_roles is just a view over pg_authid and it's trivial to run the view query manually or define a new view with the same query. The information_schema views also use pg_authid directly and are unaffected by revoking access to pg_roles. It is not necessary to revoke access to pg_roles if you've revoked access to pg_authid.

Be aware that revoking access to global tables is still a per-database operation.

Revoking access to system catalogs may have side effects, including:

  • Some system functions not working as expected
  • Some metadata operations in tools like the JDBC driver failing
  • ... etc

and is not generally considered supported.

Upvotes: 4

dbenhur
dbenhur

Reputation: 20398

revoke select on pg_roles from public;

Upvotes: 0

Related Questions