Introducing stormlight-iso

I decided in December that I want to start coding again. I've been in engineering leadership roles for the past two and a half years, which has kept me away from coding. I miss being in a flow state working on low-level technical problems for hours on end. One of the areas I'm missing out on is mastering kubernetes. I use kubernetes at work, but I removed enough that it's difficult to grok the details.

Therefore, last winter, I bought three Intel NUCs (8i3BEK1) to be the basis of my kubernetes homelab. After physically building each machine, connecting them to my network, and manually installing Ubuntu 18.04 three times, I decided that I didn't want to do this again. I am, to my wife's annoyance, forgetful. I have difficulty remembering to buy milk when going to the grocery store with the sole intention of buying milk. Imagine what the three NUCs looked like after I installed Ubuntu three times. Not very similar.

Luckily, Ubuntu had an installation method called preseeding to install itself with pre-configured answers to the dialogue prompts. Essentially, this allowed me to remaster the installation ISO so that I did not have to manually enter resposes to dialog prompts. After following the instructions from the wiki, I created an ISO that installed Ubuntu Server from start to finish without any keyboard prompts. With the ISO, I installed Ubuntu identically on my three NUCs and went about my business installing Kubernetes.

This development took several weeks because I became a father at the same time. And apparently, newborns need to feed every few hours. Though, I admit that's a coverup to the real reason it took so long. I didn't know how to do this. I've never dealt with the debian installer (what Ubuntu uses for installation), manipulating initrd, or configuring VirtualBox images to mimic intel NUCs for development. And then to top it all off, I still had to deal with differences in linux and mac tools.

Nevertheless, I codified my work into the stormlight-iso project on GitHub (stormlight is the name of my kubernetes cluster). Now I can forget the entire process without guilt. And if you'd like, you too can forget how to do it too!

With that, I'll leave you at the beginning of the README.

stormlight-iso

This project builds an Ubuntu 18.04 ISO to install Ubuntu unattended (no keyboard interaction) on Intel NUC 8 Core i3 machines.

This project assumes:

  • Installation of Ubuntu via USB stick
  • ISO built on a Mac OSX machine
  • Intel NUC has a static IP assigned to it to SSH to the machine (or some way for you to find the IP of your machine after Ubuntu has been installed and booted)
  • A USB stick with minimum 100MB of space

The project is designed to minimize the amount of physical effort to set up an Intel NUC because the author is lazy and forgetful. Also, the author has several Intel NUCs and manually entering in configuration value is error prone. Here's what the installation process looks like.

  1. Build the stormlight.iso with preseed config and an ssh public key
  2. Create a bootable USB from the stormlight.iso
  3. Walk over to the Intel NUC, plug in USB stick, and power on the machine
  4. Wait until the machine powers itself down after the installation (roughly 10-15 mins). "Look ma, no keyboard!"
  5. Unplug USB stick and power on the machine.
  6. Walk back to your computer and SSH into the machine.

That's it!

Simulate Intel NUCs with Virtualbox

Since yesterday's update, I've made the repo! It has a minimal (read: shitty) README, but the code is all there. Just git clone the repo on a linux machine and run make.

I need to add a simple way to check all dependencies. Maybe a command like make check-deps would work.

I also need to add a user-friendly way of letting people know that they should add a config/authorized_hosts file.

Simulating Intel NUCs with VirtualBox

I was thinking about writing some tests for this new repo. I'd like to run my testing on my mac before making physical moves. Here are some commands I used help manage the stormlight virtualbox.

# Turn on and off the VM
VBoxManage startvm stormlight
VBoxManage controlvm stormlight poweroff

# Attach stormlight.iso as a DVD
VBoxManage storageattach stormlight --storagectl IDE --port 1 --device 0 --type dvddrive --medium ~/Downloads/stormlight.iso

# Setting medium to "none" removes the drive. 
VBoxManage storageattach stormlight --storagectl IDE --port 1 --device 0 --medium "none"

Creating a VM to Simulate an Intel NUC with Virtualbox

