Note: I bundled this into an NPM module. It’s on NPM here and the source is on GitHub here

Set up PostgreSQL

A RDBMS needs two basic things – a database and users. The user side of this gets a little interesting though. Something I haven’t really taken into consideration before is separating the web app interactions into two distinct users. This is in keeping with the principle of least privilege, and is a security concern. During normal operation, a run-of-the-mill web app should really only be able to CRUD rows in tables. It shouldn’t be allowed to connect to other databases, it shouldn’t be allowed to create other users, and it shouldn’t be allowed to change the schema. This is the database user who feeds the front end via the server. Separately, there is more of an “administrative” role. When the web app is being updated, it may update the schema itself. This user would have more privileges than the regular user, as it should really only be used by the automated tooling that supports the web app deployment. Presumably the client (as in UI, not as in database client) should never have access to a user with these privileges.

As I don’t really know what I’m doing, I looked around for an hour or two and only really found this blog post, from a decade ago, really talking about the problem directly. It’s a bit rough around the edges, but there are some decent points in there.

Using PostgreSQL

There are various utilities like createuser and createdb that in some respects are helpful to get something done quickly. In other ways, they obscure what’s actually going on and you spend more time learning to use the utilities then just learning how to use PostgreSQL directly. To that end, we’ll connect to the database and eschew the utilities for now:

# Connect to PostgreSQL server as the default superuser "postgres"
$ psql -U "postgres" -h "192.168.99.100"
Password for user postgres:
psql (9.6.1)
Type "help" for help.

postgres=#

Administrative user

PostgreSQL documentation is pretty fantastic, so look there (link) for the difference between ROLE and USER – *spoiler alert* they’re basically the same thing, USER is just a ROLE with the LOGIN privilege. Again, obviously set the password to something that’s not terrible

# Create a user with all the default settings (no elevated privileges)
postgres=# CREATE USER "express_admin" WITH PASSWORD 'admin_password';
CREATE ROLE

Web app user

We’ll also create a user to interact with the database on behalf of the web app server. This password should be not-terrible AND not provide any hints to guessing your other password. You shouldn’t be too concerned about making these memorable, as you’ll write them down in an environment variable and then essentially forget about them.

postgres=# CREATE USER "express_user" WITH PASSWORD 'password_user';
CREATE ROLE

And take a look to make sure neither have any roles associated:

postgres=# \du
                                    List of roles
 Role name     |                        Attributes                          | Member of
---------------+------------------------------------------------------------+-----------
 express-admin |                                                            | {}
 express-user  |                                                            | {}
 postgres      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Pretty straightforward so far.

Database

Create one! The important thing here is we need to set the owner as express_admin, NOT as express_user:

postgres=# CREATE DATABASE "min_auth_test" WITH OWNER "express_admin";
CREATE DATABASE

And check out how it looks

postgres=# \l
 List of databases
 Name                 | Owner         | Encoding | Collate    | Ctype      | Access privileges
----------------------+---------------+----------+------------+------------+-----------------------
 min-auth-test        | express-admin | UTF8     | en_US.utf8 | en_US.utf8 |

Now we have users AND a database. Well, that was easy! Or was it too easy…

# Connect to test database as our least privileged user, 'express_user'
postgres=# \c min_auth_test express_user;
Password for user express_user:

You are now connected to database "min_auth_test" as user "express_user".
min_auth_test=> CREATE DATABASE "test";
ERROR: permission denied to create database

Great! Our strategy works!

min-auth-test=> CREATE TABLE "test"();
CREATE TABLE
min-auth-test=> \dt
 List of relations
 Schema | Name | Type | Owner
--------+------+-------+--------------
 public | test | table | express-user
(1 row)

# Clean up after ourselves
min-auth-test=> DROP TABLE "test";
DROP TABLE

Well dammit… those are exactly the kind of shenanigans we were trying to avoid. What’s going on here? You’ve essentially just stepped off the edge of a cliff and are now rapidly tumbling down the “how does PostgreSQL work???” line of understanding. I just want to write a web app, not learn the ins and outs of database management! To keep the focus, we’ll just push through this quickly:

# Connect to the database as your superuser
min_auth_test=> \c min_auth_test postgres
You are now connected to database "min_auth_test" as user "postgres".

# Drop the default public schema which is enabling the permissive permissions
min_auth_test=# DROP SCHEMA public;
DROP SCHEMA

# Create a different schema in its place
min_auth_test=# CREATE SCHEMA express AUTHORIZATION express_admin;
CREATE SCHEMA

