Skip to content


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'
     );

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

Posted in database, programming.

Tagged with , .


One Response

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

Continuing the Discussion



Some HTML is OK

or, reply to this post via trackback.



Better Tag Cloud