How to Configure Postgres Read Only User
You configure a postgres read only user by creating a database role with login rights and granting it read rights on your target schemas and tables. You also set default rights so the role can read future tables. Many engineering teams start by sharing one broad database account across developers and analytics tools. Overly broad roles expose production data to accidental writes table drops, and blocked data exports. How a postgres read only user works in practice PostgreSQL manages database
Your coding agents run with your access.
Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.
You configure a postgres read only user by creating a database role with login rights and granting it read rights on your target schemas and tables. You also set default rights so the role can read future tables. Many engineering teams start by sharing one broad database account across developers and analytics tools. Overly broad roles expose production data to accidental writes table drops, and blocked data exports.
How a postgres read only user works in practice
PostgreSQL manages database grants through roles. A role can act as an single user or as A group role that holds shared rights. When you manage many users, you create a group role for read access and grant single login roles membership in that group.
To build this setup, an admin first grants the role grants to connect to the target database next, the admin grants usage rights on the relevant schemas. Finally, the admin grants the select rights on existing tables. Views, and materialized views inside those schemas in PostgreSQL, the select rights allows a role to read from an entire table or from exact columns. It also permits exporting table data through the copy task.
Grants granted on existing tables do not carry over to new tables created later. Admins alter default rights in the schema so future objects grant read rights to the group role upon creation. Teams also check the special public role, which represents every user on the system by default, the public role receives database connection rights. Temporary table creation rights, and execution rights on functions and procedures in PostgreSQL 15 and later, the default setup blocks general users from creating new objects in the public schema on older or upgraded databases. Admins must revoke creation rights on the public schema from the public role.
Why role management is only the first step
Native database roles control who can log in and which objects they can read or modify. Setting up a read role stops engineers and reporting tools from deleting rows or altering table structures. This setup solves basic grants boundaries at the database layer.
Still, static database roles leave an daily gap during daily fixing and support work. A read rights is binary at the table or column level. Once a user authenticates and runs a query the database returns every matching row in plain text because the select rights also allows full table exports. Any user with a read role can pull customer records. Payment details, or personal IDs down to a local machine.
Read-Only Root Filesystem + User Provisioning (SCIM): architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Maintaining column-level grants across hundreds of changing tables. Creates heavy admin toil. Database migrations add new columns every week. A single missed default rights rule either breaks user queries or exposes restricted columns. Also, shared read keys make auditing difficult. Database logs only show the shared role name. The single engineer who ran the query.
Adding query and data controls to database sessions
Securing production data requires controls that inspect single statements and results in flight. Instead of relying only on static grants inside the database teams benefit from linking every query to a verified. Human identity from their identity provider. When a developer runs a select query against a production table field-level controls can mask sensitive values in the result set the data reaches the client screen. This approach lets engineers inspect app state and debug issues. Keeping personal data hidden and leaving a clear audit trail of every statement executed.
Add query and result controls beside Postgres
Grants and pools govern who logs into PostgreSQL. Yet every query in an open session still runs with that role's full scope. In practice, hoop.dev sits inline in the wire protocol beside your database as a sidecar for your Postgres. For every plain or prepared query an agent or human runs against production, hoop.dev decides to pass, mask, block, or escalate it for review.
When a tool or agent sends SQL, hoop.dev Postgres controls block banned statements before execution or hold risky ones for human approval via an AI Analyzer you configure. Matched sensitive columns in result rows are masked in memory before they reach the caller. Each query and decision is logged per listener, which generates evidence for SOC 2, HIPAA, and GDPR audits. View the binary on GitHub.
Frequently asked questions
Can a read role still execute functions or create temporary tables?
Yes by default, PostgreSQL grants the special public role grants to create. Temporary tables and execute functions and procedures. Every new role inherits rights from the public role an object owner revokes those default grants.
Does the select privilege allow bulk data exports?
Yes in PostgreSQL, granting the select rights on a table or view also allows the user to export that data using the copy task. You can restrict the select rights to exact columns if you want to block access to sensitive fields on a table.
Why can my read user create tables in the public schema?
Databases running versions older than PostgreSQL 15. Databases upgraded from those older versions. Allow all users to create objects in the public schema by default. An admin must revoke schema creation rights from the public role to stop read users from creating new objects.
Related guides
In this post