Read-only Connections

If you want to connect to a PostgreSQL server, but want to prevent accidental changes to the database, there are two ways you can achieve this with Postico.

Quick and easy: Transaction Mode

The easiest way to prevent accidental modifications of the database is to set the transaction characteristics to "READ ONLY".

In the server connection settings, check the "Startup Query" checkbox and type the following query into the text field:

SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;

Postico will then automatically execute this query after the connection was established, and attempts to modify the database will fail.

If you do want to make changes to the database, you can set the session back to read write by executing the following SQL query:

SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE;

Safe and flexible: Read-only roles

The more flexible approach is to create a read-only role for connecting to the database.

First, create a new role:

CREATE ROLE read_only_role NOINHERIT;

Then grant the privileges you want the read-only role to have, and revoke default privileges if needed.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_role;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only_role;
REVOKE EXECUTE ON ALL ROUTINES IN SCHEMA public FROM read_only_role;

Now use this restricted role to connect to the database, or if that's not possible, create a startup query that switches to this role with the command "SET ROLE read_only_role".

To allow making changes, you can create a separate read-write role:

Create a read/write role: CREATE ROLE read_write_role;
Grant write permissions to the read/write role
Make sure the read-only role does not inherit from other roles: ALTER ROLE read_only_role NOINHERIT
Allow the read-only role to switch to the read-write role using "GRANT read_write_role TO read_only_role"

Now you can switch to the read/write role using the SET ROLE command: SET ROLE read_write_role or switch back again with SET ROLE read_only_role;

Use caution when connecting to production servers

A lot of people look for a way to make a connection read-only so they can safely connect to production servers. However, making the connection read-only is not going to make this safe. Even with a read-only connection, you can cause problems when connecting to a production server.

We do not recommend connecting to production servers with Postico (or any other GUI tool). The problem is that GUI tools don't allow you to control which queries are sent, and even read-only queries can have harmful effects.

For example, Postico by default counts rows in tables. When tables are very large, this can cause performance degradations on the production server.

Also, long running transactions can prevent or delay certain tasks that need to wait for transactions to finish. Postico generally doesn't leave transactions open, but long running queries or manually started transactions can have this problem.

When you connect to production servers:

Disable the "always count rows" preference in Postico.
Avoid long running or expensive queries that could impact application performance.
Make sure to close all windows and tabs once you are done to avoid lingering connections.
We do not recommend connecting to production servers. If something goes wrong, it's your fault.