# making a virtualbox for stormlight
alias vb=VBoxManage

mkdir '/Users/pbui/VirtualBox VMs/test/'
vb createvm --name test --register --ostype "Ubuntu_64"
vb modifyvm test --firmware efi --rtcuseutc on --ioapic on --memory 1024 --vram 128 --nic1 nat 

vb createmedium disk --filename '/Users/pbui/VirtualBox VMs/test/test.vdi' --size 10240 --format VDI --variant Standard

vb storagectl test --name nvme --add pcie --controller NVMe
vb storagectl test --name ide --add ide --controller PIIX4

vb storageattach test --storagectl nvme --port 0 --device 0 --type hdd --medium '/Users/pbui/VirtualBox VMs/test/test.vdi' --nonrotational on

vb storageattach test --storagectl ide --port 1 --device 0 --type dvddrive --medium ~/Downloads/stormlight.iso

vb startvm test

Ubuntu Unattended Installs, Virtualbox, Manipulating initrd.gz, and Makefiles

I have three bare-bones Intel NUCs. I want to install Ubuntu Server 18.04 and then install Kubernetes using kubeadm.

But, installing Ubuntu Server three times is a chore. I've misconfigured the servers multiple times now. I'm honestly tired of it.

So I'll automate it.

Thinking out loud, this is what I'd like to see:

  • Stick a USB into the machine and power on
  • Wait for the install to complete
  • Reboot the machine
  • SSH into the machine from my mac to automate configuration with <insert some tool I've yet to decide on>.

Luckily, I came across the Automatic Installation docs and the lovely InstallCDCustomization wiki.

But, the installation is still physical. I have to create the ISO, load it on a USB stick, walk over to my Intel NUCs and stick in the USB, and boot the instance. Then I have to wait. And if I didn't setup the preseed.cfgcorrectly, I am prompted with a dialog box that stops the installation.

I need a way to simulate the installation on a new system without moving from my chair.

As I edit this post, I realize how lazy I am reading that last sentence.

Luckily, Virtualbox is a handy tool for this.

Virtualbox command line

Here are the commands I found useful simulating the installation.

VBoxManage startvm stormlight
VBoxManage controlvm stormlight poweroff

# Attach stormlight.iso as a DVD
VBoxManage storageattach stormlight --storagectl IDE --port 1 --device 0 --type dvddrive --medium ~/Downloads/stormlight.iso

# Setting medium to "none" removes the drive. 
VBoxManage storageattach stormlight --storagectl IDE --port 1 --device 0 --medium "none"

Working with initrd.gz

Through a lot of trial and error, I realized that the best way to automate the installation process is to modify the initrd.gz archive for the Ubuntu installation process. Sadly, this took a few days of trial an error tinkering with a preseed.cfg file in the root directory of the ISO and with command line flags. Neither solution worked, so modifying the initrd.gz archive it is. Here are a few helpful commands for modifying the archive (which I only just realized now that cpio was a precursor for tar -- cool!).

# Lists files in archive just like ls -l
cpio -ivt < initrd.gz

# Extract a file with isoinfo
isoinfo -i mini.iso -x '/INITRD.GZ;1' > initrd.gz

Makefile on lightweaver

lightweaver is the name of my kubernetes master -- I name my machines after orders of the Knights Radiant. This section probably doesn't matter to you readers as you don't have access to this machine, but hey, these are my development notes ...

On lightweaver (my intel NUC with ubuntu installed by hand), I set up a new working directory to help build and create the ISO with a Makefile. It's at ~/stormlight-iso on lightweaver.

ssh lightweaver
cd stormlight-iso
make

I also created scripts locally on my Mac to build the stormlight-iso, download to my Mac, run Virtualbox to create the image, and then finally have it shutdown. This is at:

~/code/stormlight/test-stormlight.sh

Update (later in the day)

I finally got everything working! Even the SSH and disabled password.

Next: make a private repo and store on GitHub so I don't forget. The repoisotry is designed to run on Ubuntu machines (for now). I will add some form of testing as well.

