How to Configure Redshift Read Only User
You set up a redshift read only user by granting schema usage and select permissions. You also set default privileges so the role can read future tables. Many data teams start by giving engineers broad database roles to speed up work. Broad permissions let a single bad query alter tables or expose private rows. How a redshift read only user works in practice Amazon Redshift manages permissions at the database, schema, and table levels. First, an admin creates a dedicated user account or a sha
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 a redshift read only user by granting schema usage and select permissions. You also set default privileges so the role can read future tables. Many data teams start by giving engineers broad database roles to speed up work. Broad permissions let a single bad query alter tables or expose private rows.
How a redshift read only user works in practice
Amazon Redshift manages permissions at the database, schema, and table levels. First, an admin creates a dedicated user account or a shared group role. Next, the admin grants connect permissions on the target database to that role. Database access alone does not let a person read any data inside.
The admin must also grant usage rights on each specific schema. Without schema usage, the user cannot see or list any tables inside. After that, the admin grants select rights on all current tables and views. This step lets the person run read queries against existing data.
Teams often forget that new tables do not inherit these select rules. When an app or pipeline builds a new table, the read role hits errors. To fix this, the admin alters default privileges for the schema owner. You must apply this default rule for every account that creates tables. If three different accounts create tables, you need three default privilege rules.
How to organize groups and roles for growth
Managing permissions on single accounts gets messy as your engineering team grows. When ten analysts join, granting table rights ten times takes too much effort. A better approach uses role-based groups inside the data warehouse. Admins create one read group and attach all schema permissions to that group.
When a new person joins, the admin adds their account to the group. When someone leaves the team, removing their account revokes all read rights at once. You can also split schemas by team to keep sensitive finance tables separate. An analyst group gets usage on analytics schemas while finance schemas stay locked.
Read-Only Root Filesystem + User Provisioning (SCIM): architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
What native roles solve and what they miss
Native database roles stop accidental writes, table drops, and schema changes. They make sure an analyst or support engineer only runs select queries. This setup protects data integrity across your main warehouse tables. Still, native roles only check who logs in and which tables they query.
Once a user has select rights on a table, they read every column by default. If that table holds customer emails, phone numbers, or card numbers, everything returns. Static permissions cannot rewrite sensitive fields inside a query result on the fly. They also require admins to distribute database passwords or manage static keys on laptops.
Teams also struggle to link shared read accounts back to individual people in logs. When five engineers share one read login, the query log shows one name. Security teams cannot tell who ran a slow or broad export query. Closing these gaps requires controls that inspect queries and results in flight.
Check database and API queries on the wire
Identity tools, roles, and network rules decide who opens a session. Setup alone cannot check what each query does or mask sensitive values in a response once a session starts. When workflows touch Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, or internal HTTP APIs, hoop.dev sits inline in the wire protocol beside those services as an open-source proxy, a sidecar for your Postgres or API. On every query an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review.
Your login details and client tools stay as they are while hoop.dev guardrails refuse bad deletes or drops before they run, and hoop.dev data masking rewrites matched sensitive values in responses in memory. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and writes every query and decision to an audit trail that generates evidence for HIPAA, GDPR, and PCI DSS. See the source on GitHub.
Frequently asked questions
Why can my read only user not see newly created tables?
Default privileges in Redshift apply only to the specific user who creates the table. You must alter default privileges for every pipeline account or admin that builds tables.
Can a read only user still create temporary tables?
Only if the user has temporary privileges granted at the database level. By default, connect privileges allow temporary table creation unless an admin revokes that right.
How do I protect sensitive columns from a read only role?
You can grant select rights on specific columns or build filtered database views. Alternatively, you can use runtime data masking to rewrite emails and card numbers in results.
Related guides
In this post