2

Below are the database privileges after I created a brand new database "foobar".

\l+ 
                                                    List of databases
  Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |   Size    | Tablespace |                Description                 
----------+----------+----------+-------------+-------------+-----------------------+-----------+------------+--------------------------------------------
foobar    | myuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7233 kB   | pg_default | 
postgres  | myuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7343 kB   | pg_default | default administrative connection database
template0 | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin          +| 7233 kB   | pg_default | unmodifiable empty database
          |          |          |             |             | rdsadmin=CTc/rdsadmin |           |            | 
template1 | myuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/myuser            +| 7233 kB   | pg_default | default template for new databases
          |          |          |             |             | myuser=CTc/myuser     |           |            | 

It has no access privileges listed, but I am able to connect to it with any user because apparently public is granted connect access to all databases, as shown in this question. After revoking connect from public on foobar, it shows the (accurate?) access privs.

 foobar    | myuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =T/myuser            +| 7233 kB   | pg_default | 
           |          |          |             |             | myuser=CTc/myuser     |           |            | 

I assume this is done to make the output of \l+ commands less verbose. I noticed that a similar thing happens with \du, when nothing is shown when a role has LOGIN but does show something for NOLOGIN.

Without full privileges listed explicitly, it is hard for a beginner like me to figure out what the 'effective' privileges is.

So, question: how do I get commands like \l+ to show the complete set of privileges?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.