GOTO Postgres Configuration for RDS and Self-managed Postgres

I've updated my previous postgres configuration so that it works for both self-managed postgres databases and RDS postgres databases.

I want to address a failing in my prior post on my GOTO postgres configuration because it fails for RDS postgres databases. Luckily, this change will work for RDS postgres and self-managed installations. Woohoo!

RDS's most privileged user is rds_superuser. rds_superuser is akin to a regular user that has privileges to create postgres roles. And, unlike a postgres superuser, rds_superuser does not have privileges to create and manage objects for another postgres role. This is the crux of the problem with my previous configuration, and the reason you see errors like ERROR: must be a member of the role "owner".

Since we cannot create objects for other users with rds_superuser, we separate role management and database object management. To manage roles, we rely on rds_superuser. To create database objects and control access, we rely on the owner. So the configuration from my previous post needs a few changes. Here's the gist:

My GOTO Postgres Configuration for Web Services

My guide to setting up postgres for web services.

My GOTO Postgres Configuration for Web Services

UPDATE Jan 6, 2020: I've updated this guide's configuration in another post to also include RDS databases. This configuration still works, but only for self-managed postgres installations that have a real superuser.

I've painfully learned how to configure postgres one incident at a time. And now this guide represents my learnings for web services. Also, I'm tired of thinking and discussing this with myself, and I want share this guide with others. This guide presents my default postgres database configuration for web services.

Table of Contents

Assumptions

This guide is a starter configuration for postgres. It makes several assumptions about a web service and development team. This may work for you. This guide assumes:

  • A running postgres database and you have superuser or superuser-like (e.g., rds_superuser)(see update for RDS) access
  • You're developing a web service with a team and both will (hopefully) grow rapidly (think: startups)
  • Schema changes are executed as an admin process -- you use rake db:migrate, manage.py migrate, sequelize db:migrate, or similar to create tables prior to app deployment
  • One application postgres database (i.e., when you run \l to show all databases on the server)
  • One schema named after the database

Last but not least, this configuration is designed to evolve as your web service and team grow. If the configuration doesn't work, change it!

The End Result

  • One user (i.e., a postgres role that can log in) owns all objects (database, schema, tables, indexes, etc.). This user is called the owner.
  • A role readwrite_users that enables read and write access to all objects created by the owner.
  • A role readonly_users that enables read-only access to all objects created by the owner.
  • An app user that belongs to readwrite_role that has connection settings to guard against scaling problems (statement_timeout, lock_timeout, idle_in_transaction_session_timeout, connection_limit, and search_path)

The Upsides

  • Enables good separation for maintenance operations (schema changes, data backfills, monitoring) from application operations (CRUD, data management, etc.)
  • Enables server-side defaults for user settings like statement_timeout and lock_timeout -- the application can still override these if needed
  • Sets up roles and allows you to never deal with postgres GRANTS, REVOKES, and the unfortunately confusing ALTER DEFAULT PRIVILEGES ever again (as long as you stay within the use-cases of this configuration of course)
  • Groups users by their access privileges of read-and-write or read-only
  • Prevents TRUNCATE of an entire table from the app user

The Downsides

This configuration doesn't come without its downsides. The biggest downside I experience is related to local development. Local development requires knowing two sets of credentials -- one credential is used for schema changes (migrations), and one is used by the app to read and write data. For the developer, this knowledge can be codified to reduce confusion, but it's still knowledge a developer should know with the configuration presented in this guide.

Another downside with this configuration is the inability to prevent DELETE * FROM table. The configuration does prevent TRUNCATE statements for application developers, but preventing DELETE * FROM table is not easily accomplished without bigger trade-offs.

Finally, setting guard rails like statement_timeout at the database layer does not prevent abuse from the application code. Postgres does not prevent SET statements. Also, it's important that the application has the ability to override settings when needed. As mentioned, the default settings are guard rails and not jail bars.

Walkthrough: Configuring Postgres

Let's walk through the steps required to create the configuration described. Connect to your database with a superuser. All statements executed within this guide require the superuser. That is all you will need.

