Pg Role
You set up a pg role in Postgres to control who logs in and reads data. One role type acts as a login user, while another groups table rights for teams. Most teams start by giving each app or engineer a direct database user. Over time, teams grant broad write rights to fix broken deploys or speed up support work. Broad permissions let a single bad query alter or drop production tables. How a pg role works in Postgres Postgres uses a single role concept to manage both users and groups across a
Role-Based Access Control (RBAC): the complete guide
Architecture patterns, implementation strategies, and security best practices. Delivered to your inbox.
You set up a pg role in Postgres to control who logs in and reads data. One role type acts as a login user, while another groups table rights for teams. Most teams start by giving each app or engineer a direct database user. Over time, teams grant broad write rights to fix broken deploys or speed up support work. Broad permissions let a single bad query alter or drop production tables.
How a pg role works in Postgres
Postgres uses a single role concept to manage both users and groups across a cluster. Every pg role lives at the cluster level, not inside just one database. A role with login rights works just like a traditional user account. A role without login rights acts as a group that holds shared permissions.
Admins build clean access by separating login accounts from permission groups. First, you create group roles for tasks like reading analytics or updating billing records. Next, you grant specific schema and table rights to those group roles. Finally, you assign individual login roles to the right groups.
Role inheritance controls how a user uses those group permissions during a session. When inheritance is on, a logged-in user gets all group rights right away. When inheritance is off, the user must switch to the group role during the session. This setup lets admins grant higher rights only when a user needs them.
Why teams struggle with role sprawl
As a company grows, the number of database users and services multiplies fast. Teams add new microservices, read replicas, and reporting tools every month. Without a strict plan, admins grant table rights straight to individual login accounts. Soon, no one knows which user can read or change sensitive customer tables.
Cleaning up old permissions is hard because revoking rights can break live apps. In Postgres, objects also belong to the role that created them. You cannot drop a departed engineer's role until you move their tables to a new owner. Shared group ownership prevents this problem before it starts.
Role-Based Access Control (RBAC): architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
What native roles solve and what they miss
Native database roles do a great job of locking down baseline access at login. They stop unauthorized users from opening a session or reading restricted schemas. They also let you separate read-only reporting apps from core transactional services.
Still, a pg role only checks permissions at the table, column, or row level. Once a user logs in with read rights, the database sends back every matching row. Native roles do not rewrite sensitive fields like emails, phone numbers, or card numbers in results. They also cannot require a teammate to approve a dangerous update statement before it runs. And if ten engineers share one support role, native logs cannot show which person ran a query.
Govern statements and responses across databases and APIs
Good config and scoped roles are needed setup. They do not check single queries in flight or mask sensitive fields in returned rows. Across Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, and internal HTTP endpoints, hoop.dev runs beside each supported service as an open-source proxy, a sidecar for your database or API.
First, hoop.dev guardrails block banned queries before they run. Then hoop.dev data masking rewrites matched sensitive values in responses in memory. The audit trail records every statement and verdict per resource. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.
Frequently asked questions
What is the difference between a Postgres user and a pg role?
In modern Postgres, a user and a role are the exact same object. The only difference is that a user starts with login permission turned on by default.
Should applications and engineers share the same database roles?
No, apps and humans have very different access needs and risk profiles. Give apps narrow service roles, and give engineers separate accounts for auditing and control.
How do you remove a pg role that owns database tables?
Postgres blocks you from dropping any role that still owns schemas, tables, or views. You must reassign those owned objects to a group role before deleting the old account.