Postgresql: Better security for Django Applications
Tags: djangorestframework, django, postgres, docker
Published: 2020-03-31 19:54:22

---
title: Postgresql: Better security for Django Applications
published: true
description: Learn about how to use postgres schemas and roles to provide better security for Django applications.
tags: #djangorestframework #django #postgres #docker
---

Whilst Django is intended to be database-agnostic, it supports many features of Postgresql which are [not shared by other databases](https://docs.djangoproject.com/en/3.0/ref/contrib/postgres/), and this makes Postgres a popular choice of database for Django applications.

A [core principle of web application security][berk] 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.

[berk]: https://security.berkeley.edu/education-awareness/best-practices-how-tos/system-application-security/database-hardening-best

### Sample Application

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

[drf-tut]: https://github.com/matthewhegarty/rest-framework-tutorial/tree/postgres-permissions

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][init], and this is where the database is created, and restrictions are applied.

[init]: https://hub.docker.com/_/postgres

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][schema], 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.

[schema]: https://www.postgresql.org/docs/10/ddl-schemas.html

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][testdb].

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

[testdb]: https://docs.djangoproject.com/en/3.0/topics/testing/overview/#the-test-database

### The initialisation script

Our script will run only when the database container is [started for the first time][dockerdb].  The script will connect to the Postgres db using the default user.
The full script is visible [here](https://github.com/matthewhegarty/rest-framework-tutorial/blob/postgres-permissions/docker/db/init-user-db.sh).

[dockerdb]: https://hub.docker.com/_/postgres

Note that environment variables referenced here (e.g. `$DB_NAME`) are defined in [`docker-compose.yml`](https://github.com/matthewhegarty/rest-framework-tutorial/blob/postgres-permissions/docker-compose.yml).

1. Create our application database

```bash
createdb $DB_NAME
```

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

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

[heredoc]: https://tldp.org/LDP/abs/html/here-docs.html

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

```sql
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
```

4. Prevent *any* connection to the new database unless explicitly given

```sql
REVOKE ALL ON DATABASE $DB_NAME FROM PUBLIC;
```

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

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

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

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

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

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

[searchpath]: https://www.postgresql.org/docs/10/ddl-schemas.html#DDL-SCHEMAS-PATH

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

```sql
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.

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

```sql
GRANT $RW_ROLE TO $MIGRATOR_DB_USER;
GRANT $RW_ROLE TO $DB_USER;
```

10.  Alter [default privileges][def] 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!

```sql
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;
```

[def]: https://www.postgresql.org/docs/10/sql-alterdefaultprivileges.html

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][entry] script which will be run when the container starts.

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

[entry]: https://docs.docker.com/engine/reference/builder/#entrypoint

### 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:

```python
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`](https://github.com/matthewhegarty/rest-framework-tutorial/blob/postgres-permissions/docker/entrypoint.sh)):

```bash
./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](https://github.com/sellonen/django-security-tips)
- [Managing Postgres users and roles](https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/)