Authentication and Authorization using Postgres and Node.js

what we're building

we're going to build a server that lets users make "posts", we'll add peristence, authentication, and authorization to it as we go

Setting Up the Express Server

first we need to set up our package.json and install some dependencies

terminal

npm init -y
npm i express morgan npm-run-all nodemon postgres
export PORT=3000

package.json

{
"scripts": {
"dev:server": "nodemon -e js,mjs,jsx -w server -x 'node server/index.mjs'",
"dev": "run-p -s dev:*"
}
}

first import and initialize express

server/index.mjs

import express from 'express';
const { PORT } = process.env;
const app = express();
app.listen(PORT, () => console.log(`server running at http://localhost:${PORT}`));

add our logging middleware first

server/index.mjs

import express from 'express';
import morgan from 'morgan';
const { NODE_ENV, PORT } = process.env;
const app = express();
app.use(morgan(NODE_ENV === 'production' ? 'combined' : 'dev'));
app.listen(PORT, () => console.log(`server running at http://localhost:${PORT}`));

in order to handle parsing the body of an http request we need to add some middleware

server/index.mjs

import express from 'express';
import morgan from 'morgan';
const { NODE_ENV, PORT } = process.env;
const app = express();
app.use(morgan(NODE_ENV === 'production' ? 'combined' : 'dev'));
app.use(express.json({ strict: true }));
app.use(express.urlencoded({ extended: false }));
app.listen(PORT, () => console.log(`server running at http://localhost:${PORT}`));

and also configure the express-session middleware

server/index.mjs

import express from 'express';
import session from 'express-session';
import morgan from 'morgan';
const { NODE_ENV, PORT } = process.env;
const app = express();
app.use(morgan(NODE_ENV === 'production' ? 'combined' : 'dev'));
app.use(express.json({ strict: true }));
app.use(express.urlencoded({ extended: false }));
app.use(
session({
// Force the session id cookie to be set on every response, and reset the expiration countdown.
rolling: true,
// Don't force a session to be created if we don't set any data.
saveUninitialized: false,
// Don't force the session to be saved back to the session store if we didn't modify it.
resave: false,
// A secret used to sign the cookie
secret: process.env.SECRET,
cookie: {
// sessions expire after 14 days
maxAge: 1000 * 60 * 60 * 24 * 14,
// The cookie can't be read by JavaScript.
httpOnly: true,
// The cookie can't be sent with cross-origin requests.
sameSite: 'lax',
// The cookie will be set with the secure flag if we're using HTTPS.
secure: 'auto',
},
}),
);
app.listen(PORT, () => console.log(`server running at http://localhost:${PORT}`));

we can add some routes that update an in-memory list of "posts"

this usually isn't a great idea but we'll fix it later

server/index.mjs

