Postgres15 Grant All on schema public not workiing

Question

My code used to work fine with postgres 12, now with postgres 15 my django project can't run migrations. I found many answers suggesting to GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO <user>, but it does not work for me as shown below.

This is the error I get:

lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.errors.InsufficientPrivilege: permission denied for schema public
LINE 1: CREATE TABLE "django_migrations" ("id" smallint NOT NULL PRI...

Below the user named django needs to be able to create tables in the public schema, so it can run migrations:

sudo -u postgres psql
could not change directory to "/home/john/project/src": Permission denied
psql (15.6 (Debian 15.6-0+deb12u1))
Type "help" for help.

postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO django;
GRANT
postgres=# 
\q

(project) john@vps-demo:~/project/src$ psql -U django my_db
Password for user django: 
psql (15.6 (Debian 15.6-0+deb12u1))
Type "help" for help.

my_db=> CREATE TABLE test_table1 (id INT);
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE test_table1 (id INT);
                     ^
my_db=> 

And verifying the permissions:

User
# sudo -u postgres psql -c "\dn+ public"
could not change directory to "/home/john/project/install": Permission denied
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description       
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                  +| 
        |                   | django=UC/pg_database_owner           +| 
        |                   | john=UC/pg_database_owner          | 
(1 row)

Answer 1

You are granting django all the permissions to the public schema/namespace in the "postgres" database not the "my_db" database/catalog.

In the first command you are logged into the "postgres" database/catalog. In the second one you are trying to create a table in the "my_db" database/catalog

So if you want to grant "django" access to "my_db" you would need to:

1. Find the owner of the database. Which you can do with \l in psql
2. Log into that account (or an account that has the ability to grant all the privileges you want to give. But beware of privilege inheritance and how it works/use a role)
3. Connect to the "my_db" with \c "my_db"
4. And re-run the original command

Also as a side note if "django" is supposed to be the sole user of the database/catalog, might as well make them the owner