Skip to content

Instantly share code, notes, and snippets.

@axelbdt
Last active November 12, 2024 17:36
Show Gist options
  • Save axelbdt/74898d80ceee51b69a16b575345e8457 to your computer and use it in GitHub Desktop.
Save axelbdt/74898d80ceee51b69a16b575345e8457 to your computer and use it in GitHub Desktop.
# Django and PostgreSQL 15, the rules have changed

Django and PostgreSQL 15, the rules have changed

Postgres 15 is just out, and while there is a lot to love about this new release, you're in for a surprise if you try to set it up with Django following tutorials like this one.

The reason is stated in the release announcement:

Remove PUBLIC creation permission on the public schema (Noah Misch) The new default is one of the secure schema usage patterns that Section 5.9.6 has recommended...

Provided your web app doesn't access your database as a superuser (it shouldn't) and uses a dedicated user, it is not allowed to use the public schema anymore. You have to create one for this specific user, and the next section will show you how.

Moreover, Django will use the public schema by default, and your first attempt to migrate will be greated by the following error:

permission denied for schema public

To prevent this, some additional configuration is needed for Django to access the right schema, which I'll also cover.

Setting up PostgreSQL

PostgreSQL installation

Install postgres following the instructions of their own excellent website. This should imply the creation of a PostgreSQL superuser postgres.

Creating the database, user and schema

Open the PostgreSQL interactive terminal:

sudo -u postgres psql

Enter these SQL commands one by one, to create a database, a user and a schema for your web application.

-- as postgres user
-- Create the database for your application
CREATE DATABASE mydb;
-- Create the dedicated user
CREATE USER myuser WITH PASSWORD 'mypassword';

-- Connect to mydb
\connect mydb;
-- Create a new schema with myuser as owner
CREATE SCHEMA myschema AUTHORIZATION myuser;

-- Set some settings as recommended by the Django documentation
ALTER ROLE myuser SET client_encoding TO 'utf8';
ALTER ROLE myuser SET default_transaction_isolation TO 'read committed';
ALTER ROLE myuser SET timezone TO 'UTC';

To summarize, a database mydb and a user myuser were created. Inside mydb, a schema myschema was created, owned by myuser. We also set some parameters for myuser according to the docs.

Setting up Django

Django installation

You can set up Django however you prefer, but here is a quick rundown of how to create a new project in a virtual env:

mkdir mydir
cd mydir

# Create and activate a virtual environment
python3 -m venv myvenv
source myvenv/bin/activate

# Install Django in the virtual environment, as well as the PostgreSQL driver
pip install django psycopg-binary

# Create a project in the current directory (supposedly mydir)
django-admin startproject myproject .

Configuring PostgreSQL usage

We will modify the settings.py to indicate that Django must use PostgreSQL and provide the connection info according to the previous section. We also have to specify to use our newly created schema myschema, the default public not being allowed anymore.

To my knowledge, Django has poor support for selecting the schema it works with. Fortunately the schema used can be resolved automatically by a PostgreSQL parameter: search_path.

For example, the default search_path is "$user",public, each comma separated value is matched against the schemas the user has access to. The special name "$user" will match the name of the user accessing the database. This means that if you chose the same value (e.g django) instead of myuser and myschema, your schema should be found with the default search_path. In our case, we have to specify the search_path in our Django settings, in addition to the more usual database connection information.

In the file myproject/settings.py, change the value of the DATABASE constant as follows:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'OPTIONS': {
                'options': '-c search_path=myschema'
            },
        'NAME': 'mydb',
        'USER': 'myuser',
        'PASSWORD': 'mypassword',
        'HOST': 'localhost',
        'PORT': '',
    }
}

The keys and values are self-explanatory, note the option setting the search_path to myschema.

Try applying the initial migrations with:

./manage.py migrate

This should fill your screen will little green OKs and your heart with a warm feeling of satisfaction.

@YAmikep
Copy link

YAmikep commented Feb 21, 2023

Thank you for that 🙏

Are you able to run the tests without issues?

I am able to migrate the database but running the tests fail, I get:

django.db.migrations.exceptions.MigrationSchemaMissing: Unable to create the django_migrations table (no schema has been selected to create in
LINE 1: CREATE TABLE "django_migrations" ("id" bigint NOT NULL PRIMA...

It looks like the original issue I was getting when migrating the db so I am confused now that I use the schema in the DB config and it works in the normal flow... 🤔

@YAmikep
Copy link

YAmikep commented Feb 21, 2023

Following up on the above, it looks like it's not new that django does not work with custom schema for the test database.
Creating the schema by overriding the test runner seems to still work.
This solution dates from 2016, I am surprised there is not a more straightforward solution nowadays... 🤔

@axelbdt
Copy link
Author

axelbdt commented Feb 21, 2023

It looks like the original issue I was getting when migrating the db so I am confused now that I use the schema in the DB config and it works in the normal flow...

The test creates it's own database, so this new database doesn't have a schema to work with, like the one we created manually.

Good thing you've found the test runner workaround to create the schema, it might very well be the only option for now. Thanks for following up on this. :)

@esauser
Copy link

esauser commented Aug 19, 2023

@axelbdt any idea how this might work if you upgrade a database instance in place to 15? We're running 14 right now. Everything currently sits in the public schema. I assume when we upgrade it will remain there. It seems accessing it there, without a super user, will not be possible. Are we literally going to have to move every single thing to a new schema?

@jseadragon
Copy link

jseadragon commented Aug 22, 2023

@axelbdt any idea how this might work if you upgrade a database instance in place to 15? We're running 14 right now. Everything currently sits in the public schema. I assume when we upgrade it will remain there. It seems accessing it there, without a super user, will not be possible. Are we literally going to have to move every single thing to a new schema?

Don't quote me on this but I think that when upgrading an existing database you'll maintain your existing permissions (because they are embedded in your pg_dump output). I think this applies for new databases. I'm pretty sure based on what I'm reading.

Obviously do some testing to make sure it works as you expect.

@jacobfnl
Copy link

While trying to set up using AWS RDS, I encountered a problem with the schema creation following this pattern:
CREATE SCHEMA myschema AUTHORIZATION myuser;
The return was ERROR: must be member of role "myuser"

To resolve this, I logged into the database with the user to create the schema. e.g.
psql -d mydatabase -U myuser -h xxxx.xxxxxxxxxxx.us-gov-east-1.rds.amazonaws.com
However, I think I could have used this shortcut:

\c mydatabase
SET SESSION ROLE myuser;

Next, CREATE SCHEMA myschema;

Then, ./manage.py migrate worked flawlessly.

@firdaus-aziz
Copy link

How would this affect implementation of multi-tenant? Anyone have experience with:

@pySilver
Copy link

You can actually set default search path on a different level, so there will be no need to bother with django and this will make sure that every connection will be consistent:

CREATE ROLE myproject WITH LOGIN ENCRYPTED PASSWORD 'myproject';
ALTER ROLE myproject SET search_path TO django, public;
GRANT CONNECT ON DATABASE myproject TO myproject;
GRANT USAGE, CREATE ON SCHEMA django TO myproject;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA django TO myproject;
ALTER DEFAULT PRIVILEGES IN SCHEMA django GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myproject;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA django TO myproject;
ALTER DEFAULT PRIVILEGES IN SCHEMA django GRANT USAGE ON SEQUENCES TO myproject;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment