Check Postgres access for a user

Question

I have looked into the documentation for GRANT Found here and I was trying to see if there is a built-in function that can let me look at what level of accessibility I have in databases. Of course there is:

\dp and \dp mytablename

But this does not show what my account has access to. I would like to see ALL the tables I have access to. Can anyone tell me if there is a command that can check my level of access in Postgres (whether I have SELECT, INSERT, DELETE, UPDATE privileges)? And if so, what would that command be?

`\du`?, or query the grants table in information_schema directly. — Marc B, Nov 13 '14 at 20:14

Mureinik · Accepted Answer · 2017-07-12T18:26:55.517

146

You could query the table_privileges table in the information schema:

SELECT table_catalog, table_schema, table_name, privilege_type
FROM   information_schema.table_privileges 
WHERE  grantee = 'MY_USER'

edited Jul 12 '17 at 18:26

answered Nov 13 '14 at 20:18

Mureinik

297,002
52
306
350

8

And you can only see rows that your db user is allowed to see. – HeyWatchThis Jul 09 '19 at 18:59
It still doesn't tell me whether the use is admin or not? – N. Raj Jan 23 '23 at 07:54

score 5 · Answer 2 · answered Aug 09 '20 at 17:12

5

For all users on a specific database, do the following:

# psql
\c your_database
select grantee, table_catalog, privilege_type, table_schema, table_name from information_schema.table_privileges order by grantee, table_schema, table_name;

answered Aug 09 '20 at 17:12

Basil Musa

8,198
6
64
63

score 3 · Answer 3 · answered Sep 07 '20 at 03:07

Use this to list Grantee too and remove (PG_monitor and Public) for Postgres PaaS Azure.

SELECT grantee,table_catalog, table_schema, table_name, privilege_type
FROM   information_schema.table_privileges 
WHERE  grantee not in ('pg_monitor','PUBLIC');

Check Postgres access for a user

3 Answers3

Linked