1

Is there a system view in Postgres where I can see which users/roles have "grant usage" and on which schema?

XerX
  • 51
  • 6

1 Answers1

2

The grants are stored in ACLs (=Access Control Lists) for each schema. You can view them by looking a pg_namespace

select nspname, nspacl 
from pg_namespace

The format of the acl is explained in the manual

  • Thank you! I noticed now the same is also available with \dn+ Must be selecting from the same place - pg_namespace. – XerX Jul 01 '22 at 15:01
  • @XerX: if you start `psql` with the option `--echo-hidden` you can see the queries that it uses to generate that output –  Jul 01 '22 at 15:03
  • yep: postgres=> \dn+ ********* QUERY ********** SELECT n.nspname AS "Name", pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner", pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges", pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description" FROM pg_catalog.pg_namespace n WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ORDER BY 1; – XerX Jul 01 '22 at 15:18