Create the owner User

The owner user owns all objects for your application's database. Every schema, table, index, and <insert favorite postgres object> is owned by this user. owner is the only user used for schema changes.

CREATE ROLE owner 
	LOGIN 
	ENCRYPTED PASSWORD 'secret' 
	CONNECTION LIMIT 3;
ALTER ROLE owner SET statement_timeout = 20000;
ALTER ROLE owner SET lock_timeout = 3000;

-- v9.6+
ALTER ROLE owner SET idle_in_transaction_session_timeout = 3000; 

  • Change the password to something secure. :)
  • CONNECTION LIMIT 3: This allows owner to connect if when we have accidental long-running queries. Which leads us to ...
  • statement_timeout: 20 seconds is long, but it provides an upper limit for long-running queries. You will likely set this value higher in migrations to accommodate for schema changes that rewrite tables and for a larger data set. But you can enjoy that error message when your data is large enough that postgres cancels your schema change.
  • lock_timeout: 3 seconds is what I've found to be long enough but not too long where an outage will occur due to connection exhaustion.
  • idle_in_transaction_session_timeout: 3 seconds, similar to lock_timeout is what I've found useful for my applications. Change the value to suit your needs. This setting is only available in postgres 9.6+.

Create the readwrite_users Role

The readwrite_users role groups users that require read and write access (CRUD) to objects owned by owner.

CREATE ROLE readwrite_users NOLOGIN;

We need a database to alter privileges, so this is as much as we can configure for this role at this point.

Create the readonly_users Role

The readonly_users role groups users that require read access (SELECT) to objects owned by owner.

CREATE ROLE readonly_users NOLOGIN;

We need a database to alter privileges, so this is as much as we can configure for this role at this point.

Create the Database

Create the exampledb database.

CREATE DATABASE exampledb 
WITH OWNER owner 
ENCODING UTF8 
LC_COLLATE 'en_US.UTF-8' 
LC_CTYPE 'en_US.UTF-8';

Postgres enables connection privileges to new databases for all users (PUBLIC) by default. Postgres also creates the public schema enabling usage and creation of tables within that schema for all users. Instead of relying on implicit privileges from the default installation, we want to declare privileges to roles explicitly. This puts us in a state of least privilege first, and then we add new privileges afterward. I generally find the least-privilege-first strategy much easier to follow mentally and in code. I'm a simpleton, so starting with nothing and adding is easier than starting when everything and then removing.

Therefore, with new databases, I revoke all privileges for the database and drop the public schema.

\c exampledb
REVOKE ALL ON DATABASE exampledb FROM PUBLIC;
DROP SCHEMA public;

Create the Schema and Configure search_path

Now create the schema. The schema is named after the database to keep things simple.

SET ROLE owner;
CREATE SCHEMA exampledb;
RESET ROLE;

Now we default the owner user to exampledb schema on connection since we dropped the public schema. Also, if this was not set, then owner defaults to "$user" -- i.e., the owner schema that does not exist.

ALTER ROLE owner SET search_path TO exampledb;

We also need to set our session's search_path to exampledb so that we, as a superuser, properly create objects in the exampledb schema.

SET search_path TO exampledb; 

GRANTs for Resources

With the basic resources provisioned, we grant privileges for the owner user, readwrite_users role, and readonly_users role. Starting with the exampledb database.

GRANT CONNECT ON DATABASE exampledb TO readwrite_users;
GRANT TEMPORARY ON DATABASE exampledb TO readwrite_users;

GRANT CONNECT ON DATABASE exampledb TO readonly_users;
GRANT TEMPORARY ON DATABASE exampledb TO readonly_users;

Now grant privileges to the exampledb schema.

GRANT USAGE ON SCHEMA exampledb TO readwrite_users;
GRANT USAGE ON SCHEMA exampledb TO readonly_users;

-- Not needed, but being explicit helps with /dn+
GRANT CREATE, USAGE ON SCHEMA exampledb TO owner;

