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.
Install postgres following the instructions of their own excellent website. This should imply the creation of a PostgreSQL superuser postgres
.
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.
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 .
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 OK
s and your heart with a warm feeling of satisfaction.
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:
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... 🤔