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.

I have added some details on reading the ACL to my answer.

Eelke
– Eelke

2014-09-09 08:49:29 +00:00
Commented Sep 9, 2014 at 8:49 — Eelke
– Eelke, Commented Sep 9, 2014 at 8:49

phanaz · Accepted Answer · 2023-02-10 20:41:55Z

58

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.

edited Feb 10, 2023 at 20:41

phanaz

1,71312 silver badges23 bronze badges

answered Sep 5, 2014 at 18:06

Eelke

22.3k5 gold badges57 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

ddreian Over a year ago

Thank you, but it still does not seem to work.

sudo -u postgres psql -d datastore_default -U ckan_default -c 'GRANT USAGE ON SCHEMA public TO readonlyuser;' WARNING:  no privileges were granted for "public" GRANT

. \z prints the same thing. I have PostgreSQL 9.1.14 (Ubuntu 12.04 LTS)

Eelke Over a year ago

I think it gives that message when the user with which you are logged in does not have sufficient rights to grant those rights. You should have GRANT privileges or are the owner of public or be superuser.

ddreian Over a year ago

thank you. it is clear now. I had the impression that the + has some special meaning.

tamjd1 Over a year ago

I have a postgres user who is the owner of a table which it can't truncate... I see in the ACL for that user it is: arwdRxt. What does it mean and how is it different from arwdDxt? My assumption is that it is because of D vs. R where D stands for TRUNCATE.

Tomáš Pospíšek Over a year ago

The psql_access_priv_decoder is a tool that you can feed the output of psql and that will explain you the access privileges in clear text.

|

Paku · Accepted Answer · 2024-05-03 05:12:36Z

PostgreSQL has 3 layers of objects (Database, Schema, objects e.g. Tables) and also implicit grants given to DB object owners

That means that in order to select from a table we need

CONNECT on the Database
USAGE on the Schema (Given implicitly to schema owner)
SELECT on the Table (Given implicitly to table owner)

To see these privileges:

Use \l+ to see privileges of Database
Use \dn+ to see privileges of Schemas
Use \dp+ to see privileges of Tables

Privileges are seen here

We also need to understand acl entries of the following format:

grantee=privilege-abbreviation[*]/grantor

e.g. in the following \dp+ acl example user has been given all permissions by postgres role

user=arwdDxt/postgres

If the acl entry is empty, it means the object has default owner privileges (all privileges)

If the “grantee” column is empty for a given object, it means that privileges are given to PUBLIC role (every role that exists)

=UC/postgres

Also it's confusing when Public schema is used. You have CREATE permission on schema so when the tables are created with the same user you select data with and you have owner permissions out of the box.

Furthermore. Before you can do any of the above you must connect to the Cluster. This means connecting using a role with LOGIN permission using a valid password and SSL.

Collectives™ on Stack Overflow

PostgreSQL permissions explained

2 Answers 2

7 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Related