psql Read-Only by Default

2026-01-15

When running ad-hoc queries against a production database, there’s always a risk of accidentally running a destructive query. A stray UPDATE without a WHERE clause or an unintended DELETE can cause significant damage.

A simple way to protect against this is to run psql in read-only mode by default. PostgreSQL has a session setting called default_transaction_read_only that does exactly this. When enabled, any DML (INSERT, UPDATE, DELETE) or DDL will fail with an error like cannot execute INSERT in a read-only transaction.

To enable this by default, add the following to your .psqlrc file:

SET default_transaction_read_only = on;

This makes all sessions read-only by default. But what about when you actually need to write something?

The default_transaction_read_only setting is just the default transaction mode, and can be overridden on a per-transaction basis:

BEGIN READ WRITE;
-- your writes here
COMMIT;

This approach has a nice property: write operations become intentional and explicit. You can’t accidentally run a destructive query because you have to consciously start a read-write transaction first.

There are other ways to achieve a similar result, such as creating a shell alias that sets the flag off or using a database role with only SELECT privileges. But the .psqlrc setting combined with BEGIN READ WRITE is simple, requires no additional tooling, and makes write intent visible in your query history.