CODEWITHSUNDEEP
postgresqlprivilegessql-grant
moth
moth

Reputation: 2339

Permission denied to create table even after using GRANT command

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 ...

enter image description here

Upvotes: 1

Views: 3709

Answers (2)

helderam
helderam

Reputation: 37

You can change the owner of database to the new user:

ALTER DATABASE airflow OWNER TO airflow;

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656221

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.

Related:

Upvotes: 5

Related Questions