Postgresql: Better security for Django Applications

Whilst Django is intended to be database-agnostic, it supports many features of Postgresql which are not shared by other databases, and this makes Postgres a popular choice of database for Django applications.

A core principle of web application security is that the application should use the 'least privilege' model, meaning that the database permissions for the application are locked-down so that the application has only the permissions it needs to do its job, and no more.

For example, imagine that an attacker was able to exploit a web application bug and run their own SQL commands. This would be bad, but the severity could be minimised if the attacker was restricted to a certain set of operations.

In this blog post, I'm going to demonstrate how we can use Postgres schema and roles to ensure that Django application privileges are limited appropriately.

Sample Application

To demonstrate the concepts, I've created a clone of the Django Rest Framework Tutorial which runs on Docker.

Feel free to clone the repo and run with docker-compose up (Ctrl+C to stop).

Once the app is running:

• Browse to http://localhost:8000/
• Use admin / password123 to login.

To start from scratch, run docker-compose down -v, followed by docker-compose up.

NB The sample application includes hard-coded passwords (in docker-compose.yml) - this is just to make life easier for demonstration purposes, and you should never hard-code passwords in a real application.

Database initialisation

When the Postgres docker container starts for the first time, it runs an initialisation script, and this is where the database is created, and restrictions are applied.

Using containers in this way makes it very easy to initialise and run an application consistently. If you are not using Docker, then use another method of initialising the Postgres database. For example, an external hook which runs when the db is initialised, such as an AWS Lambda function.

Postgres schemas

Postgres has the concept of schemas, and this forms a useful basis for securing our application. A schema is effectively a namespace. If you have a table called Customer, and a schema called App, then the Customer table can be isolated to within the App schema.

By explicitly defining roles which have access to a schema, we can be confident that a database user (i.e. our Django application) is accessing only those tables and sequences which they are permitted to do so.

Postgres has a default schema, called public, and unless otherwise specified, database users can access and create objects in this public schema. This creates a potential security weakness which we will remove.

Postgres roles

A Postgres role can be created and given explicit privileges. A database user can then be assigned to the role. For example, we could create a 'read-only' role and a 'read-write' role. Permissions could be restricted according to these roles.

By explicitly defining privileges, we are clear about what given roles are permitted to do.

DB initialisation walkthrough

There are some particular nuances with defining schemas and roles for use with Django. In the rest of this post, I'll walk through the steps required to restrict user access for Django.

Specifically, our script needs to address the following:

1. Create a database and define schemas and roles.

2. Create a 'django migrator' user which has permission to create our application objects (i.e. those created when manage.py migrate is run). Additionally, the 'django migrator' user must be allowed to create databases, because this is necessary when tests are run.

3. Create a 'django application' user. This user will have appropriate read and write privileges on our application tables.

The initialisation script

Our script will run only when the database container is started for the first time. The script will connect to the Postgres db using the default user.
The full script is visible here.

Note that environment variables referenced here (e.g. $DB_NAME) are defined in docker-compose.yml.

1. Create our application database

createdb $DB_NAME

1. Define a heredoc to run a script through psql. The connection to psql is made using the default Postgres user.

psql -v ON_ERROR_STOP=1 --dbname "$DB_NAME" <<-EOSQL
...
EOSQL

1. Lockdown the 'public' schema. This prevents creation of new objects unless given permission.

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

1. Prevent any connection to the new database unless explicitly given

REVOKE ALL ON DATABASE $DB_NAME FROM PUBLIC;

1. Create Migrator role. The migrator user is allowed to create databases for the purposes of running the Django test suite.

CREATE USER $MIGRATOR_DB_USER WITH PASSWORD '$MIGRATOR_DB_PASS' CREATEDB;
CREATE SCHEMA $SCHEMA AUTHORIZATION $MIGRATOR_DB_USER;

1. The migrator user must have access to the 'public' schema because that is where new db tables for the test db will be created.

ALTER ROLE $MIGRATOR_DB_USER SET SEARCH_PATH TO $SCHEMA, public;

1. Create an 'application user' role. This user must only be allowed to query the application schema. The 'search path' is defined to ensure that only our application schema is visible to the application db user.

CREATE USER $DB_USER WITH PASSWORD '$DB_PASS';
ALTER ROLE $DB_USER SET SEARCH_PATH TO $SCHEMA;

1. Create a role which can connect to the db and has read / write access to the application schema only.

CREATE ROLE $RW_ROLE;
GRANT CONNECT ON DATABASE $DB_NAME TO $RW_ROLE;
GRANT USAGE ON SCHEMA $SCHEMA TO $RW_ROLE;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA $SCHEMA TO $RW_ROLE;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA $SCHEMA TO $RW_ROLE;

If you examine the GRANT statements above, you can see how it would be possible to restrict access further for other types of roles. For example, we could define a 'read-only' role which allowed only SELECT privileges.

1. Grant the defined read / write role to our db users.

GRANT $RW_ROLE TO $MIGRATOR_DB_USER;
GRANT $RW_ROLE TO $DB_USER;

1. Alter default privileges for read / write role. This is a necessary step because otherwise the role will not have access to any tables which are created later. That will include tables created in the manage.py migrate step, and that would render the application inaccessible to users!

ALTER DEFAULT PRIVILEGES FOR USER $MIGRATOR_DB_USER GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO $RW_ROLE;
ALTER DEFAULT PRIVILEGES FOR USER $MIGRATOR_DB_USER GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO $RW_ROLE;

We can see the above steps in the output produced when Postgres is initialised. Once complete, it will mean that we have two users, one of whom has restricted privileges to access database objects.

Application entrypoint

If you are familiar with Docker, you will know that you can define an entrypoint script which will be run when the container starts.

We will use this script to run our migrations and start the server.

Referencing the Migrator User

Recall that we have defined our 'migrator user' to have the appropriate permissions to create tables (and other objects). We want to use this user when our migrations are run.

This is achieved by defining an additional settings_migrator.py file, which overrides the db connection parameters:

DATABASES["default"]["USER"] = 'migrator_user'  
DATABASES["default"]["PASSWORD"] = os.environ["MIGRATOR_DB_PASS"]  

We must reference this overridden settings file when we run migrate (in entrypoint.sh):

./manage.py migrate --settings=tutorial.settings_migrator

We will not override settings anywhere else in the entrypoint script, which means that all other manage.py commands will run using our application user.

Note that if you exclude the --settings argument, then the migration task will fail, because it doesn't have appropriate permissions. You can try this out by removing the argument from entrypoint.sh, and running docker-compose down -v; docker-compose up --build.

A note on testing

As mentioned earlier, by default Django requires permission to create databases when running tests. This means that tests also need to be run using the --settings flag which defines the connection details for the 'migrator user'.

Conclusion

This post has shown how we can restrict database access for our Django applications using Postgres features. Two users were defined in the Postgres db. We then used one 'migrator user' to create run our migrations. The application user was given read / write privileges to the application schema only.

When building web applications, restricting access rights at the database level is an important security consideration, therefore it is worthing spending some time understanding how Postgres configuration can help.

Further reading

• django security tips
• Managing Postgres users and roles