List the database privileges using psql

Question

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.

score 206 · Accepted Answer · edited Apr 15 '20 at 17:17

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???**" — ijoseph, Jul 17 '18 at 17:50

score 178 · Answer 2 · answered Aug 04 '11 at 15:59

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. — pedrosanta, Aug 18 '11 at 16:20

score 175 · Answer 3 · edited Jun 26 '19 at 12:25

175

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=#

edited Jun 26 '19 at 12:25

Levi Arista

103
3

answered Oct 18 '16 at 07:11

Himanshu Chauhan

1,851
1
8
3

Note that (as least under Postgres 9.4) the above will not work for materialized views. – Seldom 'Where's Monica' Needy Apr 14 '17 at 23:24
Note that you may need to run these as postgres user, or else some grants may not appear (e.g. `\dp` tells you that a specific user has access to a table, but this select would not select the related grants). – Jānis Elmeris Aug 10 '21 at 21:05

score 38 · Answer 4 · edited Dec 04 '18 at 16:31

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...

In v12 `\du+`and `\dg+`seems to return same results. I have 2 roles with `NOLOGIN`option and both are listed with both commands. Checked with `\?`and it gives same description for both commands so I guess the role-with-login vs user distinction is no longer available. — EAmez, Mar 18 '21 at 14:16

score 36 · Answer 5 · answered Apr 06 '18 at 10:55

36

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;

answered Apr 06 '18 at 10:55

Denys

473
4
8

` "ERROR: column r.rolbypassrls does not exist\n\nLINE 9: , r.rolbypassrls\n\n ^\n",` unfortunately does not work – ribamar Dec 31 '18 at 15:16
source? whats the query for `\l`? – milahu Dec 14 '21 at 15:13

score 24 · Answer 6 · answered Nov 09 '20 at 17:12

This is how you can list all privileges of a role (grantee):

SELECT grantor, grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'myuser'

Will result in:

 grantor  | grantee  | table_schema | table_name | privilege_type
----------+----------+--------------+------------+----------------
 postgres | myuser   | myapp        | employees  | INSERT
 postgres | myuser   | myapp        | employees  | SELECT
 postgres | myuser   | myapp        | employees  | UPDATE
 postgres | myuser   | myapp        | employees  | DELETE

Works in PG 10

1

Works also in Pg 12. – RicarHincapie Apr 05 '21 at 22:54

score 13 · Answer 7 · edited Dec 29 '15 at 18:06

13

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

edited Dec 29 '15 at 18:06

Community

1

answered Dec 18 '15 at 19:03

Adam Shostack

231
2
2

score 12 · Answer 8 · answered Mar 04 '20 at 16:27

12

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 |
+------+--------------+----------------+

answered Mar 04 '20 at 16:27

stefannienhuis

121
1
3

3

Getting this error in pg 9.4 - ERROR: could not find array type for data type information_schema.character_data – Adam Mulla Nov 04 '20 at 07:40
gr8 job with aggregation. – samshers Oct 26 '21 at 06:33

score 3 · Answer 9 · answered Jun 21 '20 at 14:49

3

You can input by following:

SELECT * FROM pg_roles;

and you will get

rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid

maybe here you will think that there are too many roles, and this time, you can use WHERE to select what role you want to see

SELECT * FROM pg_roles WHERE rolname='your role name';

In fact, you can also just input

\du

and you will see all roles you created instead of default

answered Jun 21 '20 at 14:49

Ricky Xu

31
2

1

How is this different from existing answers posted earlier? – mustaccio Jun 21 '20 at 14:56
Here as a supplement to the previous, I think, the previous answers were missing this part or not concise enough. – Ricky Xu Jun 21 '20 at 15:09

milahu · Answer 10 · 2021-12-14T15:50:16.040

2

list database owners

select d.datname, r.rolname
from pg_catalog.pg_database d, pg_catalog.pg_roles r
where d.datdba = r.oid;

docs: pg_database and pg_roles

related: test for a specific privilege:

select has_database_privilege('dbname', 'CREATE');

docs: has_database_privilege (via)

edited Dec 14 '21 at 15:50

answered Dec 14 '21 at 15:28

milahu

121
3

List the database privileges using psql

10 Answers10

Linked