List the database privileges using psql

  • I'm in the middle of a database server migration and I can't figure (after googling and searching here) how can I list the database privileges (or all the privileges across the server) on PostgreSQL using the psql command line tool?

    I'm on Ubuntu 11.04 and my PostgreSQL version is 8.2.x.

  • DrColossos

    DrColossos Correct answer

    9 years ago
    postgres=> \l
                                      List of databases
       Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
    -----------+----------+----------+-------------+-------------+-----------------------
     postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
     template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
     template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
    

    The docs on Privileges give an explanation of how to interpret the output. For specific privileges on a table of the current database, use \z myTable.

    `\z myTable` is perfect for ensuring that you've successfully granted access to someone and avoiding looking like an idiot when you say "okay does it work now? it **doesn't???**"

  • perhaps you mean listing users and their privileges for a database - I can't quite tell from the question:

    postgres=> \du
                                 List of roles
        Role name    |  Attributes  |                    Member of
    -----------------+--------------+------------------------------------------------
     dba             | Create role  | {util_user,helpdesk_user,helpdesk_admin}
     helpdesk_admin  | Cannot login | {helpdesk_user}
     helpdesk_user   | Cannot login | {helpdesk_reader}
     jack            |              | {helpdesk_admin}
     postgres        | Superuser    | {}
                     : Create role
                     : Create DB
    

    Nope I wanted a way to list the privileges of a specific database, but I already figured it out. Owner of the database always has all privileges, right? And afterwards we can add more privileges on the database to other users/groups. Those are listed with the \l command. But very thanks anyway.

  • You can do that by following:

    SELECT grantee, privilege_type 
    FROM information_schema.role_table_grants 
    WHERE table_name='mytable'
    

    This gives you this kind of output:

    mail=# select grantee, privilege_type from information_schema.role_table_grants where table_name='aliases';
       grantee    |  privilege_type
    --------------+-----------------
     mailreader   |  INSERT
     mailreader   |  SELECT
     mailreader   |  UPDATE
     mailreader   |  DELETE
     mailreader   |  TRUNCATE
     mailreader   |  REFERENCES
     mailreader   |  TRIGGER
    (7 rows)
    
    mail=#
    

    Welcome to the site! One small question: why did you insert the output as a screenshot? Please use normal text as often as possible.

    Is there a way I can see permission for sequences? This only gives table information

    Note that (as least under Postgres 9.4) the above will not work for materialized views.

    @HimanshuChauhan if I add a new role 'new_role' using role 'mailreader', will the information_schema.role_table_grants list new_role too?

    verified on postgresql 9.5

  • Using psql meta-commands:

    https://www.postgresql.org/docs/current/static/app-psql.html

    Going over the page with Ctrl+F gives:

    \ddp [ pattern ] Lists default access privilege settings.

    \dp [ pattern ] Lists tables, views and sequences with their associated access privileges.

    \l[+] [ pattern ] List the databases in the server and show .... access privileges.

    Also mentioned above, but not found with word "privileges" on the manual page:

    \du+ for roles with login and \dg+ for roles without - will have a filed "Member of" where you find roles granted to roles.

    I deliberately skip function and language privileges here, found in psql manual as barely manipulated (and if you do use those privileges you wont come here for an advise). same for user defined types, domains and so on - using "+" after the meta-command will show you privileges if applicable.


    A little extreme way to check the privileges is dropping the user in transaction, e.g.:

    s=# begin; drop user x;
    BEGIN
    Time: 0.124 ms
    ERROR:  role "x" cannot be dropped because some objects depend on it
    DETAIL:  privileges for type "SO dT"
    privileges for sequence so
    privileges for schema bin
    privileges for table xx
    privileges for table "csTest"
    privileges for table tmp_x
    privileges for table s1
    privileges for table test
    Time: 0.211 ms
    s=# rollback;
    ROLLBACK
    Time: 0.150 ms
    

    When the list is longer than N, (at least in 9.3), warning with list of privileges is collapsed, but you still can find it full in logs...

  • Undercovers psql uses the bellow query when you issue \du command.

    SELECT r.rolname, r.rolsuper, r.rolinherit,
      r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
      r.rolconnlimit, r.rolvaliduntil,
      ARRAY(SELECT b.rolname
            FROM pg_catalog.pg_auth_members m
            JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
            WHERE m.member = r.oid) as memberof
    , r.rolreplication
    , r.rolbypassrls
    FROM pg_catalog.pg_roles r
    WHERE r.rolname !~ '^pg_'
    ORDER BY 1;
    

    is this sql standard?

    ` "ERROR: column r.rolbypassrls does not exist\n\nLINE 9: , r.rolbypassrls\n\n ^\n",` unfortunately does not work

  • A (possibly obvious) additional step is become the postgres user, otherwise you may get errors about roles not existing.

    sudo su - postgres
    psql -l
    

    or

    psql
    postgres=> \l
    

    Meta: I'm adding this because this question is highly rated on the google query "postgres list roles" and I spent a bit of time in much lower ranked results before I found what I wanted, so I'm memorializing the extra info.

  • This is my query composed of multiple answers on this question:

    SELECT grantee AS user, CONCAT(table_schema, '.', table_name) AS table, 
        CASE 
            WHEN COUNT(privilege_type) = 7 THEN 'ALL'
            ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')
        END AS grants
    FROM information_schema.role_table_grants
    GROUP BY table_name, table_schema, grantee;
    

    This results in something like this:

    +------+--------------+----------------+
    | user |    table     |     grants     |
    +------+--------------+----------------+
    | foo  | schema.table | ALL            |
    | bar  | schema.table | SELECT, INSERT |
    +------+--------------+----------------+
    
  • -- file: src/sql/pgsql/list-table-priviledges.sql
    -- usage:
    -- alias psql="PGPASSWORD=${postgres_db_useradmin_pw:-} psql -v -q -t -X -w -U ${postgres_db_useradmin:-}"
    -- psql -d dev_qto < src/sql/pgsql/list-table-priviledges.sql | less
    SELECT grantee, table_name , privilege_type
    FROM information_schema.role_table_grants
    WHERE 1=1
    AND grantee = 'usrqtoapp'
    AND table_name='readme_doc'
    ;
    
    -- purpose:
    -- list the priveledges per user or for user in a database
    -- eof file: src/sql/pgsql/list-table-priviledges.sql
    

    output

    usrqtoapp | readme_doc | INSERT
    usrqtoapp | readme_doc | SELECT
    usrqtoapp | readme_doc | UPDATE
    usrqtoapp | readme_doc | DELETE
    

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM