The concept of roles subsumes the concepts of “users” and “groups”. In PostgreSQL versions before 8.1, users and groups were distinct kinds of entities, but now there are only roles. Any role can act as a user, a group, or both.
SELECT r.rolname, ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b
ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
WHERE r.rolname NOT IN (‘pg_signal_backend’,’rds_iam’,
‘rds_replication’,’rds_superuser’, ‘rdsadmin’,’rdsrepladmin’)
ORDER BY 1;
PostgreSQL enabled grant permissions directly to the database users. However, as a good practice, it is recommended that you create multiple roles with specific sets of permissions based on application and access requirements. Then assign the appropriate role to each user. The roles should be used to enforce a least privilege model for accessing database objects. The super user should never be used by the application.
The widely used system view for listing the roles in PostgreSQL is shown below.
select rolname from pg_roles;
Here, you can observe the users read1,2,3 are shown as roles.
The most commonly used system view for listing the users is as below.
Now, the main thing how we can map the list of users to tagged roles. We can make use of pg_authmembers view and get details like below.
SELECT r.rolname, ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b
ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r where rolcanlogin =’true’
ORDER BY 1;
We can get the same info more easily if you have psql access .