Future-proofing

GRANT (and REVOKE) enables privileges for existing objects but not for new objects. This is accomplished with ALTER DEFAULT PRIVILEGES.

Let's configure privileges for the readwrite_users role. We want to ensure new objects like tables or indexes allow, well, read and write privileges for the users in the readwrite_users role.

ALTER DEFAULT PRIVILEGES
  FOR ROLE owner
  IN SCHEMA exampledb
  GRANT SELECT, INSERT, UPDATE, DELETE
  ON TABLES
  TO readwrite_users;

ALTER DEFAULT PRIVILEGES
  FOR ROLE owner
  IN SCHEMA exampledb
  GRANT USAGE, SELECT, UPDATE
  ON SEQUENCES
  TO readwrite_users;

ALTER DEFAULT PRIVILEGES
  FOR ROLE owner
  IN SCHEMA exampledb
  GRANT EXECUTE
  ON FUNCTIONS
  TO readwrite_users;

ALTER DEFAULT PRIVILEGES
  FOR ROLE owner
  IN SCHEMA exampledb
  GRANT USAGE
  ON TYPES
  TO readwrite_users;

As stated earlier, these statements rely on owner to create all objects in the database. Therefore, these statements do not include privileges that would allow a user in readwrite_users to create objects. Refer to the ALTER DEFAULT PRIVILEGES and Privileges documentation for details on the other privileges.

Additionally, I did not include a statement for future schemas because this guide doesn't support multiple schemas.

Now let's move onto the readonly_users role.

ALTER DEFAULT PRIVILEGES
  FOR ROLE owner
  IN SCHEMA exampledb
  GRANT SELECT
  ON TABLES
  TO readonly_users;

ALTER DEFAULT PRIVILEGES
  FOR ROLE owner
  IN SCHEMA exampledb
  GRANT USAGE, SELECT
  ON SEQUENCES
  TO readonly_users;

ALTER DEFAULT PRIVILEGES
  FOR ROLE owner
  IN SCHEMA exampledb
  GRANT EXECUTE
  ON FUNCTIONS
  TO readonly_users;

ALTER DEFAULT PRIVILEGES
  FOR ROLE owner
  IN SCHEMA exampledb
  GRANT USAGE
  ON TYPES
  TO readonly_users;

Finally, we want to ensure PUBLIC (a special role that means all users) does not have access to objects in our database. This forces users to belong to either the readwrite_users role or readonly_users role to access the database.

ALTER DEFAULT PRIVILEGES
  FOR ROLE owner
  REVOKE ALL PRIVILEGES
  ON TABLES
  FROM PUBLIC;

ALTER DEFAULT PRIVILEGES
  FOR ROLE owner
  REVOKE ALL PRIVILEGES
  ON SEQUENCES
  FROM PUBLIC;

ALTER DEFAULT PRIVILEGES
  FOR ROLE owner
  REVOKE ALL PRIVILEGES
  ON FUNCTIONS
  FROM PUBLIC;

ALTER DEFAULT PRIVILEGES
  FOR ROLE owner
  REVOKE ALL PRIVILEGES
  ON TYPES
  FROM PUBLIC;

ALTER DEFAULT PRIVILEGES
  FOR ROLE owner
  REVOKE ALL PRIVILEGES
  ON SCHEMAS
  FROM PUBLIC;

Once again, I favor explicitness for readability (and my sanity) because not all of the statements above are required. The default privileges for PUBLIC are documented, and a few of the statements above are redundant.

With these last statements, you will not need to work with GRANTS and ALTER DEFAULT PRIVILEGES again as long as you create users in the readwrite_users or readonly_users roles. WOOHOO!

Create the app User

The app user is what your web service will use to connect to the database with. app (usually) requires read and write permissions, so this user belongs to the readwrite_users role.

CREATE ROLE app WITH
	LOGIN 
	ENCRYPTED PASSWORD 'secret' 
	CONNECTION LIMIT 90 
	IN ROLE readwrite_users;
