PostgreSQL: View database connect permissions

Question

How do you view users that have been issued GRANT CONNECT ON DATABASE <database> TO <user>?

\dp - lists table/view permissions
\dn+ - lists schema permissions
\l+ does not list all users that can access the database

Wow this question is old and it seems I never selected an answer. I think that's because at the time I was looking for something more. I haven't looked into this in a while, so I apologize if this is wrong. I'm guessing that the pg_hba.conf file permits users to connect to a server, without an explicit grant being cast. Therefore, databases that haven't been locked down will be able to be accessed by anyone virtually allowed in, which the `\l` command may not ever be able to know. — vol7ron, May 04 '16 at 04:04

score 25 · Answer 1 · edited Apr 06 '21 at 05:58

25

A bit odd if the \l+ command just displays some of the users that have permission/privilege to connect to the database. I could not repeat that myself on a PostgreSQL 8.4 installation (Ubuntu 10.04 LTS). What version are you using?

Anyway, perhaps you could check the table holding the ACL's for that particular database and from that deduce whether the user has the correct privileges or not:

SELECT datname as "Relation", datacl as "Access permissions" FROM pg_database WHERE datname = 'databasename';

If you just want to check one user you could do something like this:

SELECT * FROM has_database_privilege('username', 'database', 'connect');

How are the permissions/privileges to interpreted? The privileges are to be read like this:

user = privileges / granted by

Omitting user means that PUBLIC is granted the privilege, ie all roles. For example if the privilege is =Tc/postgres then all roles may connect and create temporary tables in that particular database and it is the postgres user who granted the privilege.

There is a synopsis at the PostgreSQL site explaining the different privileges: https://www.postgresql.org/docs/current/ddl-priv.html#PRIVILEGE-ABBREVS-TABLE.

rolename=xxxx -- privileges granted to a role
        =xxxx -- privileges granted to PUBLIC

            r -- SELECT ("read")
            w -- UPDATE ("write")
            a -- INSERT ("append")
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
      arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
            * -- grant option for preceding privilege

        /yyyy -- role that granted this privilege

edited Apr 06 '21 at 05:58

HorsePunchKid

848
12
17

answered Jun 03 '11 at 06:24

John P

15,035
4
48
56

John, I'll look into this. I believe I was testing on an 8.4 version as well. I think I may be able to query it, but I was hoping that there would be a shortcut command to do it. Regardless, I'll have to see if I remember the unique case that brought this to my attention. – vol7ron Jun 26 '11 at 18:38
Been a while since I saw this question. 8.4 and no, I know there are accounts that can access the database that aren't being shown in the list...unless... Question: what does it mean when no user is listed before the equal sign in `=Tc/`? Does that mean anyone has Temporary connect? – vol7ron Apr 02 '12 at 18:54
Yes. Good question by the way, I've updated the answer how the privileges are to interpreted. – John P Oct 01 '12 at 04:50
I'll have to revisit the issue I noticed. IIRC I wanted to see users that were explicitly given the ability to connect to a specific database. This wasn't easily found. I'll have to check again, but I think if you are a user on one database, you can connect to another database on the same postgres server. – vol7ron Oct 01 '12 at 05:09
how about accepting an answer or answering yourself then @vol7ron ? – Cpt. Senkfuss Apr 23 '15 at 16:30
@Cpt.Senkfuss welcome to StackOverflow! As a new user, you should understand that answers should not be selected only to close a question. Instead, they should be selected when an answer appropriately answers the question. If an answer provides helpful information, or contributes to answering the question, an up-vote can be given; as I have done above. Also, comments should be addressing the answer, not the OP, unless the comment involves both the OP and answer/answerer. If you have further questions, please seek advice from http://meta.stackoverflow.com (and welcome again!) – vol7ron Apr 23 '15 at 17:29
so by "this wasn't easily found" in your comment above, you actually meant to say that it wasn't found at all, and that you never found out how to view those users? – Cpt. Senkfuss Apr 23 '15 at 17:37

score 0 · Answer 2 · answered Apr 19 '11 at 21:59

0

I'm using psql from postgres 8.4 and postgres 9.0, and the command \l or \l+ gives me column Access Privileges where I have entry:

<user_name>=c/<database_name>

and earlier I gave the user the connect privilege as you wanted.

As it states on the page http://www.postgresql.org/docs/9.0/static/sql-grant.html, the c letter here means Connect.

answered Apr 19 '11 at 21:59

Szymon Lipiński

27,098
17
75
77

Right, but it's not listing all the users. I'm thinking this might be a limitation. Or perhaps the pg_hba.conf is doing something first. – vol7ron Apr 19 '11 at 22:09

PostgreSQL: View database connect permissions

2 Answers2