Someone asked me recently how to list all tables that a given user can select from. The problem they had was that they had a special user used by a client, so the client can make its own sql queries. However due to the security policy, that user can only perform
SELECT queries and only from some tables.
They wanted to give the client a list of tables which can be selected.
Let’s assume the user name is:
super_me. In fact it really doesn’t matter too much.
Such a report can be generated using this simple query:
SELECT schemaname||'.'||tablename FROM pg_tables WHERE has_table_privilege ( 'super_me', schemaname||'.'||tablename, 'select' ) AND schemaname NOT IN ( 'pg_catalog', 'information_schema' );
The Query Explanation
The pg_tables View
There is a special PostgreSQL view named
pg_tables. It contains information about all the tables in the database. I’m only interested in the full table names, so I will use only the first two columns. One is
schemaname and the second is
The has_table_privilege function
has_table_privilege function returns
true if user has the privilage for the given table. So it will return
true for all the tables I’m interested in.
Filtering Standard Schemas Out
There are schemas like
information_schema which exist in every PostgreSQL database, and I’m not interested in them, so I just remove them from the result dataset.