# Use this new schema as the default for both users
min_auth_test=> ALTER ROLE express_admin SET search_path TO express;
ALTER_ROLE
min_auth_test=> ALTER ROLE express_user SET search_path TO express;
ALTER_ROLE

# Spoiler alert, but later on we're going to store case insensitive text
# Enable the PostgreSQL citext extension now to make it easier then
CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA express;

# We're done with the postgres superuser, so use express_admin from here on out
min_auth_test=# \c min_auth_test express_admin
Password for user express_admin:
You are now connected to database "min_auth_test" as user "express_admin".

# Give the express_user the permission to access the database, but not create anything
min_auth_test=> GRANT USAGE ON SCHEMA express TO express_user;
GRANT

# Strip permission to execute functions from that nasty PUBLIC
min_auth_test=> ALTER DEFAULT PRIVILEGES FOR ROLE express_admin
min_auth_test-> REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES

# Let express_user have access to the CRUD the data in all tables
min_auth_test=> ALTER DEFAULT PRIVILEGES FOR ROLE express_admin IN SCHEMA express
min_auth_test-> GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO express_user;
ALTER DEFAULT PRIVILEGES

# Let express_user use sequences
min_auth_test=> ALTER DEFAULT PRIVILEGES FOR ROLE express_admin IN SCHEMA express
min_auth_test-> GRANT SELECT, USAGE ON SEQUENCES TO express_user;
ALTER DEFAULT PRIVILEGES

# Let express_user use functions
min_auth_test=> ALTER DEFAULT PRIVILEGES FOR ROLE express_admin IN SCHEMA express
min_auth_test-> GRANT EXECUTE ON FUNCTIONS TO express_user;
ALTER DEFAULT PRIVILEGES

Now we’ve got a bit more of an intentional setup, let’s try testing it out again.

# Admin can do admin-y things on our database?
min_auth_test=> CREATE TABLE express.test();
CREATE TABLE
min_auth_test=> DROP TABLE express.test;
DROP TABLE

# Admin can't do admin-y things outside of our database
min_auth_test=> CREATE DATABASE testy_test_test;
ERROR: permission denied to create database

# User can't do admin-y things on our database
min_auth_test=> \c min_auth_test express_user;
Password for user express_user:
You are now connected to database "min_auth_test" as user "express_user".
min_auth_test=> CREATE TABLE express.test();
ERROR: permission denied for schema express
LINE 1: CREATE TABLE express.test();

Beautiful! Seems to be working as designed. Let’s create a couple more databases so we can handle our environments separately.

min_auth_test=> \c postgres postgres
Password for user postgres:
You are now connected to database "postgres" as user "postgres".
postgres=# CREATE DATABASE min_auth_development WITH TEMPLATE min_auth_test OWNER express_admin;
CREATE DATABASE
postgres=# CREATE DATABASE min_auth_production WITH TEMPLATE min_auth_test OWNER express_admin;
CREATE DATABASE

And take a look:

postgres=# \l
                                           List of databases
           Name           |     Owner     | Encoding |  Collate   |   Ctype    |   Access privileges
--------------------------+---------------+----------+------------+------------+-----------------------
 min_auth_development     | express_admin | UTF8     | en_US.utf8 | en_US.utf8 |
 min_auth_production      | express_admin | UTF8     | en_US.utf8 | en_US.utf8 |
 min_auth_test            | express_admin | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres                 | postgres      | UTF8     | en_US.utf8 | en_US.utf8 |
 template0                | postgres      | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                          |               |          |            |            | postgres=CTc/postgres
 template1                | postgres      | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                          |               |          |            |            | postgres=CTc/postgres
(6 rows)

Looks good to me! Try it out to be sure:

postgres=# \c min_auth_development express_admin;
Password for user express_admin:
You are now connected to database "min_auth_development" as user "express_admin".
min_auth_development=> CREATE TABLE express.test();
CREATE TABLE
min_auth_development=> DROP TABLE express.test;
DROP TABLE

min_auth_development=> \c min_auth_development express_user;
Password for user express_user:
You are now connected to database "min_auth_development" as user "express_user".
min_auth_development=> CREATE TABLE express.test();
ERROR: permission denied for schema express
LINE 1: CREATE TABLE express.test();

Awesome! A few databases ready to go, and a permission model that keeps the main application user from doing what its not supposed to. It’d be nice to encapsulate this all so that we can do it programmatically…