Read-only PostgreSQL connections: three layers

By Anil Gautam · Published and reviewed September 20, 2026

For routine PostgreSQL inspection, use an account with only the access you need. Turn on the client’s read-only setting too, but remember that the database permissions decide what that account can change.

Start with a dedicated role

Ask the database owner for an inspection role limited to the required databases, schemas, tables and views. Do not reuse the application owner or an administrative credential just because it connects successfully. Check access to existing tables and what happens when a migration creates a new one.

PostgreSQL privileges are described in the privileges guide. Granting broad access to make an error disappear may disclose more rows or schemas than the task requires.

Use a read-only transaction for an inspection session

On the same PostgreSQL connection, with no transaction already open, this example checks the transaction setting and then ends the transaction.

BEGIN READ ONLY;
SHOW transaction_read_only;
SELECT current_database(), current_user;
ROLLBACK;

Do not leave the transaction idle after your investigation. Read-only transaction behavior has engine-defined limits; it is not a replacement for a carefully scoped role. Consult SET TRANSACTION before relying on it as a boundary.

Turn on the client guardrail too

plainDB has a read-only connection setting on both Mac and Windows. Combine it with clear environment labels and a dedicated database role. The UI setting helps prevent accidental operations during ordinary browsing; a credential reused outside the app still has whatever permissions PostgreSQL grants it.

Test the permission design safely

Have the database owner validate the role against a disposable test environment. Record the objects that can be read and the operations that should be denied. Include views and callable functions in the review rather than looking only at table grants. For Supabase, also distinguish a direct SQL role from API requests governed by an end user's authentication context.

When a legitimate inspection query fails, report the exact object and operation. Request the narrow permission required for that task. This creates a reviewable change and keeps the next troubleshooting session from quietly escalating into administrative access.

Continue reading

Understand edit review · SQL access versus API access

For the desktop workflow, compare plainDB features on Mac and Windows or download the 14-day trial.