Does migrate.sh need to include a call to psql?

[matthewhegarty]

Thanks so much for creating this example. There is very little out there about Postgres security for Django

A question:

I can see that psql is called after manage.py migrate in migrate.sh.

1. Why can't we include the GRANT calls for djangouser in roles.sql?

2. Surely a call to psql is going to require a password file somewhere?

[matthewhegarty]

Ah.. So I understand that we cannot issue GRANT statements to tables if they don't exist yet, so the calls have to go after the migrate.py call.

[matthewhegarty]

(I'm including this for anyone else who goes down the rabbit hole of user permissions for Postgres / Django)

I found you don't need to have calls in migrate.sh if you include the following in roles.sql

    ALTER DEFAULT PRIVILEGES FOR USER djangomigrator IN SCHEMA playschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO djangouser;
    ALTER DEFAULT PRIVILEGES FOR USER djangomigrator IN SCHEMA playschema GRANT USAGE ON SEQUENCES TO djangouser;

ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be applied to objects created in the future

postgres docs

[matthewhegarty]

Here is my full init script in case it is of use:

#!/bin/bash
# initialises the dev db only
# https://github.com/sellonen/django-security-tips
# https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/

set -e
SCHEMA=myschema
RW_ROLE=readwrite

echo "creating database"
createdb $DB_NAME

echo "setting default roles"
psql -v ON_ERROR_STOP=1 --dbname "$DB_NAME" <<-EOSQL
    -- lockdown permissions on public schema
    REVOKE CREATE ON SCHEMA public FROM PUBLIC;
    REVOKE ALL ON DATABASE $DB_NAME FROM PUBLIC;

    -- Migrator role
    CREATE USER $MIGRATOR_DB_USER WITH PASSWORD '$MIGRATOR_DB_PASS';
    CREATE SCHEMA $SCHEMA AUTHORIZATION $MIGRATOR_DB_USER;

    -- The user is allowed to create databases for the purposes of running the Django test suite.
    -- The 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 CREATEDB;
    ALTER ROLE $MIGRATOR_DB_USER SET SEARCH_PATH TO $SCHEMA, public;

    -- App User role
    CREATE USER $DB_USER WITH PASSWORD '$DB_PASS';
    ALTER ROLE $DB_USER SET SEARCH_PATH TO $SCHEMA;

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

    -- Grant privileges to users
    GRANT $RW_ROLE TO $MIGRATOR_DB_USER;
    GRANT $RW_ROLE TO $DB_USER;

    -- Alter privileges so that the RW Role will be able to access tables created in future
    -- by the Migrator User
    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;
EOSQL