How to Configure Aurora Postgres Create User

Published
Length
3 min read

Luis Felipe Dias

All posts

You run an aurora postgres create user workflow by setting up a login role. Next, you attach explicit schema and table rights to that new role. Many teams hurry through this step and reuse one shared admin account. Broad roles let any user or script alter tables or dump customer records. A safer plan uses separate accounts and strict group permissions for each person. How the aurora postgres create user workflow works In PostgreSQL, users and groups are both database roles. The create user ta

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 run an aurora postgres create user workflow by setting up a login role. Next, you attach explicit schema and table rights to that new role. Many teams hurry through this step and reuse one shared admin account. Broad roles let any user or script alter tables or dump customer records. A safer plan uses separate accounts and strict group permissions for each person.

How the aurora postgres create user workflow works

In PostgreSQL, users and groups are both database roles. The create user task sets up a role with login rights. A new login role cannot read your custom tables right away. Yet every new role gets default rights from a built-in group called PUBLIC. By default, PUBLIC lets any role log in to the database. It also lets any role make temp tables and run functions. If you want tight security, the object owner can take back these defaults.

In PostgreSQL 15 and newer, users cannot make objects in the public schema. Older databases, or upgraded ones, still let PUBLIC make new objects there. In those older databases, an admin must take back that right from PUBLIC. Once you lock down defaults, you can build group roles. You grant table and schema rights to a group role first. Then you add each person to the right group role. This keeps setup simple as your engineering team grows.

Scoping read rights and default privileges

After you make a group role, you must grant read or write rights. The SELECT right lets a role read from tables and views. You can grant SELECT on a whole table or only on specific columns. Column rules help when a table holds emails, phone numbers, or card numbers. Keep in mind that SELECT also lets a user copy table data out.

Standard table grants only cover the tables that exist right now. If a migration adds a table tomorrow, your group role cannot read it. Admins fix this by changing default rights for future objects. Still, those default rules only apply to objects made by one specific role. If two roles run migrations, you must set default rules for both roles. Missing one migration role breaks read access for the whole team.

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 roles and group memberships control who can log in to the cluster. They also set the baseline tables a user can read or change. This works well for static apps that run the same queries every day.

Still, native roles only check permissions when a session starts or touches a table. They do not inspect what individual queries do in flight. A developer with read rights on a customer table can pull every row. If you skip column grants, query results will show raw national IDs and emails. Sharing static database passwords also creates risk when people leave the team. Even with strict group roles, you still lack a single audit log across clusters. You also cannot ask a manager to approve one risky query in real time. Teams need a way to inspect queries and mask fields like card numbers.

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 can a new user still make tables in the public schema?

Databases made before PostgreSQL 15 give the PUBLIC group create rights by default. Even if you upgrade the database, those old default grants remain in place. An admin must revoke the create right on the public schema from PUBLIC.

How do I let a user read only safe columns in a table?

PostgreSQL lets you grant the SELECT right on specific columns instead of everything. You list the safe columns when you grant rights to the group role. The user can then query those specific columns without reading restricted columns.

Why do my read-only users lose access to new tables after a deploy?

Standard grants only cover tables that exist at the moment you run the grant. Default privileges only cover new tables made by the exact role you specified. If a different deploy role builds a table, you must add rules for it.

The query runs. The emails stay hidden.