PostgreSQL. How to get the base of the current user?

How to get the database to which the user has access? Or vice versa, how to find out from the database which users have access to it?

PostgreSQL has its own distribution of access rights. Which can be divided into two "steps":

• access directly to the server
• access to server objects

Access to server

Access to the server is carried out, in addition to configs, using the mechanism of "roles". In the narrow sense ( we read about the roles ) the “role” can be interpreted as a “user”. Each role gains or does not gain access to the entire database cluster on the server. Information about this can be obtained by the following request:

SELECT rolname AS "User", -- название ролей CASE WHEN rolsuper OR rolcanlogin THEN 'Yes' ELSE 'No' END AS "Access" -- есть ли право для логина? FROM pg_roles; 

Example:

 --------------- User | Access ------+-------- pgsql | Yes User | Yes Testo | No --------------- 

Access to server objects

In this case, we will select objects of the “database” type , and determine which “role” the ACL has for them with the 'CONNECT' type. This can be implemented with the following query:

 WITH bases AS ( SELECT * FROM ( SELECT datname AS "db", (aclexplode(datacl)).grantor AS "grantor", (aclexplode(datacl)).grantee AS "grantee", (aclexplode(datacl)).privilege_type AS "type" FROM pg_database ) AS Query WHERE Query.type = 'CONNECT' ), roles AS ( SELECT * FROM pg_roles ) SELECT DISTINCT * FROM ( SELECT * FROM ( SELECT bases.db AS db, roles1.rolname AS user FROM bases LEFT JOIN roles AS roles1 ON bases.grantor = roles1.oid ) AS one UNION SELECT * FROM ( SELECT bases.db AS db, roles2.rolname AS user FROM bases LEFT JOIN roles AS roles2 ON bases.grantee = roles2.oid ) AS two ) AS res WHERE res.user NOTNULL 

Example:

 ========================= db | user =================+======= database_clients | pgsql template0 | pgsql template1 | pgsql testo | User testo | pgsql testo_final | User =================+=======