How to Configure Aurora Postgres Grant Permissions

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up aurora postgres grant permissions by creating group roles and assigning table rights. Then you place user accounts into those group roles. Most teams start by sharing one broad login across apps and engineers. Shared accounts often carry full write rights on every table. That setup makes it easy for one bad query to drop production data. A clear role model limits what each person or app can read and write. How aurora postgres grant permissions work The database engine uses roles to

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 aurora postgres grant permissions by creating group roles and assigning table rights. Then you place user accounts into those group roles. Most teams start by sharing one broad login across apps and engineers. Shared accounts often carry full write rights on every table. That setup makes it easy for one bad query to drop production data. A clear role model limits what each person or app can read and write.

How aurora postgres grant permissions work

The database engine uses roles to manage all user and group rights. Instead of granting rights to each user, you create a group role first. You then assign database, schema, and table rights to that group role. When a new engineer joins the team, you add their account to the group. When they leave, you remove them from the group role. This keeps permission management simple as your team grows.

Read rights can apply to whole tables or to specific columns. The read privilege lets a role read data from tables, views, or materialized views. It also lets the role export data out of those objects. If a table holds sensitive columns, you can grant read rights on safe columns only. You also need to check the special role named PUBLIC. Every role on the system gets the rights assigned to PUBLIC.

Default rights and schema rules

By default, PUBLIC gets the right to log in to databases and make temporary tables. It also gets the right to run functions and procedures. The owner of an object can remove these default rights at any time. In PostgreSQL 15 and newer, regular users cannot create objects in the public schema. Older databases, or ones upgraded from older releases, may still allow this behavior. On those older systems, an admin must revoke creation rights on the public schema from PUBLIC.

New tables do not pick up past table grants on their own. You must set default rules for future objects created by your migration role. Otherwise, your read group will fail to read new tables after a deployment. The creator of the new tables must be the role attached to those default rules. If another admin creates a table by hand, the default rule will not apply. Teams often use one shared migration role to avoid this problem.

AI Agent Permissions + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Where native database roles fall short

Native database roles decide who can log in and which objects they can touch. Once a user logs in, the database trusts every valid query from that session. A user with read rights on a customer table can pull every row at once by default. Column grants also require constant updates whenever schemas change. If an engineer needs temporary write access to fix a bug, admins must alter roles by hand. Those temporary grants often stay active long after the fix is done.

Static roles also cannot mask sensitive values inside a column during a query. Either a role has the right to read the email or card number column, or the query fails. Many support workflows need the query to run while hiding raw emails or phone numbers. Relying only on native grants forces teams to build separate views for every team. That adds extra work each time a table changes.

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 I grant read access to only a few columns in a table?

Yes, the read privilege can target specific columns on a table or view. The user must specify only those allowed columns when they run a query. Selecting all columns at once will fail if any column lacks the grant by default.

Why can new users still run functions without explicit grants?

The database grants function execution rights to the special PUBLIC role by default. Every user belongs to PUBLIC, so you must revoke that default right to restrict functions.

How do I stop users from creating tables in the public schema?

PostgreSQL 15 and later block this by default on new databases. On older or upgraded databases, an admin must revoke schema creation rights from the PUBLIC role.

The query runs. The emails stay hidden.