When are access privileges listed by \l, and when are they not? The access privileges listed by \l can change after a grant and revoke:
$ createuser -EP my_readonly
$ psql development
development=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------------------+----------+----------+-------------+-------------+-----------------------
development | vagrant | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
...
development=# grant usage on schema public to my_readonly;
development=# grant connect on database development to my_readonly;
development=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------------------+----------+----------+-------------+-------------+----------------------------
development | vagrant | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/vagrant +
| | | | | vagrant=CTc/vagrant +
| | | | | my_readonly=c/vagrant
...
development=# revoke connect on database development from my_readonly;
REVOKE
development=# revoke usage on schema public from my_readonly;
REVOKE
development=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------------------+----------+----------+-------------+-------------+-----------------------
development | vagrant | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/vagrant +
| | | | | vagrant=CTc/vagrant
Why is that? What state changed? I believe the my_readonly user's ability to connect was unchanged through this whole psql session (because I'm guessing the PUBLIC role has connect privileges), but clearly something changed: what is that thing?
Side question: how can I explicitly ask postgres whether PUBLIC in fact does have connect privileges (they may have been revoked — see Why can a new user select from any table?)?
Best Answer
The backslash commands in psql are shortcuts for a query or queries that look through the system catalogs. The
\l
command looks at information inpg_catalog.pg_database
, specifically, this query:You can make
psql
show what it is using for the backslash commands by passing the-E
flag to it when you invoke it on the command line.If the permissions on a database or other object are the defaults that PostgreSQL creates them with, the
*acl
column will beNULL
. If you change the defaults, as you have, the ACL column will be populated with information related to theGRANT
and/orREVOKE
statements you have ran.You can see the permissions/ACLs specifically via either
\z
or\dp
If you read further here:
http://www.postgresql.org/docs/9.4/static/sql-grant.html
If you scroll down, (or search for the word
psql
), you can look at the table that shows you how to interpret the ACLs that you see with\l
or in an ACL column.For example:
=Tc/vagrant
means that PUBLIC (the implicit role that contains all roles) has permissions to create temporary tables
T
and connectc
, because the ACL line=xxxxx
denotes permissions applied to PUBLIC, whilerolname=xxxx
applies to that specific role.This presentation from Dalibo should also help clarify this further: Managing Rights in PostgreSQL
Hope that helps. =)