Permission denied to create table even after using GRANT command

Question

I'm connected to a Postgres 14 instance with postgres role. I have created a new database called airflow and a new user/role called airflow too, who is the owner of the db.

                                        List of databases
   Name    |    Owner    | Encoding |   Collate   |    Ctype    |        Access privileges
-----------+-------------+----------+-------------+-------------+---------------------------------
 airflow   | airflow     | UTF8     | en_HK.UTF-8 | en_HK.UTF-8 |

I have issued the command grant all on database airflow to airflow; And then I have:

                                        List of databases
   Name    |    Owner    | Encoding |   Collate   |    Ctype    |        Access privileges
-----------+-------------+----------+-------------+-------------+---------------------------------
 airflow   | airflow     | UTF8     | en_HK.UTF-8 | en_HK.UTF-8 | =Tc/airflow                    +
           |             |          |             |             | airflow=CTc/airflow

However I still get this error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InsufficientPrivilege) permission denied for schema public
LINE 2: CREATE TABLE log (
                     ^

[SQL:
CREATE TABLE log (
        id SERIAL NOT NULL,
        dttm TIMESTAMP WITH TIME ZONE,
        dag_id VARCHAR(250),
        task_id VARCHAR(250),
        map_index INTEGER,
        event VARCHAR(30),
        execution_date TIMESTAMP WITH TIME ZONE,
        owner VARCHAR(500),
        extra TEXT,
        CONSTRAINT log_pkey PRIMARY KEY (id)
)    
]

(Background on this error at: https://sqlalche.me/e/14/f405)

I'm connecting to the instance as airflow user to the airflow database.

Update: Just saw on pgAdmin that I did not grant all on PUBLIC schema for airflow ...

Can you give us the output of this? select rolname from pg_catalog.pg_roles WHERE CAST(oid AS INT) > 16383 — JustBeingHelpful, Mar 01 '23 at 03:15

Erwin Brandstetter · Answer 1 · 2023-03-01T03:32:57.043

grant all on database airflow to airflow;

Sounds mighty, but does not do all that much. The role still needs additional privileges on schema(s), tables, and possibly more objects inside the database.

In particular, your error message says:

permission denied for schema public

So you need (at least) the CREATE privilege on the schema public. Either directly, or by way of granting it to PUBLIC. Like:

GRANT CREATE ON SCHEMA public TO airflow;

Important updates for Postgres 15! The release notes:

Remove PUBLIC creation permission on the public schema (Noah Misch)

And:

Change the owner of the public schema to be the new pg_database_owner role (Noah Misch)

You can still change that any way you like. It's just the new, safer, more restrictive default. Follow the link for details.
But that does not apply to Postgres 14. You must have removed privileges yourself somehow.

Permission denied to create table even after using GRANT command

1 Answers1