How to Configure RDS Postgres Service Account
To set up an rds postgres service account, create a login role with narrow table rights. Next, revoke extra default rights and assign the account to a group role. Many teams start by sharing one broad database role across multiple apps and workers. When permissions are too wide, a single compromised app or bad query can alter production data. How to set up an rds postgres service account PostgreSQL manages database permissions through roles. A role can act as a single user, a group, or an app
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.
To set up an rds postgres service account, create a login role with narrow table rights. Next, revoke extra default rights and assign the account to a group role. Many teams start by sharing one broad database role across multiple apps and workers. When permissions are too wide, a single compromised app or bad query can alter production data.
How to set up an rds postgres service account
PostgreSQL manages database permissions through roles. A role can act as a single user, a group, or an app account. Instead of granting rights to each account one by one, teams create group roles. You grant table rights to the group role first. Then you add the rds postgres service account as a member of that group.
Every new role inherits rights from a special system role called PUBLIC. By default, PUBLIC allows every role to connect to the database and create temporary tables. It also lets every role run functions and procedures. The database owner can revoke these default PUBLIC privileges to lock down the instance.
Schema rules also matter when you set up an account. In PostgreSQL 15 and later, regular users cannot create new objects in the public schema by default. On older versions or upgraded databases, an admin must revoke that create right from PUBLIC. This step stops an app from making rogue tables in your main schema.
Once the defaults are clean, grant only the specific commands the app needs. For example, the SELECT privilege lets the role read from a table or view. You can grant SELECT on an entire table or restrict it to specific columns. Keep in mind that SELECT also allows the role to export data with COPY TO.
How to manage permissions as schemas change
Database schemas change often as teams ship new features. Standard table grants only apply to tables that exist right now. When a migration creates a new table, your service account will not have rights to it by default. Admins must update default privilege rules or include grant steps in every migration script.
Service-to-Service Authentication + Service Account Governance: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Splitting accounts by task also keeps your database safer. A background worker that only reads reports should not share a role with your main web app. Give the reader worker a read-only group role, and give the web app its own write role. If a worker has a bug, it cannot delete rows.
What native database roles solve and what they leave open
Native roles and grants do a good job of controlling who can log in. They also define which tables and columns an account is allowed to touch. This setup blocks unauthorized users from reading restricted tables or dropping schemas.
Still, static grants only check permissions at the table or column level. They do not inspect what individual statements do while a session is active. A role with read access to a customer table can pull one row or dump millions. If that table holds emails, phone numbers, card numbers, or national IDs, SELECT returns all of them. Native grants also cannot pause a risky update to ask a human for approval first. Once the account logs in, every allowed command runs right away.
Check every Postgres query on the wire
Database roles and network rules decide who can open a session. They do not read what each SQL statement does after login. With hoop.dev, you run an open-source proxy, a sidecar for your Postgres, inline in the Postgres wire protocol. 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 apps and agents keep their login details and change only the host and port in the connection string their tool uses.
Before a query runs, hoop.dev guardrails refuse bad deletes or drops with a real Postgres error, or hold risky queries for approval. On the way back, hoop.dev data masking rewrites sensitive values in returned rows in memory, and every verdict goes to the audit trail. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and generates evidence for HIPAA, GDPR, and PCI DSS. See the code on GitHub.
Frequently asked questions
Can I restrict an rds postgres service account to specific columns?
Yes, PostgreSQL lets you grant the SELECT privilege on specific columns of a table or view. This stops the account from querying other columns in that same table. You must list the allowed columns when you apply the grant.
Why can a new service account run functions without explicit grants?
PostgreSQL grants default rights to a built-in role named PUBLIC, which every role inherits. By default, PUBLIC has the right to connect, make temporary tables, and run functions. An admin must revoke these rights from PUBLIC to restrict new accounts.
Why did my service account lose access after a schema migration?
Table grants only cover objects that exist when you run the grant command. When a migration creates a new table, existing service roles do not get access by default. You must alter default privileges for the role that runs migrations or add grants to the script.
Related guides
In this post