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.

auvipy/django-postgresql-15.md