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?
2
Answers
You have created the DB after having granted the privileges on the
public
schema. Chances are youradmin
user is using the new DB, which only have the default priviledgesThe first comment nailed the most likely reason this is happening. Quoting the release announcement:
The reason your fix didn’t work is that all actions you took on database
postgres
in regards to useradmin
‘s privileges on schemapublic
concern only that schema within the databasepostgres
. Schemapublic
on databasepostgres
is not the same schemapublic
as the one on newly createdmydb
.Also, this:
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 schemapublic
. My guess is that you wanted to makeadmin
also the owner ofmydb
, in which case you need to addOr you need to repeat your
GRANT USAGE, CREATE ON SCHEMA public TO admin;
onmydb
.Here’s some more documentation on secure schema usage patterns the PostgreSQL 15 change was based on.