PostgreSQL permissions explained

Question

Please explain the output of the \z command in PostgreSQL. I understand the permission, I read the documentation, but somehow I missed the interpretation of the output of \z.

datastore_default=> \z                                      Access privileges  Schema |      Name       | Type  |         Access privileges         | Column access privileges  --------+-----------------+-------+-----------------------------------+--------------------------  public | _table_metadata | view  | ckan_default=arwdDxt/ckan_default+|          |                 |       | datastore_default=r/ckan_default +|          |                 |       | readonlyuser=r/ckan_default      +|   public | foo             | table | ckan_default=arwdDxt/ckan_default+|          |                 |       | datastore_default=r/ckan_default +|          |                 |       | readonlyuser=r/ckan_default      +|  

Somehow readonlyuser seems to be able to read tables foo and _foo but in practice it cannot. Both commands return an error:

sudo -u postgres psql -d datastore_default -U readonlyuser -c 'SELECT * FROM foo' sudo -u postgres psql -d datastore_default -U readonlyuser -c 'SELECT * FROM public.foo' ERROR:  permission denied for schema public LINE 1: SELECT * FROM public.foo 

Edit: apparently I had a poor understanding of how database and schema permissions work. First of all only the db admin (user postgres) or the owner of the database (in my case user ckan_default) can grant other users privileges on a specific database. The schema is only at a database level, so it's ok that I added readonlyuser the permission to see the public schema, it cannot select from other databases anyway.

Answer 1

The error says permission denied for schema public (emphasis mine)

You need to give readonlyuser rights on schema public:

GRANT USAGE ON SCHEMA public TO readonlyuser; 

The contents of the ACL is explained on this page. The most relevant part quoted here:

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 

The + are part of the way psql formats the result, they are not part of the value.