How To Check PostgreSQL Privileges

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.

The Solution

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

The has_table_privilege function

The 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 pg_catalog and information_schema which exist in every PostgreSQL database, and I’m not interested in them, so I just remove them from the result dataset.