Check the user role membership

[Deleted User]

This tip was authored by Jim Knicely.

The HAS_ROLE function returns a Boolean value that indicates whether a role has been assigned to a user.

Example:

To create a read-only user and role, do the following:

dbadmin=> CREATE ROLE read_only_role;
CREATE ROLE

dbadmin=> CREATE USER read_only;
CREATE USER

dbadmin=> GRANT read_only_role TO read_only;
GRANT ROLE

To verify that grant, query the V_CATALOG.GRANTS system table:

dbadmin=> SELECT object_name, grantee, object_type
dbadmin->   FROM grants
dbadmin->  WHERE grantee = 'read_only'
dbadmin->    AND object_type = 'ROLE';
  object_name   |  grantee  | object_type
----------------+-----------+-------------
 read_only_role | read_only | ROLE
(1 row)

But it’s a lot easier to use the HAS_ROLE function:

dbadmin=> SELECT has_role('read_only', 'read_only_role');
 has_role
----------
t
(1 row)

Have Fun!