Use a dedicated user to run your database migrations on PostgreSQL

Posted on 2021-03-10 in Blog

I'll detail here how to use one user to apply your migrations and one to run you site. This method can be applied to any framework and environment as long as your database is PostgreSQL. It's of course possible to do with other databases, but the SQL syntax to achieve this will be different.

Before beginning, let's see why we would want to do this:

Increased security: by limiting the database operations your site is allowed to make, you reduce the attack surface by applying the principle of least privilege. Please note that I am only talking about schema updates: your site still needs to do SELECT, INSERT, UPDATE and DELETE to work correctly. So, you only limit the surface of attack. If an attacker were to gain access to your database, they would still be able to steal or destroy data. You thus still need proper protection on your database with SSL, strong passwords and possibly access limitation (like IP whitelisting).
Reduced error capabilities: if you are to execute some queries (to debug or analyze something) in your production database, you cannot alter the schema. So if you do make mistakes, they shouldn't be too serious. Please understand that I don't advise you to run queries directly on your production database (your site with its peer-reviewed code is there for it), but you can need it from time to time.

How can we achieve this? Let's dive in. You can launch all these commands in a container if you want to try them out. That's what I did to write this tutorial. All my psql commands are written with that in mind, please adapt them if you must connect differently to your database.

To begin with, we must create the user that will apply our migrations. Let's call it sqlmigrations. We could use the postgres user for this, but I think it's better to have a dedicated user without the super privilege role for the reasons explained above. To do this, connect to the database with psql -U postgres and run:
```
CREATE ROLE sqlmigrations WITH LOGIN CREATEDB CREATEROLE;
```
LOGIN is required to allow us to use this user to connect to a database, CREATEDB will allow us to create a database that will be owned by sqlmigrations and CREATEROLE will allow us to create the other user with sqlmigrations. This way, all our objects will have the proper owner which will make our lives easier: since everything we need to operate on belongs to sqlmigrations we can use this user for all our operations. You could reduce the permissions of sqlmigrations to limit its scope further, it's just a bit more complex and I won't detail this here. With all the information here, you would be able to do this easily if you want.
Now that our user is created, we can exit the first command prompt with \q and connect with our new user with:
```
psql -U sqlmigrations -d postgres
```
Since no database exists with the name sqlmigrations we need to specify another existing database like the default postgres. If we don't, our connection will fail with psql: FATAL: database "sqlmigrations" does not exist.
Now, let's create our database:
```
CREATE DATABASE djangosite;
```
We can now connect to our database with:
```
psql -U sqlmigrations -d djangosite
```

Let's create the user for our site:

CREATE ROLE django WITH LOGIN;

If you use the \du command to list our users, you should see this at this point:

Role name   |                         Attributes                         | Member of
---------------+------------------------------------------------------------+-----------
django        |                                                            | {}
postgres      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
sqlmigrations | Create role, Create DB

Now, let's give the proper roles to our django user. This always happen in two steps: we apply the change on existing objects then we alter the default options to enforce our rules to new objects. If you have multiple schemas, you will have to repeat the operation for all of them. Here, I only cover the public schema.

-- Allow our user to run SELECT, INSERT, UPDATE, DELETE queries.
GRANT SELECT, INSERT, UPDATE, DELETE
    ON ALL TABLES
    IN SCHEMA public
    TO django;
-- Enable this for all new tables.
ALTER DEFAULT PRIVILEGES
    GRANT SELECT, INSERT, UPDATE, DELETE
    ON TABLES
    TO django;
-- Allow our user to use SEQUENCES.
-- It's required to insert data with auto-incrementing primary keys for instance.
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO django;
ALTER DEFAULT PRIVILEGES
    GRANT USAGE, SELECT
    ON SEQUENCES
    TO django;

So far, so good. We are not completely done yet however. Our django user still can alter its permission which we don't want: what's the point of limiting what a user can do if it can do privilege escalation? We can avoid this with:

REVOKE GRANT OPTION
    FOR ALL PRIVILEGES
    ON ALL TABLES
    IN SCHEMA public
    FROM django;
ALTER DEFAULT PRIVILEGES
    REVOKE GRANT OPTION
    FOR ALL PRIVILEGES
    ON TABLES
    FROM django;

Last step, we must prevent our django user to be able to create table (which is possible by default). We need to do this in three steps (as far as I know, it's the only way to do this):
1. Connect to the database with the postgres user (only a super user can do this):
```
psql -U postgres -d djangosite
```
2. Revoke the ability to create tables for all users:
```
-- public (lower case) is for the schema public.
-- PUBLIC (upper case) means "for all users".
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
```
3. Grant back the ability to create table to the sqlmigrations user (again, only a super user can do this):
```
GRANT CREATE ON SCHEMA public TO sqlmigrations;
```

Now that we've done all that, let's test it:

As sqlmigrations, you should be able to do what you want. For instance:

djangosite=> CREATE TABLE tata(id INTEGER);
CREATE TABLE
djangosite=> ALTER TABLE toto ADD COLUMN toto TEXT;
ALTER TABLE
djangosite=> ALTER TABLE toto DROP COLUMN toto;
ALTER TABLE

But the user django has restricted access (connect with psql -U django -d djangosite):

djangosite=> SELECT * FROM toto;
id
----
(0 rows)

djangosite=> INSERT INTO toto VALUES (1);
INSERT 0 1
djangosite=> SELECT * FROM toto;
id
----
1
(1 row)

djangosite=> ALTER TABLE toto ADD COLUMN toto TEXT;
ERROR:  must be owner of table toto
djangosite=> DROP TABLE toto;
ERROR:  must be owner of table toto
djangosite=> CREATE TABLE dj(id INTEGER);
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE dj(id INTEGER);
                    ^
djangosite=> DELETE FROM toto WHERE id = 1;
DELETE 1
djangosite=> SELECT * FROM toto;
id | tata
----+------
2 |
(1 row)

To view a summary of all permissions, run:

djangosite=> \ddp
                    Default access privileges
    Owner     | Schema | Type  |          Access privileges
---------------+--------+-------+-------------------------------------
sqlmigrations |        | table | sqlmigrations=arwdDxt/sqlmigrations+
            |        |       | django=arwd/sqlmigrations
(1 row)

djangosite=> \dp
                                    Access privileges
Schema | Name | Type  |          Access privileges          | Column privileges | Policies
--------+------+-------+-------------------------------------+-------------------+----------
public | toto | table | sqlmigrations=arwdDxt/sqlmigrations+|                   |
    |      |       | django=arwd/sqlmigrations           |                   |
(1 row)

Bonus: if you created everything as the postgres user and want to change to another user, you will have to change the owner of the database and its tables with:

ALTER DATABASE djangosite OWNER TO sqlmigrations;
-- Repeat for each table
ALTER TABLE toto OWNER TO sqlmigrations;

You cannot use REASSIGN OWNED BY postgres TO sqlmigrations; to gain time because PostgreSQL doesn't allow you to do that for objects that belongs to the user postgres (it would mess up PG since many system objects are owned and must be owned by the user postgres).

Some resources: