How to check PostgreSQL privileges.

Someone asked me how to list all tables that a given user can select from. They’ve got a special user for a client, so the client can make its own sql queries but due to the security policy, that user can only select and only from some tables. They wanted to give the client a list of tables that can be selected.

Let’s name the user: ’super_me’. 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'
     );

Bookmark It

Related posts:

1. PostgreSQL Wishlist PostgreSQL is a great database, in my opinion much...
2. How to Allow For Max 4 Rows in Update (PostgreSQL) The Problem Question found on the net: how to restrict...
3. PostgreSQL FOUND Problem FOUND is a global variable that exists in the plpgsql...
4. PostgreSQL Strange Timings A very simple query. PostgreSQL 8.4. I have no idea...
5. PostgreSQL Collation – part 1 PostgreSQL is (IMHO) much better than MySQL but unfortunately it ...
6. PostgreSQL Collation – part 2 Due to many questions about the PostgreSQL collation that were...