Postgres 15. permission denied for schema public

Question

Can't create tables in public schema as non-superuser

postgres - super user.

What I've done:

ALTER SCHEMA public owner to postgres;  

CREATE USER admin WITH PASSWORD 'my-password';   

GRANT USAGE, CREATE ON SCHEMA public TO postgres;   
GRANT USAGE, CREATE ON SCHEMA public TO admin;    

CREATE DATABASE mydb;    
GRANT ALL ON DATABASE mydb TO admin;

privileges:

postgres=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres        +| 
        |          | admin=UC/postgres    | 
(1 row)

what I got:

enter image description here

How to create tables in public schema?

https://www.postgresql.org/about/news/postgresql-15-released-2526/ `PostgreSQL 15 also revokes the CREATE permission from all users except a database owner from the public (or default) schema` — Richard Huxton, Oct 18 '22 at 12:15
"postgres" owner database. "postgres" owner schema "public". \dn+ shows that the admin has full access to the schema, but that doesn't work for me. — Merkalov Anton, Oct 18 '22 at 12:21

Zegarek · Accepted Answer · 2023-01-22T19:32:21.270

73

The first comment nailed the most likely reason this is happening. Quoting the release announcement:

PostgreSQL 15 also revokes the CREATE permission from all users except a database owner from the public (or default) schema.

The reason your fix didn't work is that all actions you took on database postgres in regards to user admin's privileges on schema public concern only that schema within the database postgres. Schema public on database postgres is not the same schema public as the one on newly created mydb.

Also, this:

GRANT ALL ON DATABASE mydb TO admin;

grants privileges on the database itself, not things within the database. admin can now drop the database, for example, still without being able to create tables in schema public. My guess is that you wanted to make admin also the owner of mydb, in which case you need to add

ALTER DATABASE mydb OWNER TO admin;

Or you need to repeat your GRANT USAGE, CREATE ON SCHEMA public TO admin; on mydb.

Here's some more documentation on secure schema usage patterns the PostgreSQL 15 change was based on.

edited Jan 22 '23 at 19:32

answered Oct 18 '22 at 13:14

Zegarek

6,424
1
13
24

1

Thank you! I understand that public is different. How can i execute the command: `GRANT USAGE, CREATE ON SCHEMA public TO admin;` For the public schema on "mydb"? Sorry if my question is stupid) `ALTER DATABASE mydb OWNER TO admin;` This work, but not what I was looking for) – Merkalov Anton Oct 18 '22 at 13:36
Depending on what db client/IDE you are in, you might need to create a new connection. However you connected to database `postgres` at the moment, you should do the same again and just replace the database name in connection settings to `mydb`. Once you're in, you can make sure by issuing `select current_database(), current_user;` – Zegarek Oct 18 '22 at 13:40
Thank you very much. It works! `postgres=# \c myDB` and `myDB=# GRANT USAGE, CREATE ON SCHEMA public TO admin;` – Merkalov Anton Oct 18 '22 at 13:53
Special thanks, I was stuck for two days on this issue, the `keycloak` was not able to access to the `public schema`. – Askar Oct 26 '22 at 09:17
You also may need `GRANT ALL PRIVILEGES ON TABLESPACE tsname TO username;` – Putnik Jul 28 '23 at 17:08

score 1 · Answer 2 · answered Oct 18 '22 at 13:12

1

You have created the DB after having granted the privileges on the public schema. Chances are your admin user is using the new DB, which only have the default priviledges

answered Oct 18 '22 at 13:12

JGH

15,928
4
31
48

Postgres 15. permission denied for schema public

2 Answers2