[SOLVED] Postgres 15. permission denied for schema public

Issue

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:

How to create tables in public schema?...

newfag

Solution

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.

Answered By - Zegarek

Answer Checked By - Robin (WPSolving Admin)