HOWTO: Secure Postgres for Web Apps

postgres-webapp-boilerplate.sql

/* Postgres web app boilerplate cheat sheet

   I always hate the tedious set up needed whenever I want to write a new web app and wire up a database.
   The first half of this cheat sheet is a Postgres script that can be used to instantiate and secure a new db.
   The second half of the cheat sheet is a collection of useful snippets.
*/

/**************************************************************************************************************/
/* START "psql" and PASTE below. NOTE: Change "db_name", "admin_pw" and "user_pw" here to reflect your needs. */
/**************************************************************************************************************/
\c postgres postgres;
\set db_name app
\set admin_pw '''password1''';
\set user_pw '''password2''';
/* Stop editing here. Below variables are automatically set, although they can optionally be altered. */
\set db_admin :db_name _admin
\set db_user :db_name _user
\set db_schema :db_name _schema
/* Create roles and database */
CREATE USER :db_admin WITH PASSWORD :admin_pw;
CREATE USER :db_user WITH PASSWORD :user_pw;
CREATE DATABASE :db_name WITH OWNER :db_admin;
\c :db_name postgres;
/* Replace default schema in the new database and start fresh */
DROP SCHEMA public;
CREATE SCHEMA :db_schema AUTHORIZATION :db_admin;
/* For convenience, set db_admin's and db_user's default schema to the new schema,
   but leave the db's default schema as the nonexistent 'public' schema. */
ALTER USER :db_admin SET search_path TO :db_schema;
ALTER USER :db_user SET search_path TO :db_schema;
/* Do the rest of the db managament using db_admin */
\c :db_name :db_admin;
/* Only allow admins and db_user to use the new database */
REVOKE CONNECT ON DATABASE :db_name FROM PUBLIC;
GRANT CONNECT ON DATABASE :db_name TO :db_user;
/* Allow db_user to access, but not create objects in the schema */
GRANT USAGE ON SCHEMA :db_schema TO :db_user;
/* PUBLIC should not be allowed to execute functions created by db_admin user */
ALTER DEFAULT PRIVILEGES FOR ROLE :db_admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
/* Change app_user's default privileges for tables, sequences and functions created by app_admin in schema */
ALTER DEFAULT PRIVILEGES FOR ROLE :db_admin IN SCHEMA :db_schema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO :db_user;
ALTER DEFAULT PRIVILEGES FOR ROLE :db_admin IN SCHEMA :db_schema GRANT SELECT, USAGE ON SEQUENCES TO :db_user;
ALTER DEFAULT PRIVILEGES FOR ROLE :db_admin IN SCHEMA :db_schema GRANT EXECUTE ON FUNCTIONS TO :db_user;
/***   STOP HERE   ***/


/***************************/
/** RANDOM SNIPPETS BELOW **/
/***************************/

/* Change the default postgres password: */
\c postgres postgres
\password postgres


/* CHANGE PORT: First, find the location of postgresql.conf
                Then, edit the file and look for "port".
		Finally, restart postgres. */
psql -t -P format=unaligned -c 'show config_file'


/* LOCKDOWN PERMISSIONS: First, find the location of pg_hba.conf */
psql -t -P format=unaligned -c 'show hba_file'
/*                       Then, edit the file and use this as reference:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             glenn,postgres                         trust
local   app             app_admin                              trust
local   all             all                                     md5
# IPv4/6 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
# IPv4/6 internet connections:
host    all             all             0.0.0.0/0             reject
host    all             all             ::0/0                 reject
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
host    replication     all             0.0.0.0/0             reject
host    replication     all             ::0/0                 reject
*/


/* SAMPLE: Table creation */
\c :db_name :db_admin
CREATE TABLE snippets (
  id INTEGER NOT NULL PRIMARY KEY generated always as identity,
  title VARCHAR(100) NOT NULL,
  content TEXT NOT NULL,
  created TIMESTAMP NOT NULL,
  expires TIMESTAMP NOT NULL
);


/* SAMPLE: Row insertion */
\c :db_name :db_user
INSERT INTO snippets (title, content, created, expires) VALUES (
  'Over the wintry',
  'Over the wintry\nforest, winds howl in rage\nwith no leaves to blow.\n\n– Natsume Soseki',
  NOW(),
  (NOW() + INTERVAL '1 year')
);