Check user permission in PostgreSQL database

Check the user access in PostgreSQL database

Check user specific permission in PostgreSQL

SELECT table_catalog, table_schema, table_name, privilege_type
FROM   information_schema.table_privileges 
WHERE  grantee = 'USER_NAME'

SELECT table_catalog, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
where grantee = 'postgres' and table_catalog = 'hello'

table_catalog |    table_schema |  table_name  | privilege_type
 hello         | public         | actor       | INSERT
 hello         | public         | actor       | SELECT
 hello         | public         | actor       | UPDATE
 hello         | public         | actor       | DELETE
 hello         | public         | actor       | TRUNCATE
 hello         | public         | actor       | REFERENCES
 hello         | public         | actor       | TRIGGER

Check all user on a specific database with privileges assigned:

select grantee, table_catalog, privilege_type, table_schema, table_name 
from information_schema.table_privileges order by grantee, table_schema, table_name;

Check the table permission in PostgreSQL

SELECT privilege_type, is_grantable
    FROM information_schema.role_table_grants
        table_schema = 'public'
        AND table_name = 'actor'
        AND grantee = 'postgres';
privilege_type | is_grantable
 INSERT         | YES
 SELECT         | YES
 UPDATE         | YES
 DELETE         | YES
 TRIGGER        | YES

Check user present in PostgreSQL Server:

hello-# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 user1     |                                                            | {}

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.