import express from 'express';
import session from 'express-session';
import morgan from 'morgan';
const { NODE_ENV, PORT } = process.env;
const app = express();
app.use(morgan(NODE_ENV === 'production' ? 'combined' : 'dev'));
app.use(express.json({ strict: true }));
app.use(express.urlencoded({ extended: false }));
app.use(
session({
rolling: true,
saveUninitialized: false,
resave: false,
secret: process.env.SECRET,
cookie: {
maxAge: 1000 * 60 * 60 * 24 * 14,
httpOnly: true,
sameSite: 'lax',
secure: 'auto',
},
}),
);
const posts = [];
app.get('/posts', (req, res) => {
res.json(posts);
});
app.post('/posts', (req, res) => {
const { name, body } = req.body;
posts.push({ name, body });

and finally, store the user name in a session

server/index.mjs

import express from 'express';
import session from 'express-session';
import morgan from 'morgan';
const { NODE_ENV, PORT } = process.env;
const app = express();
app.use(morgan(NODE_ENV === 'production' ? 'combined' : 'dev'));
app.use(express.json({ strict: true }));
app.use(express.urlencoded({ extended: false }));
app.use(
session({
rolling: true,
saveUninitialized: false,
resave: false,
secret: process.env.SECRET,
cookie: {
maxAge: 1000 * 60 * 60 * 24 * 14,
httpOnly: true,
sameSite: 'lax',
secure: 'auto',
},
}),
);
const posts = [];
app.get('/posts', (req, res) => {
res.json(posts);
});
app.post('/posts', (req, res) => {
app.get('/currentUser', (req, res) => {

first import and initialize express

add our logging middleware first

in order to handle parsing the body of an http request we need to add some middleware

and also configure the express-session middleware

we can add some routes that update an in-memory list of "posts"

this usually isn't a great idea but we'll fix it later

and finally, store the user name in a session

server/index.mjs

import express from 'express';
const { PORT } = process.env;
const app = express();
app.listen(PORT, () => console.log(`server running at http://localhost:${PORT}`));

we can test our server by running npm run dev:server and sending some requests to it, here for example using curl:


# send a post with a name and store the cookie
curl -v -c sess.cookie localhost:$PORT/posts -d 'name=you' -d 'body=hello world!'
# use the cookie to retrieve the name
curl -v -b sess.cookie localhost:$PORT/currentUser

how do cookie sessions work?

  1. we put some randomly generated and very hard to guess gibberish (sometimes called an opaque token) in a [signed] cookie for a user
  2. you put that token, along with some data (eg their user id) into a table with an expiration
  3. on every request you look at the token in the cookie [and check it's signature], see if it exists in the db and isnt expired, and return the data (eg the user id)
  4. passing around that random, hard-to-guess token makes it very hard to try to impersonate another user, and storing the data (user_id) and expiration in the db means you can revoke the session whenever you want since you own the db

the code above uses in-memory data structures to store posts and sessions, which may work fine during development, but isn't suitable for a production deployment since it has no persistence or cache eviction strategy and so memory usage will scale linearly with new sessions and posts and eventually cause the process to use up all available memory and crash with no way to recover the data afterwards

Persisting Data with Postgres

To persist our data we'll need a database, and what better choice is there than Postgres, the world's most advanced open source database?

There are many ways to run Postgres, one of the easiest ways to manage Postgres for development and production is to use Docker. I've provided a docker-compose.yml file that will run a Postgres 16 server and expose it on port 5432, and a script that will create a database and user for our application.

terminal

npm i -D dotenv inquirer

docker-compose.yml
scripts/generate-dotenv.mjs
scripts/db-setup.mjs

version: "3.8"
services:
db:
image: postgres:16-alpine
environment:
- POSTGRES_USER=${ROOT_DATABASE_USER}
- POSTGRES_PASSWORD=${ROOT_DATABASE_PASSWORD}
command: "postgres -c shared_preload_libraries=pg_stat_statements -c pg_stat_statements.track=all -c log_destination=stderr"
volumes: - /var/run/postgresql:/var/run/postgresql
ports:
- 5432:5432

package.json

{
"scripts": {
"setup": "run-s env:init db:up db:setup",
"env:init": "node scripts/generate-dotenv.mjs",
"db:up": "docker-compose up -d db",
"db:setup": "node -r dotenv/config scripts/db-setup.mjs",
"dev:server": "nodemon -e js,mjs,jsx -w server -x 'node -r dotenv/config server/index.mjs'",
"predev": "run-s -s db:up",
"dev": "run-p -s dev:*"
}
}

now that we have a running postgres cluster first we need to create some tables

migrations/0010-schemas.sql
migrations/0020-sessions.sql
migrations/0030-users.sql
migrations/0040-posts.sql

drop schema if exists app_public cascade;
drop schema if exists app_private cascade;
create extension if not exists citext with schema public;
create schema app_public;
create schema app_private;

now that we have a posts table we can update our application code and store our posts in it

server/index.mjs

import express from 'express';
import session from 'express-session';
import morgan from 'morgan';
import postgres from 'postgres';
const { DATABASE_URL, NODE_ENV, PORT } = process.env;
const sql = postgres(DATABASE_URL);
const app = express();
app.use(morgan(NODE_ENV === 'production' ? 'combined' : 'dev'));
app.use(express.json({ strict: true }));
app.use(express.urlencoded({ extended: false }));
app.use(
session({
rolling: true,
saveUninitialized: false,
resave: false,
secret: process.env.SECRET,
cookie: {
maxAge: 1000 * 60 * 60 * 24 * 14,
httpOnly: true,
sameSite: 'lax',
secure: 'auto',
},
}),
);
app.get('/posts', async (req, res) => {
res.json(await sql`select * from posts order by created_at desc`);
});
app.post('/posts', async (req, res) => {
const { name, body } = req.body;
req.session.user = { name };
await sql`insert into posts (name, body) values (${name}, ${body})`;
res.json(await sql`select * from posts order by created_at desc`);
});
app.get('/currentUser', (req, res) => {
res.json(req.session.user);
});
app.listen(PORT, () => console.log(`server running at http://localhost:${PORT}`));

the Postgres adapter we're using provides a tagged template to run our SQL queries. it's important to note the syntax for calling tagged template functions doesn't use parentheses, which allows the tagged function to send the query separately from the user input and avoid SQL injection.

persist our sessions in postgres

server/index.mjs

import express from 'express';
import session from 'express-session';
import morgan from 'morgan';
import postgres from 'postgres';
const { DATABASE_URL, NODE_ENV, PORT } = process.env;
const sql = postgres(DATABASE_URL);
const app = express();
app.use(morgan(NODE_ENV === 'production' ? 'combined' : 'dev'));
app.use(express.json({ strict: true }));
app.use(express.urlencoded({ extended: false }));
class PgSessionStore extends session.Store {
}
app.use(
session({
rolling: true,
saveUninitialized: false,
resave: false,
secret: process.env.SECRET,
cookie: {
maxAge: 1000 * 60 * 60 * 24 * 14,
httpOnly: true,
sameSite: 'lax',
secure: 'auto',
},
store: new PgSessionStore(),
}),
);
app.get('/posts', async (req, res) => {

our store needs a method to set sessions

server/index.mjs

import express from 'express';
import session from 'express-session';
import morgan from 'morgan';
import postgres from 'postgres';
const { DATABASE_URL, NODE_ENV, PORT } = process.env;
const sql = postgres(DATABASE_URL);
const app = express();
app.use(morgan(NODE_ENV === 'production' ? 'combined' : 'dev'));
app.use(express.json({ strict: true }));
app.use(express.urlencoded({ extended: false }));
class PgSessionStore extends session.Store {
set(sid, session, callback) {
if (!session.user) return callback();
sql`
insert into sessions (sid, user_id, expires)
values (${sid}, ${session.user.userId}::uuid, ${Number(session.cookie.expires)})
on conflict (sid) do update set
user_id = $2,
expires = now() + interval '14 days'
`.then(() => callback(), callback);
}
}
app.use(
session({
rolling: true,
saveUninitialized: false,
resave: false,
secret: process.env.SECRET,
cookie: {

and a method to retrieve sessions

server/index.mjs

import express from 'express';
import session from 'express-session';
import morgan from 'morgan';
import postgres from 'postgres';
const { DATABASE_URL, NODE_ENV, PORT } = process.env;
const sql = postgres(DATABASE_URL);
const app = express();
app.use(morgan(NODE_ENV === 'production' ? 'combined' : 'dev'));
app.use(express.json({ strict: true }));
app.use(express.urlencoded({ extended: false }));
class PgSessionStore extends session.Store {
set(sid, session, callback) {
if (!session.user) return callback();
sql`
insert into sessions (sid, user_id, expires)
values (${sid}, ${session.user.userId}::uuid, ${Number(session.cookie.expires)})
on conflict (sid) do update set
user_id = $2,
expires = now() + interval '14 days'
`.then(() => callback(), callback);
}
get(sid, callback) {
sql`
select sid, user_id, expires
from sessions
where sid = ${sid} and expires > now()
`.then(([result]) => {
callback(null, result || null);
}, callback);

a method to update the session expiration

and a method to delete sessions

server/index.mjs

import express from 'express';
import session from 'express-session';
import morgan from 'morgan';
import postgres from 'postgres';
const { DATABASE_URL, NODE_ENV, PORT } = process.env;
const sql = postgres(DATABASE_URL);
const app = express();
app.use(morgan(NODE_ENV === 'production' ? 'combined' : 'dev'));
app.use(express.json({ strict: true }));
app.use(express.urlencoded({ extended: false }));
class PgSessionStore extends session.Store {
set(sid, session, callback) {
if (!session.user) return callback();
sql`
insert into sessions (sid, user_id, expires)
values (${sid}, ${session.user.userId}::uuid, ${Number(session.cookie.expires)})
on conflict (sid) do update set
user_id = $2,
expires = now() + interval '14 days'
`.then(() => callback(), callback);
}
get(sid, callback) {
sql`
select sid, user_id, expires
from sessions
where sid = ${sid} and expires > now()
`.then(([result]) => {
callback(null, result || null);
}, callback);

persist our sessions in postgres

our store needs a method to set sessions

and a method to retrieve sessions

a method to update the session expiration

and a method to delete sessions

server/index.mjs

import express from 'express';
import session from 'express-session';
import morgan from 'morgan';
import postgres from 'postgres';
const { DATABASE_URL, NODE_ENV, PORT } = process.env;
const sql = postgres(DATABASE_URL);
const app = express();
app.use(morgan(NODE_ENV === 'production' ? 'combined' : 'dev'));
app.use(express.json({ strict: true }));
app.use(express.urlencoded({ extended: false }));
class PgSessionStore extends session.Store {
}
app.use(
session({
rolling: true,
saveUninitialized: false,
resave: false,
secret: process.env.SECRET,
cookie: {
maxAge: 1000 * 60 * 60 * 24 * 14,
httpOnly: true,
sameSite: 'lax',
secure: 'auto',
},
store: new PgSessionStore(),
}),
);
app.get('/posts', async (req, res) => {

typically most apps would use an existing library like connect-pg-simple, but as of writing one did not exist that uses the postgres adapter we are using, and I also wanted to explicitly show what a store does under the hood

this is suddenly a lot of different code in one file, so let's break it up into some smaller files

server.mjs
PgSessionStore.mjs
posts.mjs
db.mjs

import express from 'express';
import session from 'express-session';
import morgan from 'morgan';
import { PgSessionStore } from './PgSessionStore';
import posts from './posts'
const { NODE_ENV, PORT } = process.env;
const app = express();
app.use(morgan(NODE_ENV === 'production' ? 'combined' : 'dev'));
app.use(express.json({ strict: true }));
app.use(express.urlencoded({ extended: false }));
app.use(session({
rolling: true,
saveUninitialized: false,
resave: false,
secret: process.env.SECRET,
cookie: {
maxAge: 1000 _ 60 _ 60 _ 24 _ 14,
httpOnly: true,
sameSite: 'lax',
secure: 'auto',
},
store: new PgSessionStore(),
}));
app.use(posts);
app.listen(PORT, () => console.log(`server running at http://localhost:${PORT}`));

actually adding the auth routes

terminal

npm i argon2

create router and add some simple validations

auth.mjs

import { Router } from 'express';
import argon from 'argon2';
import { sql } from './db';
const argonOpts = {
type: argon.argon2id,
hashLength: 40,
};
const validate = {
username(value) {
if (typeof value !== 'string') return 'username must be a string'
// TODO: check for forbidden words
},
password(value) {
if (typeof value !== 'string') return 'password must be a string'
if (value.length < 8) return 'password must be at least 8 characters'
// TODO: check against zxcvbn
},
};
const router = Router();
router.get('/currentUser', (req, res) => {
res.json({ data: { currentUser: req.session.user || null } });
});
export default router;

add the /register route

auth.mjs

import { Router } from 'express';
import argon from 'argon2';
import { sql } from './db';
const argonOpts = {
type: argon.argon2id,
hashLength: 40,
};
const validate = {
username(value) {
if (typeof value !== 'string') return 'username must be a string'
// TODO: check for forbidden words
},
password(value) {
if (typeof value !== 'string') return 'password must be a string'
if (value.length < 8) return 'password must be at least 8 characters'
// TODO: check against zxcvbn
},
};
const router = Router();
router.get('/currentUser', (req, res) => {
res.json({ data: { currentUser: req.session.user || null } });
});
router.post('/register', (req, res) => {
if (req.session.user) return res.redirect('/');
const { username, password, confirmPassword } = req.body;
req.session.regenerate(async () => {
const [user] = await sql`
insert into app_public.users (username, password_hash)
values (${username}, ${await argon.hash(password, argonOpts)})
returning *
`;
req.session.user = user;
res.json({ data: { currentUser: user } });
});

add some basic field validations

auth.mjs

import { Router } from 'express';
import argon from 'argon2';
import { sql } from './db';
const argonOpts = {
type: argon.argon2id,
hashLength: 40,
};
const validate = {
username(value) {
if (typeof value !== 'string') return 'username must be a string'
// TODO: check for forbidden words
},
password(value) {
if (typeof value !== 'string') return 'password must be a string'
if (value.length < 8) return 'password must be at least 8 characters'
// TODO: check against zxcvbn
},
};
const router = Router();
router.get('/currentUser', (req, res) => {
res.json({ data: { currentUser: req.session.user || null } });
});
router.post('/register', (req, res) => {
if (req.session.user) return res.redirect('/');
const { username, password, confirmPassword } = req.body;
const fieldErrors = {};
if (!username) fieldErrors.username = 'missing username';
const validUsername = validate.username(username);
if (!validUsername) fieldErrors.username = validUsername;
if (!password) fieldErrors.password = 'missing password';
if (!confirmPassword) fieldErrors.confirmPassword = 'please re-type your password';
if (password !== confirmPassword) fieldErrors.confirmPassword = 'passwords dont match';
const validPassword = validate.password(password);

some error handling on conflicting usernames

auth.mjs

import { Router } from 'express';
import argon from 'argon2';
import { PostgresError } from 'postgres';
import { sql } from './db';
const argonOpts = {
type: argon.argon2id,
hashLength: 40,
};
const validate = {
username(value) {
if (typeof value !== 'string') return 'username must be a string'
// TODO: check for forbidden words
},
password(value) {
if (typeof value !== 'string') return 'password must be a string'
if (value.length < 8) return 'password must be at least 8 characters'
// TODO: check against zxcvbn
},
};
const router = Router();
router.get('/currentUser', (req, res) => {
res.json({ data: { currentUser: req.session.user || null } });
});
router.post('/register', (req, res) => {
if (req.session.user) return res.redirect('/');
const { username, password, confirmPassword } = req.body;
const fieldErrors = {};
if (!username) fieldErrors.username = 'missing username';
const validUsername = validate.username(username);
if (!validUsername) fieldErrors.username = validUsername;
if (!password) fieldErrors.password = 'missing password';
if (!confirmPassword) fieldErrors.confirmPassword = 'please re-type your password';
res.status(500).json({ error: 'there was an error processing your request' });

now we can start on the login route

auth.mjs

import { Router } from 'express';
import argon from 'argon2';
import { PostgresError } from 'postgres';
import { sql } from './db';
const argonOpts = {
type: argon.argon2id,
hashLength: 40,
};
const validate = {
username(value) {
if (typeof value !== 'string') return 'username must be a string'
// TODO: check for forbidden words
},
password(value) {
if (typeof value !== 'string') return 'password must be a string'
if (value.length < 8) return 'password must be at least 8 characters'
// TODO: check against zxcvbn
},
};
const router = Router();
router.get('/currentUser', (req, res) => {
res.json({ data: { currentUser: req.session.user || null } });
});
router.post('/register', (req, res) => {
if (req.session.user) return res.redirect('/');
const { username, password, confirmPassword } = req.body;
const fieldErrors = {};
if (!username) fieldErrors.username = 'missing username';
const validUsername = validate.username(username);
if (!validUsername) fieldErrors.username = validUsername;
if (!password) fieldErrors.password = 'missing password';
if (!confirmPassword) fieldErrors.confirmPassword = 'please re-type your password';
return res.status(401).json({ error: { formErrors: 'Invalid username or password' } });

and again with some basic validations

plz use zod

auth.mjs

import { Router } from 'express';
import argon from 'argon2';
import { PostgresError } from 'postgres';
import { sql } from './db';
const argonOpts = {
type: argon.argon2id,
hashLength: 40,
};
const validate = {
username(value) {
if (typeof value !== 'string') return 'username must be a string'
// TODO: check for forbidden words
},
password(value) {
if (typeof value !== 'string') return 'password must be a string'
if (value.length < 8) return 'password must be at least 8 characters'
// TODO: check against zxcvbn
},
};
const router = Router();
router.get('/currentUser', (req, res) => {
res.json({ data: { currentUser: req.session.user || null } });
});
router.post('/register', (req, res) => {
if (req.session.user) return res.redirect('/');
const { username, password, confirmPassword } = req.body;
const fieldErrors = {};
if (!username) fieldErrors.username = 'missing username';
const validUsername = validate.username(username);
if (!validUsername) fieldErrors.username = validUsername;
if (!password) fieldErrors.password = 'missing password';
if (!confirmPassword) fieldErrors.confirmPassword = 'please re-type your password';
return res.status(422).json({ error: { fieldErrors } });

the last piece is the logout route

auth.mjs

import { Router } from 'express';
import argon from 'argon2';
import { PostgresError } from 'postgres';
import { sql } from './db';
const argonOpts = {
type: argon.argon2id,
hashLength: 40,
};
const validate = {
username(value) {
if (typeof value !== 'string') return 'username must be a string'
// TODO: check for forbidden words
},
password(value) {
if (typeof value !== 'string') return 'password must be a string'
if (value.length < 8) return 'password must be at least 8 characters'
// TODO: check against zxcvbn
},
};
const router = Router();
router.get('/currentUser', (req, res) => {
res.json({ data: { currentUser: req.session.user || null } });
});
router.post('/register', (req, res) => {
if (req.session.user) return res.redirect('/');
const { username, password, confirmPassword } = req.body;
const fieldErrors = {};
if (!username) fieldErrors.username = 'missing username';
const validUsername = validate.username(username);
if (!validUsername) fieldErrors.username = validUsername;
if (!password) fieldErrors.password = 'missing password';
if (!confirmPassword) fieldErrors.confirmPassword = 'please re-type your password';
req.session.regenerate(err => {

don't forget to add the new auth router to the express instance

auth.mjs
server.mjs

import express from 'express';
import session from 'express-session';
import morgan from 'morgan';
import { PgSessionStore } from './PgSessionStore';
import posts from './posts';
import auth from './auth'
const { NODE_ENV, PORT } = process.env;
const app = express();
app.use(morgan(NODE_ENV === 'production' ? 'combined' : 'dev'));
app.use(express.json({ strict: true }));
app.use(express.urlencoded({ extended: false }));
app.use(
session({
rolling: true,
saveUninitialized: false,
resave: false,
secret: process.env.SECRET,
cookie: {
maxAge: 1000 * 60 * 60 * 24 * 14,
httpOnly: true,
sameSite: 'lax',
secure: 'auto',
},
store: new PgSessionStore(),
}),
);
app.use(posts);
app.use(auth);
app.listen(PORT, () => console.log(`server running at http://localhost:${PORT}`));

create router and add some simple validations

add the /register route

add some basic field validations

some error handling on conflicting usernames

now we can start on the login route

and again with some basic validations

plz use zod

the last piece is the logout route

don't forget to add the new auth router to the express instance

auth.mjs

import { Router } from 'express';
import argon from 'argon2';
import { sql } from './db';
const argonOpts = {
type: argon.argon2id,
hashLength: 40,
};
const validate = {
username(value) {
if (typeof value !== 'string') return 'username must be a string'
// TODO: check for forbidden words
},
password(value) {
if (typeof value !== 'string') return 'password must be a string'
if (value.length < 8) return 'password must be at least 8 characters'
// TODO: check against zxcvbn
},
};
const router = Router();
router.get('/currentUser', (req, res) => {
res.json({ data: { currentUser: req.session.user || null } });
});
export default router;

authorization

so far a user can register, login, logout, and make post, but there are no restrictions on a user's name, so anyone can post as any user.

in order to leverage Postgres Row-Level-Security we need a visitor role with limited permissions, and an authenticator role that we can log-in to

scripts/db-setup.mjs
scripts/generate-dotenv.mjs
db.mjs

// @ts-check
import postgres from 'postgres';
import inquirer from 'inquirer';
const {
DATABASE_OWNER,
DATABASE_OWNER_PASSWORD,
DATABASE_NAME,
ROOT_DATABASE_URL,
DATABASE_VISITOR,
DATABASE_AUTHENTICATOR,
DATABASE_AUTHENTICATOR_PASSWORD
} = process.env;
await sql.unsafe`grant ${DATABASE_VISITOR} to ${DATABASE_AUTHENTICATOR}`;

the rest of this article is still missing explanations and context, but the code is complete and should work as expected

check back later for more details!

migrations/0010-setup-schemas.sql

drop schema if exists app_public cascade;
drop schema if exists app_private cascade;
create extension if not exists citext with schema public;
revoke all on schema public from public;
alter default privileges revoke all on sequences from public;
alter default privileges revoke all on functions from public;
grant all on schema public to :DATABASE_OWNER;
create schema app_public;
create schema app_private;
grant usage on schema public, app_public, app_hidden to :DATABASE_VISITOR;
alter default privileges in schema public, app_public
grant usage, select on sequences to :DATABASE_VISITOR;
alter default privileges in schema public, app_public
grant execute on functions to :DATABASE_VISITOR;
create function app_private.tg__timestamps() returns trigger as $$
begin
NEW.created_at = (case when TG_OP = 'INSERT' then NOW() else OLD.created_at end);
NEW.updated_at = (case when TG_OP = 'UPDATE' and OLD.updated_at >= NOW() then OLD.updated_at + interval '1 millisecond' else NOW() end);
return NEW;
end;
$$ language plpgsql volatile set search_path to pg_catalog, public, pg_temp;

migrations/0010-setup-schemas.sql
migrations/0020-sessions.sql

create table app_private.sessions (
sid text primary key,
user_id uuid not null,
expires timestamptz not null default now() + interval '14 days'
);
create function app_public.current_session_id() returns text as $$
select nullif(pg_catalog.current_setting('my.session_id', true), '');
$$ language sql stable;
create function app_public.current_user_id() returns uuid as $$
select user_id from app_private.sessions
where uuid = app_public.current_session_id();
$$ language sql stable security definer set search_path to pg_catalog, public, pg_temp;

migrations/0010-setup-schemas.sql
migrations/0020-sessions.sql
migrations/0030-users.sql

create table app_public.users (
user_id uuid primary key default gen_random_uuid(),
username citext not null,
bio text,
-- removed the password_hash column
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
alter table app_private.sessions
add constraint sessions_user_id_fkey (user_id) references app_public.users;
create unique index users_username_idx on app_public.users (username);
create table app_private.user_secrets (
user_id uuid primary key references app_public.users on delete cascade,
password_hash text not null unique
);

migrations/0010-setup-schemas.sql
migrations/0020-sessions.sql
migrations/0030-users.sql
migrations/0040-posts.sql

create type app_public.post_visibility as enum ('public', 'private');
create table app_public.posts (
id uuid primary key default gen_random_uuid(),
user_id uuid not null references app_public.users on delete cascade,
body text not null,
visibility app_public.post_visibility not null default 'public',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);

migrations/0010-setup-schemas.sql
migrations/0020-sessions.sql
migrations/0030-users.sql
migrations/0040-posts.sql
withAuthContext.mjs

import { authenticator as sql } from "./sql"
const role = process.env.DATABASE_VISITOR!
export function withAuthContext(req, cb) {
const sessionId = req.sessionID
return sql.begin(async sql => {
await sql`
select
set_config('role', ${role}, false),
set_config('my.session_id', ${sessionId}, true)
`
return await cb(sql)
})
}

migrations/0010-setup-schemas.sql
migrations/0020-sessions.sql
migrations/0030-users.sql
migrations/0040-posts.sql
withAuthContext.mjs
posts.mjs

import { Router } from 'express';
import { withAuthContext } from './withAuthContext';
const router = Router();
router.get('/posts', (req, res) => {
withAuthContext(req, async sql => {
res.json(await sql`select * from posts order by created_at desc`);
})
})
router.post('/posts', (req, res) => {
const { body, visibility } = req.body;
withAuthContext(req, async sql => {
await sql`
insert into posts (name, visibility)
values (${name}, ${visibility ?? sql.unsafe('default')})`;
res.json(await sql`select * from posts order by created_at desc`);
})
})
export default router

migrations/0010-setup-schemas.sql
migrations/0020-sessions.sql
migrations/0030-users.sql
migrations/0040-posts.sql
withAuthContext.mjs
posts.mjs

create table app_public.users (
user_id uuid primary key default gen_random_uuid(),
username citext unique not null,
bio text,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
alter table app_private.sessions
add constraint sessions_user_id_fkey (user_id) references app_public.users;
create unique index users_username_idx on app_public.users (username);
alter table app_public.users enable row level security;
create policy select_all
on app_public.users
for select using (true);
create policy update_self
on app_public.users
for update using (user_id = app_public.current_user_id());
grant select
on app_public.users
to :DATABASE_VISITOR;
grant
update (username, bio)
on app_public.users
to :DATABASE_VISITOR;
alter table app_private.sessions
add constraint sessions_user_id_fkey
foreign key ("user_id") references app_public.users on delete cascade;
create table app_private.user_secrets (
user_id uuid primary key references app_public.users on delete cascade,
password_hash text not null unique
);
alter table app_private.user_secrets enable row level security;

migrations/0010-setup-schemas.sql
migrations/0020-sessions.sql
migrations/0030-users.sql
migrations/0040-posts.sql
withAuthContext.mjs
posts.mjs

create type app_public.post_visibility as enum ('public', 'private');
create table app_public.posts (
id uuid primary key default gen_random_uuid(),
user_id uuid not null default app_public.current_user_id() references app_public.users on delete cascade,
body text not null,
visibility app_public.post_visibility not null default 'public',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
alter table app_public.posts enable row level security;
create policy select_own_and_public on app_public.posts
for select using (user_id = app_public.current_user_id() or visibility = 'public');
create policy insert_own on app_public.posts
for insert with check (user_id = app_public.current_user_id());
create policy update_own on app_public.posts
for update using (user_id = app_public.current_user_id());
create policy delete_own on app_public.posts
for delete using (user_id = app_public.current_user_id());
create policy all_as_admin on app_public.posts
for all using (exists (
select 1 from app_public.users
where user_id = app_public.current_user_id() and role = 'admin'
));
grant
select,
insert (body, visibility),
update (body, visibility),
delete
on app_public.posts to :DATABASE_VISITOR;

migrations/0010-setup-schemas.sql

drop schema if exists app_public cascade;
drop schema if exists app_private cascade;
create extension if not exists citext with schema public;
revoke all on schema public from public;
alter default privileges revoke all on sequences from public;
alter default privileges revoke all on functions from public;
grant all on schema public to :DATABASE_OWNER;
create schema app_public;
create schema app_private;
grant usage on schema public, app_public, app_hidden to :DATABASE_VISITOR;
alter default privileges in schema public, app_public
grant usage, select on sequences to :DATABASE_VISITOR;
alter default privileges in schema public, app_public
grant execute on functions to :DATABASE_VISITOR;
create function app_private.tg__timestamps() returns trigger as $$
begin
NEW.created_at = (case when TG_OP = 'INSERT' then NOW() else OLD.created_at end);
NEW.updated_at = (case when TG_OP = 'UPDATE' and OLD.updated_at >= NOW() then OLD.updated_at + interval '1 millisecond' else NOW() end);
return NEW;
end;
$$ language plpgsql volatile set search_path to pg_catalog, public, pg_temp;

PostGraphile is a huge inspiration for this post and some of the ideas and code are directly lifted from and the graphile-starter. Big thanks to Benjie for the incredible work he does on the Graphile suite!

Comments