[ACCEPTED]-PostgreSQL: View database connect permissions-postgresql

Accepted answer
Score: 24

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

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

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

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

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

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

user = privileges / granted by 

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

There 2 is a synopsis at the PostgreSQL site explaining 1 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
Score: 0

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


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

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

More Related questions