My GOTO Postgres Configuration for Web Services
My guide to setting up postgres 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
- The End Result
- The Upsides
- The Downsides
- Walkthrough: Configuring Postgres
- The Gist of It
- Developer Basics
- Outro
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(see update for RDS) accesssuperuser
-like (e.g.,rds_superuser
) - 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 theowner
. - A role
readonly_users
that enables read-only access to all objects created by theowner
. - An
app
user that belongs toreadwrite_role
that has connection settings to guard against scaling problems (statement_timeout
,lock_timeout
,idle_in_transaction_session_timeout
,connection_limit
, andsearch_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
andlock_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 theapp
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 allowsowner
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 tolock_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;
GRANT
s 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 defaultsmax_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-secondstatement_timeout
.lock_timeout
: 750ms might not seem much, but if you have the 1-secondstatement_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:[email protected]: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:
- Change the
app
password:ALTER ROLE myuser ENCRYPTED PASSWORD 'newpassword'
- Update the credentials wherever you store them (e.g., heroku settings, chef data bags, etc)
- 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.