Postgres 15 requires a Schema during installation

[Alef-Burzmali]

Change Type

Correction

Area

Installation/upgrade

Proposed Changes

With the next Debian release coming with Postgres 15 as the default version, there is an interesting change for the configuration of the database for Django. With the current installation steps, you have a "permission denied for schema public" error during the initial migration.

I'll paraphrase my source: https://gist.github.com/axelbdt/74898d80ceee51b69a16b575345e8457

In Postgres 15 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...

In practice, it means that the configuration block in configuration_example.py for the database should be changed to:

DATABASE = {
    'ENGINE': 'django.db.backends.postgresql',  # Database engine
    'NAME': 'netbox',         # Database name
    'USER': '',               # PostgreSQL username
    'PASSWORD': '',           # PostgreSQL password
    'HOST': 'localhost',      # Database server
    'PORT': '',               # Database port (leave blank for default)
    'CONN_MAX_AGE': 300,      # Max database connection age
    'OPTIONS': {
        'options': '-c search_path=netbox',
    }
}

and in the instructions to create the database in PostgreSQL Database Installation:

CREATE DATABASE netbox;
CREATE USER netbox WITH PASSWORD 'J5brHrAXFLQSif0K';
\connect netbox;
CREATE SCHEMA netbox AUTHORIZATION netbox;

As far as I know, these changes are also compatible with Postgres <15. Also, I have not found any bug or issue with this configuration on my test server with Postgres 15.

I do not have (yet) the systems needed to test what happens during a migration from an older Postgres version to Postgres 15 and if some specific actions would be required.

[DanSheps]

I believe we are currently documenting the lowest netbox supported Postgres version in our documentation.

This "secure schema" pattern isn't something netbox really requires. NetBox, from a database perspective, is a single user application. It would make far more sense to instead do this the third point recommended under Usage Patterns:

Keep the default search path, and grant privileges to create in the public schema. All users access the public schema implicitly. This simulates the situation where schemas are not available at all, giving a smooth transition from the non-schema-aware world. However, this is never a secure pattern. It is acceptable only when the database has a single user or a few mutually-trusting users. In databases upgraded from PostgreSQL 14 or earlier, this is the default.

This would instead change the instructions to (I think, I would need to confirm):

CREATE DATABASE netbox;
CREATE USER netbox WITH PASSWORD 'J5brHrAXFLQSif0K';
ALTER DATABASE netbox OWNER TO netbox;
\connect netbox;
GRANT CREATE ON SCHEMA public TO netbox

[Alef-Burzmali]

It makes sense. Certainly, installing a second application on the same database as NetBox is not a standard installation.

I've tested your commands, they work on a new installation. You just need to add a missing ; at the end of the last one.

[DanSheps]

Awesome, thanks for testing. I would say we should update the docs with the extra step(s) required for this change.

[Alef-Burzmali]

Hello. Happy to prepare a PR for this, if you want to assign this issue to me.