Hi,
You can use the following queries to check privileges. I have tested with my created user 'user01'
Check Role Attributes
postgres=# SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication
postgres-# FROM pg_roles
postgres-# WHERE rolname = 'user01';
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication
---------+----------+------------+---------------+-------------+-------------+----------------
user01 | f | t | f | f | t | f
(1 row)
Check Database Privileges:
postgres=# SELECT datname,
postgres-# has_database_privilege('user01', datname, 'CONNECT') AS connect,
postgres-# has_database_privilege('user01', datname, 'CREATE') AS create,
postgres-# has_database_privilege('user01', datname, 'TEMP') AS temp
postgres-# FROM pg_database;
datname | connect | create | temp
-----------+---------+--------+------
postgres | t | f | t
agens | t | f | t
template1 | t | f | f
template0 | t | f | f
(4 rows)
Check Schema Privileges:
postgres=# SELECT nspname,
postgres-# has_schema_privilege('user01', nspname, 'CREATE') AS create,
postgres-# has_schema_privilege('user01', nspname, 'USAGE') AS usage
postgres-# FROM pg_namespace;
nspname | create | usage
--------------------+--------+-------
pg_toast | f | f
pg_temp_1 | f | f
pg_toast_temp_1 | f | f
pg_catalog | f | t
public | t | t
information_schema | f | t
(6 rows)
