Tue 08 September 2015

Read-only Postgres database

There are times when I've needed to effectively make a Postgres database read-only, for example after loading or updating reference data. In the past I've revoked permissions from database roles to achieve this but as this post on the PostgreSQL list states, it's possible to do so by making all transactions against the database read-only by default.

For example assuming I have an osdata database, I can alter it to set default_transaction_read_only (docs) to true which will make future transitions read-only regardless of which login role connects:

ALTER DATABASE osdata SET default_transaction_read_only = true;

As the name implies setting default_transaction_read_only to true only sets transactions to be read-only by default, a user can override or unset the setting on a per-session basis e.g. set default_transaction_read_only = false;.