How to Configure Amazon Q Postgres Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up amazon q postgres read only access by making a restricted database role. You then give that role read rights on chosen tables and columns. Teams often start by handing an AI assistant their normal developer login. That approach creates huge risk when the role has broad write or delete rights. A single bad prompt can make the tool change rows or drop tables. Setting up a dedicated read role stops the agent from changing stored data. How to set up amazon q postgres read only access P

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 set up amazon q postgres read only access by making a restricted database role. You then give that role read rights on chosen tables and columns. Teams often start by handing an AI assistant their normal developer login. That approach creates huge risk when the role has broad write or delete rights. A single bad prompt can make the tool change rows or drop tables. Setting up a dedicated read role stops the agent from changing stored data.

How to set up amazon q postgres read only access

PostgreSQL manages read rights through roles and object privileges. Instead of setting up rules for one user at a time, teams make a group role. Admins grant read rights to that group role and add the agent login as a member. The core read right in PostgreSQL is the select privilege. This privilege lets a role read from a whole table, a view, or specific columns. It also allows the role to export table data using the copy command.

To lock down a new role, you must check the built-in public role first. Every database role gets the rights given to the public role by default. Out of the box, the public role can log in to databases and make temporary tables. It can also run functions and procedures across the database. If an existing function writes data, a read role could run it and change rows. The object owner must take away these default public rights to lock down the database.

Schema rules also matter when you set up a safe database role. In PostgreSQL 15 and newer versions, regular users cannot make objects in the public schema. Older databases, or ones upgraded from older releases, still allow this by default. On those older systems, an admin must take away schema create rights from the public role. You can also limit the select privilege to safe columns on a table. That way, the role only reads the columns you explicitly allow.

What native database roles solve and what they miss

A native read role does a good job of blocking direct writes and schema changes. It ensures the AI tool cannot run update, insert, or delete statements on your tables. If you restrict column rights, you also block queries that ask for unapproved columns. For basic schema checks and simple data lookups, native roles form a solid first line.

Still, static database roles only check who logs in and which objects they touch. Once the session starts, the database does not check the context of each query in flight. First, a read role still has full access to every allowed column in a table. If the agent queries a customer table, the database returns raw emails and phone numbers. Those raw values then flow straight into the AI context window and external model logs. Static column rules are all or nothing, while dynamic masking rewrites matched fields in results.

Auditor Read-Only Access + Customer Support Access to Production: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Second, developers still have to store the database login key on their local laptops. The AI coding agent reads this local key from a file or shell variable to log in. Anyone or any process on that machine can take and reuse that stored key. Third, database audit logs only show the shared role name, not which human triggered the query. You cannot link a strange select query back to the exact prompt or user session.

Why query level controls matter for AI agents

AI coding tools work fast and run many queries to explore a database schema. A read role cannot stop an agent from running a huge join that locks up memory. It also cannot ask a human to approve a query that scans a sensitive table. Teams need controls that inspect each statement in flight and mask sensitive fields like card numbers. Managing keys and checking queries outside the local machine keeps data safe during AI sessions.

Run guardrails and masking on the Postgres connection

Role grants and firewall rules are needed setup. Setup alone cannot check single SQL statements or mask sensitive rows in a response. By design, hoop.dev sits inline in the Postgres protocol beside Postgres, RDS, or Aurora as a sidecar for your Postgres. On every production query from an engineer or AI agent, hoop.dev decides whether to pass, mask, block, or hold it for approval.

First, hoop.dev guardrails refuse disallowed drops or deletes before they run, and risky statements can pause for human review. Next, hoop.dev data masking rewrites sensitive fields in returned rows in memory. Furthermore, hoop.dev holds SOC 2 Type II and logs every statement and verdict per resource. Check out the project on GitHub.

Frequently asked questions

Can a PostgreSQL read role still change data through functions?

Yes, the built-in public role gets the right to run functions and procedures by default. If a function changes tables, a read role can call it unless the owner revokes execute rights.

Does granting select on a table allow bulk data exports?

Yes, the PostgreSQL select privilege also allows the role to run the copy command on that table. An agent with select rights can export entire tables unless you block bulk queries in flight.

How do older PostgreSQL versions handle the public schema for read roles?

Databases older than PostgreSQL 15 let the public role create new objects in the public schema. An admin must revoke create rights on the public schema so the read role cannot make tables.

The query runs. The emails stay hidden.