Check Postgres access for a user
PostgresqlPrivilegesData DictionaryPostgresql Problem Overview
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?
Postgresql Solutions
Solution 1 - Postgresql
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'
Solution 2 - Postgresql
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;
Solution 3 - Postgresql
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');