How to Configure Postgres Least Privilege
You configure postgres least privilege by revoking default public grants and assigning scoped group roles to users. You then grant only the exact table or column read grants that each role requires to operate. Many engineering teams start by sharing a single admin account across apps and developers. Overly broad roles expose every table to accidental updates. Schema drops, or bulk data exports during routine work. How to configure postgres least privilege with native roles A clean grants mode
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 configure postgres least privilege by revoking default public grants and assigning scoped group roles to users. You then grant only the exact table or column read grants that each role requires to operate. Many engineering teams start by sharing a single admin account across apps and developers. Overly broad roles expose every table to accidental updates. Schema drops, or bulk data exports during routine work.
How to configure postgres least privilege with native roles
A clean grants model starts with the special role named PUBLIC this built-in target grants permissions to every role on the database system at once by default, every role receives grants to connect to databases and create temporary tables. Roles also receive default grants to execute functions and procedures across the database object owners can revoke these default grants to lock down baseline access. Adding new users.
Schema creation rules also require review during setup in PostgreSQL 15 and later, the default config blocks standard users from creating objects in the public schema. Older versions, or databases upgraded from earlier releases. Still allow everyone to create objects there. Admins fix this exposure by revoking creation rights on the public schema from the PUBLIC role.
Once the baseline is clean. Admins set up group roles to manage grants for large teams. Instead of granting table rights to single users. You grant grants to a group role single users then receive membership in the appropriate group role for their job function for read-only users, the select privilege allows reading from an entire table view, or materialized view. Admins can also grant the select privilege on exact columns of a table to hide sensitive fields. Keep in mind that the select privilege also allows users to export table data using copy tasks.
Why static role grants are hard to maintain
Native group roles and column grants work well. Database schemas stay small and static still, production systems change every week as teams ship new features and run migrations. When a developer creates a new table or adds a sensitive column existing group grants can fall behind. Admins must audit and update column-level grants every deployment to keep sensitive fields restricted.
Also, engineers often need temporary write access to fix a broken migration or debug a production incident. Granting and revoking role memberships by hand for every support ticket creates heavy admin overhead. Teams often leave elevated roles attached to user accounts long after an incident ends. This grants drift turns a strict role hierarchy back into a shared high-privilege system.
What native database permissions leave open
Database role systems decide who can log in and which objects they can touch at connection time. They do not inspect what single statements or queries do in flight during an active session. A user with read access to a customer table can still query millions of rows at once and because the select privilege permits copy exports. A read-only user can dump an entire table locally.
Least Privilege Principle + End-to-End Encryption: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Column-level grants also present an all-or-nothing choice for app support engineers if a support role needs to view a customer record. They see the raw values in every allowed column native grants cannot mask sensitive substrings like email addresses or government IDs inside query results. Securing live database sessions requires controls that inspect queries and transform sensitive results at the protocol level.
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 every role get by default in PostgreSQL?
Every role inherits grants granted to the special PUBLIC role by default, PUBLIC allows users to connect to databases. Create temporary tables, and execute functions and procedures. Object owners can revoke these defaults at any time.
Can you restrict read access to specific columns in a table?
Yes, admins can grant the select privilege on exact columns of a table view, or materialized view. Users with that grant can query only the specified columns and will receive an error if they request restricted columns.
Do upgraded databases allow all users to create objects in the public schema?
Databases created in PostgreSQL 15 and later block. General users from creating objects in the public schema by default. Still, databases upgraded from earlier versions retain the older permissive behavior until an admin revokes creation rights from PUBLIC.
Short role checks help teams work fast. Simple rules make each review clear. Good logs keep every team safe. Scoped keys stop bad changes early.
Teams test new role rules in staging first. Clear owner lists speed up each audit. Small role scopes cut down on risk. Regular log checks catch drift right away.
Related guides
In this post