PostgreSQL: List databases user has privilege to connect

Question

How do I list only the databases the user has CONNECT privilege on PostgreSQL?

For instance, user1 has access to database db1 and user2 has access to db2.

When each of these users login/connect to their databases and they need to retrieve the databases list they can connect with.

Here is what i've done so far:

Created two databases db1 and db2 on my new prostgreSQL server.

CREATE DATABASE db1
    WITH 
    OWNER = admin
    ENCODING = 'WIN1252'
    CONNECTION LIMIT = -1;

CREATE DATABASE db2
    WITH 
    OWNER = admin
    ENCODING = 'WIN1252'
    CONNECTION LIMIT = -1;

I've also created two new users (other than admin) user1 and user2

CREATE USER user1 WITH LOGIN ENCRYPTED PASSWORD 'user1';
CREATE USER user2 WITH LOGIN ENCRYPTED PASSWORD 'user2';

Then I ran the following queries to make user1 as owner of db1 and user2 as the onwer of db2

BEGIN;
ALTER DATABASE db1 owner to user1;
REVOKE CONNECT ON DATABASE db1 FROM public; 
GRANT CONNECT ON DATABASE db1 TO user1;
COMMIT

BEGIN;
ALTER DATABASE db2 owner to user2;
REVOKE CONNECT ON DATABASE db2 FROM public; 
GRANT CONNECT ON DATABASE db2 TO user2;
COMMIT

This way user1 cannot connect to db2 and user2 cannot connect to db1.

Running this query

SELECT datname , (aclexplode(datacl)).grantor,
       (aclexplode(datacl)).grantee,
       (aclexplode(datacl)).privilege_type,
       (aclexplode(datacl)).is_grantable FROM pg_database

gives this:

 datname | grantor | grantee | privilege_type | is_grantable
---------+---------+---------+----------------+--------------
 db1     |   16386 |       0 | TEMPORARY      | f
 db1     |   16386 |   16386 | CREATE         | f
 db1     |   16386 |   16386 | TEMPORARY      | f
 db1     |   16386 |   16386 | CONNECT        | f
 db2     |   16387 |       0 | TEMPORARY      | f
 db2     |   16387 |   16387 | CREATE         | f
 db2     |   16387 |   16387 | TEMPORARY      | f
 db2     |   16387 |   16387 | CONNECT        | f
(8 rows)

omitted template1 and template0 from results

How do I interpret if user1 has access to CONNECT db2 or not?

Is there any other way to achieve this?

Nick Barnes · Accepted Answer · 2017-06-08 03:54:10Z

4

aclexplode().grantee can be joined to pg_user.usesysid to get the name of the user.

However, the simpler solution is to use the access privilege inquiry functions.

To check if user1 can connect to db2:

SELECT has_database_privilege('user1', 'db2', 'CONNECT')

To list all databases which are accessible to user1:

SELECT datname
FROM pg_database
WHERE has_database_privilege('user1', datname, 'CONNECT')

answered Jun 8, 2017 at 3:54

Nick Barnes

21.8k5 gold badges64 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ashwin Over a year ago

Excellent. Is there also a way to avoid the templates coming up in the result?

Ashwin Over a year ago

silly me. I found the way to exclude the templates. Adding filter to where clause worked: and datistemplate = false;

Collectives™ on Stack Overflow

PostgreSQL: List databases user has privilege to connect

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related