CODEWITHSUNDEEP
sqlpostgresqlpgadminpostgresql-15
Merkalov Anton
Merkalov Anton

Reputation: 853

Postgres 15. permission denied for schema public

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?

Upvotes: 75

Views: 104448

Answers (4)

noonex
noonex

Reputation: 2075

For those who is still struggling: public schema is in every database, so you must make sure you connect to the right one when granting access, e.g.

# sudo -u tst psql -c 'create table xxx as select 1'
ERROR:  permission denied for schema public
# sudo -u postgres psql -c 'GRANT all ON SCHEMA public TO tst'
GRANT
# sudo -u tst psql -c 'create table xxx as select 1'
ERROR:  permission denied for schema public
# sudo -u postgres psql -c 'GRANT all ON SCHEMA public TO tst' tst
GRANT
# sudo -u tst psql -c 'create table xxx as select 1'
SELECT 1

Upvotes: 0

swaap
swaap

Reputation: 71

For me the soln was changing the directory to createdDB.

//script.sql

\c postgres; // adding this line was the fix
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE USER test PASSWORD 'test' NOSUPERUSER CREATEDB CREATEROLE INHERIT;
GRANT CONNECT ON DATABASE postgres TO test;
GRANT USAGE ON SCHEMA public TO test;
GRANT CREATE ON SCHEMA public TO test;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO test;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO test;

SELECT pg_catalog.has_schema_privilege('test', 'public', 'USAGE') AS has_usage,pg_catalog.has_schema_privilege('test', 'public', 'CREATE') AS has_create;`

Upvotes: 4

Zegarek
Zegarek

Reputation: 26347

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.

Upvotes: 182

JGH
JGH

Reputation: 17906

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

Upvotes: 1

Related Questions