ALTER ROLE app SET statement_timeout = 1000;
ALTER ROLE app SET lock_timeout = 750;

-- v9.6+
ALTER ROLE app SET idle_in_transaction_session_timeout = 1000; 

ALTER ROLE app SET search_path = exampledb;
  • Change the password. :)
  • CONNECTION LIMIT 90: if you're using the default installation of postgres, the installer usually defaults max_connections to 100. Setting this to 90 ensures there are a few more connections available for administrators to hop in and wrangle queries in the event of an incident. Feel free to change this to match your database setup. If you need more connections, follow postgres' best practice of adding a connection pooler in front of the database.
  • statement_timeout: 1 second is a long time for a web request. 1 second for one query of many queries in a web request feels like an eternity to a user. Feel free to change this to suit your needs. I like pushing this as low as possible for my services. Keeping this low is important at scale: Twitch has a 1-second statement_timeout.
  • lock_timeout: 750ms might not seem much, but if you have the 1-second statement_timeout, this allows your query 249ms of execution time. Change as required.
  • idle_in_transaction_session_timeout: for postgres v9.6+, this ensures you do not have zombie transactions that maintain active connections. This guards the database against webservers that terminate requests and leave a connection open.

The GIST of It

Alright, alright! That was a lot of text to set up a database. Here's the gist. I use the same defaults and secrets above, so please change them!


Developer Basics

This guide's postgres configuration provides better defaults for scaling web services with databases than the defaults provided by a base installation of postgres. That said, this configuration may seem harsh and punishing if you're unfamiliar with writing efficient SQL. But, this is okay. Learn to write SQL efficiently while your web service and team scale up. While you're doing this, the configuration will help protect you from painful incidents like a tipped database. Nevertheless, let's discuss a few points to keep in mind.

The app user has SELECT, INSERT, UPDATE, and DELETE privileges for all tables created by the owner user. app is the only user your web service connects with to handle web requests. SQL queries sent with the app user should be fast, or the settings for the app user will terminate the statement. If your application code attempts to create tables, indexes, or other postgres objects, you will be on the receiving end of a permission failure. To solve this, use the owner user.

The owner user manages all tables and indexes for your application's database. The management of the database schema should happen outside of the webserver process -- in an admin process like rake db:migrate or alembic upgrade head. The owner user is not designed for web service requests. You want to create the structure of the database with owner, but manage the data in that structure with app.

Finally, you can override setting values if needed. The defaults are guard rails, not jail bars. You can jump over the rail if you know what you're doing.

Schema Changes as a superuser

Hopefully, you don't need to do this as schema migrations with owner should suffice. But, incidents happen and maybe the quickest way to stop the bleeding is to hop onto the database and wrangle some code.

SET ROLE owner;
-- ALTER TABLE ...
-- ...
RESET ROLE;

Schema Changes (via Migrations) and Local Development

Use the owner's credentials to run migrations. If your application follows the Twelve-Factor App's Config Factor, then the owner credentials can be passed in via environment variables in the form of a postgres connection URI. Here's an example of what the URI would look like for a local database:

postgres://owner:secret@127.0.0.1:5432/exampledb 

If you're using rake db:migrate, set the DATABASE_URL environment variable to the owner credentials.

If you're using alembic upgrade, ensure EnvironmentContext.configure passes in the database URI from the environment, and you can swap out database URIs as mentioned. e.g.,

# env.py
import os
from alembic import context

context.configure(url=os.getenv("DATABASE_URL", "localhost"))

Similar strategies are available in your migration tool of choice.

List Users

List users and roles:

\du+

List users and their settings (only as a superuser):

SELECT usename, useconfig FROM pg_shadow;

Add User

We only add users to readwrite_users or readonly_users.

CREATE ROLE newuser WITH
	LOGIN
	ENCRYPTED PASSWORD 'secret'
	CONNECTION LIMIT 3
	IN ROLE readwrite_users;
ALTER ROLE newuser SET search_path = exampledb;

