How to Configure RDS Postgres Backup User
To set up an rds postgres backup user, create a dedicated login role. Then grant read rights on your target schemas and tables. Teams often reuse their main app account for database dumps instead. This shortcut gives backup jobs the power to change or delete live data. A stolen backup key can then put the whole database at risk. Setting up a read-only role stops backup tasks from writing to tables. How to set up an rds postgres backup user Start by creating a group role that holds your read-o
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 backup user, create a dedicated login role. Then grant read rights on your target schemas and tables. Teams often reuse their main app account for database dumps instead. This shortcut gives backup jobs the power to change or delete live data. A stolen backup key can then put the whole database at risk. Setting up a read-only role stops backup tasks from writing to tables.
How to set up an rds postgres backup user
Start by creating a group role that holds your read-only rights. PostgreSQL lets you set up group roles to manage rights for many users. You then grant your new login user membership in that group role. This keeps your permission setup clean as you add more tools.
Next, grant the read privilege on the tables you want to export. In PostgreSQL, the select privilege allows reading from a table or view. It also allows copying data out of the table during a backup. You can even grant this read right on specific columns if needed. For full table dumps, you grant it across all tables in the schema.
You must also check what the default public role can do. The special public role grants rights to every role on the system. By default, public gets the right to connect to databases. It also gets the right to make temporary tables and run functions. The object owner can revoke these default rights to lock down the account. In PostgreSQL 15 and later, regular users cannot create objects in the public schema. If you upgraded from an older version, revoke that create right from public.
Why default privileges matter for new tables
Granting read rights on current tables only covers objects that exist today. Tomorrow, a developer or migration script might create a brand new table. Your backup account will not have rights to read that new table. The next export job will fail or skip the new data.
To fix this, you must alter default privileges for the schemas you back up. Default privileges apply to new objects created by a specific role. If your migration role builds tables, change the defaults for that migration role. Tell the database to grant read rights to your backup group on new tables. Now your rds postgres backup user can read future tables without manual fixes.
User Provisioning (SCIM) + End-to-End Encryption: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
What native roles solve and what they leave open
Native database roles do a great job of blocking unwanted writes. A read-only account cannot drop tables or alter rows during an export. This setup decides who can log in and which objects they can read. It protects your database integrity if a script has a bug.
Still, static roles cannot inspect or control what happens inside an active session. Once the backup role logs in, it can read every row in full by default. Native roles do not rewrite sensitive fields like emails or phone numbers in results. They also cannot mask card numbers or national IDs during a dump. If an engineer uses the backup login for testing, they pull raw data. Also, static passwords for backup jobs often sit in scripts for months. Managing and rotating those long-lived keys takes constant work from your team.
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
Why did my backup fail after a database migration?
The migration role likely created new tables without granting read rights on them. Update the default privileges for the role that runs your schema changes. This ensures your backup group gets read access on all future tables.
Can a read-only backup role still run functions?
Yes, the special public role gets the right to run functions by default. Every database user inherits the rights given to the public role. The object owner can revoke this execute right from public to block it.
Can I limit a backup user to specific columns?
Yes, PostgreSQL lets you grant the select privilege on specific columns of a table. Still, most standard dump tools expect to read the entire table at once. Column grants work best when you export data using custom queries.
Related guides
In this post