How to Configure Postgres Create User

Published
Length
3 min read

Luis Felipe Dias

All posts

To run a postgres create user workflow, an admin defines a new database role with login rights. The admin then assigns exact schema and table grants to that role engineering teams often create shared accounts or grant broad grants to speed up onboarding. When grants are too broad any user or service can read or change sensitive production data. How the postgres create user workflow works In PostgreSQL, users and groups are both managed through the concept of database roles. When you create a

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 run a postgres create user workflow, an admin defines a new database role with login rights. The admin then assigns exact schema and table grants to that role engineering teams often create shared accounts or grant broad grants to speed up onboarding. When grants are too broad any user or service can read or change sensitive production data.

How the postgres create user workflow works

In PostgreSQL, users and groups are both managed through the concept of database roles. When you create a user you create a role that has grants to log in instead of assigning grants to one account at a time. Admins set up group roles. Single login accounts then receive membership in those group roles to inherit their grants.

Every new role also inherits rights from a special system role named PUBLIC this special role grants rights to every role on the system by default, every role receives the right to connect to databases and create temporary tables. Roles also receive default rights to execute functions and procedures. The object owner revokes them.

Schema grants also depend on your database version in version 15 and later, default settings block users from creating objects in the public schema in older versions or upgraded databases. Admins must revoke that creation right from the PUBLIC role for read access, admins grant the select rights on whole tables. Views, or exact columns.

How group roles scale database administration

Managing grants for each developer account by hand. Breaks down as an team grows. When twenty engineers join a team. Updating table grants for each person takes too much time. Group roles solve this problem by separating identity from grants sets an admin creates a group role for analysts or backend. Services and grants table rights once.

When a new engineer joins. The admin runs the user creation step and assigns the group if that engineer changes teams. Removing them from the group revokes their table rights right away. Admins can also restrict read rights to exact columns on sensitive tables. Still, maintaining column grants across hundreds of tables requires. Constant manual updates whenever schemas change.

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 and rights control who can log in and which objects they can touch. They provide a solid foundation for separating read-only accounts from admin accounts by revoking default public rights and using group roles, teams prevent blocked schema changes.

Still, native database grants stop working once a user authenticates and starts a session. A role decides if an account can read a table not what single statements do in flight for example, the select rights also allows a user to export table data in bulk if a developer needs read access to debug an issue. Native roles expose raw sensitive fields.

Static database keys also sit in config files and local machines for months if an attacker steals a password. The database cannot tell the attacker apart from the real engineer. Also, database Roles cannot require peer approval before someone runs a destructive update statement.

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

What permissions does a new PostgreSQL user get by default?

Every new user inherits grants from the special PUBLIC role by default, this includes rights to connect to databases. Create temporary tables, and execute functions or procedures. The object owner can revoke these default grants at any time.

Can a new user create tables in the public schema?

In PostgreSQL 15 and later. New users cannot create objects in the public schema by default in earlier versions, or databases upgraded from older versions. Everyone has this right by default. Admins must revoke creation rights on the public schema from the PUBLIC role to block it.

How can I limit a user to reading specific columns?

You can grant the select rights on exact columns of a table view, or materialized view. This lets the user query only the allowed columns. Hiding the rest of the table keep in mind that the select rights also allows the user to export those allowed columns.

The query runs. The emails stay hidden.