-- Recommended connection settings
ALTER ROLE newuser SET statement_timeout = 1000;
ALTER ROLE newuser SET lock_timeout = 750;
-- v9.6+
ALTER ROLE newuser SET idle_in_transaction_session_timeout = 1000; 

Tweak the IN ROLE, CONNECTION LIMIT, and settings to fit your needs.

Drop/Remove User

Connect as the superuser and execute the following:

-- Change password
ALTER ROLE myuser ENCRYPTED PASSWORD 'somethingbogus';

-- Terminate existing queries/connections
SELECT pg_terminate_backend(pg_stat_activity.pid) 
FROM pg_stat_activity
WHERE usename = 'myuser';

-- Now remove the user
DROP USER myuser;

Changing the user password stops new connections from clients. Terminating existing queries ensures that the targeted drop is no longer connected to the database before dropping the user.

Update User Settings

Connect as the superuser and execute the following:

ALTER ROLE myuser SET statement_timeout = 5000;

-- Optional, terminate existing queries
SELECT pg_terminate_backend(pg_stat_activity.pid) 
FROM pg_stat_activity
WHERE usename = 'myuser';

Settings are applied at connection time. Therefore, you need to have your application or developer reconnect after the change.

app Password Rotation

Connect as the superuser and follow these steps:

  1. Change the app password: ALTER ROLE myuser ENCRYPTED PASSWORD 'newpassword'
  2. Update the credentials wherever you store them (e.g., heroku settings, chef data bags, etc)
  3. Restart your application to use new credentials.

This strategy does have a flaw. The time between Step 1 and Step 3 may raise exceptions from the application if you're using webservers that cancel requests (Rack::Timeout, g/Unicorn, etc) and reconnect to the database. You'll want to execute the above steps rapidly.

Dynamically Set Settings From Your Application

The database is configured with default settings like statement_timeout for app, but these can be overridden from the application code. A quick first pass might look like:

-- WARNING! Leaky settings! WARNING!
SET statement_timeout TO 5000; 
-- ... execute query ...
RESET statement_timeout;

This is not a full-proof method because you may leak setting values. If you're using a connection pooler like pgbouncer, then executing this statement from the application will enable this setting for the life of the connection. The connection pooler shares this connection with other requests, so you must call RESET statement_timeout after you've completed your query. Otherwise, the setting value lasts until the connection is closed, a new SET statement_timeout query is executed, or if RESET statement_timeout is executed. But even if you executed RESET, leaking the value to other requests may still occur with long-request-terminating webservers.

A better method is to change the setting in a transaction using SET LOCAL:

BEGIN;
SET LOCAL statement_timeout = 5000;
-- execute queries
COMMIT; -- or ROLLBACK

Now, when the transaction is complete (COMMIT or ROLLBACK), statement_timeout is reset. Additionally, if your webserver terminates long requests during a transaction, then idle_in_transaction_session_timeout will terminate the connection and clean up the setting set by the application.

Query Errors

With all this talk of query cancellations, what are the errors that occur? Let's have a look.

ERROR:  canceling statement due to statement timeout

You see this error message when a query surpasses the statement_timeout value.

If possible, rework the query (maybe it's missing an index?) and then try again. If this strategy doesn't work, you can increase statement_timeout for the duration of this query.

ERROR:  canceling statement due to lock timeout

You see this error message when a query attempts to acquire a lock and surpasses the lock_timeout value.

This one isn't as easy (or difficult) as fixing statement_timeout. For this error, you'll need to make sure that your application queries hold locks for as little as possible. Or you can delay the queries. You can rearchitect web requests so that the locks happen out of the web request cycle -- in an asynchronous job.

FATAL:  terminating connection due to idle-in-transaction timeout

You see this error message when you open a transaction and idle for longer than idle_in_transaction_session_timeout.

You can fix this by rearranging your queries to ensure there are no long pauses in between queries sent to the database.


Outro

This configuration described in this guide is what I use for web services now. I even use this for personal projects that don't have the data size or team size at work! That said, this is my default setup for web services and it has served (and serving) me well. I hope it works for you.