Postgres 15. permission denied for schema public

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

No amount of GRANTing works on database postgres in regards to user admin's privileges on schema public in mydb. Schema public on postgres db is separate and distinct from schema public on the newly created mydb.

This doesn't help:

GRANT ALL ON DATABASE mydb TO admin;

It grants privileges on the database itself, not things inside the database. It lets admin drop that database, still without being able to create tables in it. To make admin also the owner of mydb:

ALTER DATABASE mydb OWNER TO admin;

Or, at creation time:

CREATE DATABASE mydb OWNER admin;

Or repeat GRANT USAGE, CREATE ON SCHEMA public TO admin; while connected to mydb, if they're supposed to have all that access without being the owner.

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

---

Depending on what db client/IDE you are in, you might need to open 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 this way:

select current_database(), current_user;

If you're using psql client, the "postgres CLI", command-line client, use \c mydb.