[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:

<user_name>=c/<database_name>

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.

Source: stackoverflow.com

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

Postgres vs Firebird

Representing Sparse Data in PostgreSQL

Restarting postgres

select/display last inserted serial id in postgres

Equivalent of FOUND_ROWS() function in Postgresql

Trouble installing psycopg2 on CentOS

Are there any restrictions on Postgres column alias names?

cursor.execute("INSERT INTO im_entry.test ("+entrym+") VALUES ('"+p+"');")

How to pass parameter to sql 'in' statement?

How create an PostgreSQL connection pool using Java?