user cannot create table on schema public Postgres 16

Question

I have to create a table in Postgres public schema for Airflow implementation. When I create a table with airflow user I get this error

permission denied for schema public
LINE 1: CREATE TABLE public.PERSONS(

if I run in psql

\dn+ public

I get

                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description       
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                  +| 
        |                   | airflow=UC/pg_database_owner           | 
(1 row)

At this stage, I am not understanding what privileges I might be missing for the user after running

CREATE USER airflow PASSWORD
GRANT USAGE ON SCHEMA public TO airflow
GRANT ALL ON SCHEMA public TO airflow

I cannot replicate. To your question add the output of select current_user, session_user; in the session you do the CREATE TABLE. — user7070613
– user7070613, Commented Jun 12, 2024 at 14:46
This is the output of "select current_user, session_user;" ('airflow', 'airflow') — Eliseo Di Folco
– Eliseo Di Folco, Commented Jun 13, 2024 at 7:26
As I said I cannot replicate, so it must be something with your setup/usage. More information needed: 1) OS and version. 2) Where did you install Postgres from? 3) Do you have more then one instance of Postgres running? 4) Is all the above being done in one session and where the commands committed? Add answers as text update to question text, along with information in your commnet. — user7070613
– user7070613, Commented Jun 13, 2024 at 14:49
Eventually it was due to permission on the airflow database. In version 14 GRANT ALL PRIVILEGES ON DATABASE airflow TO airflow was enough, but in Postgres 16 I needed to assign ownership to the airflow user for him to be able to create in the public schema. There should be a better option than granting ownership though — Eliseo Di Folco
– Eliseo Di Folco, Commented Jun 19, 2024 at 10:24
This is due to the change PG 15 release Notes: Remove PUBLIC creation permission on the public schema. Users/roles start out with the permissions granted the PUBLIC role and that was what allowed your user to create objects in the public schema. Now(15+) you have to specifically add the CREATE privilege for a role/user. What is confusing is that this airflow=UC/pg_database_owner shows that privilege(C) granted. For more information see Privileges. — user7070613
– user7070613, Commented Jun 19, 2024 at 15:02

Frank Heikens · Accepted Answer · 2024-06-12 13:33:42Z

1

CREATE is missing:

GRANT CREATE ON SCHEMA public TO airflow;

answered Jun 12, 2024 at 13:33

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

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

1 Comment

Eliseo Di Folco Over a year ago

I did run that too but it does not work. You should see from airflow=UC/ from \dn+ public

Collectives™ on Stack Overflow

user cannot create table on schema public Postgres 16

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related