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 better...
2. PostgreSQL Versioning Policy I was explaining the PostgreSQL version naming convention to my...
3. How To Store UUIDs in PostgreSQL This story is a just another example of what I...
4. How to Allow For Max 4 Rows in Update (PostgreSQL) The Problem Question found on the net: how to restrict...
5. How to Store Application Settings Some ideas on how to store application settings in a...
6. PostgreSQL FOUND Problem FOUND is a global variable that exists in the plpgsql...