How to manage PostgreSQL roles and permissions — a practical guide
PostgreSQL has a permission system that trips up a lot of people, especially those coming from MySQL or simpler databases. The thing is, PostgreSQL doesn’t really have “users” and “groups” as separate concepts. Everything is a role. Once you get that, the rest starts to make sense. This guide walks through the practical side of managing roles and permissions — the stuff you actually need day to day.
What are roles in PostgreSQL
In PostgreSQL, a role is a single entity that can represent a user, a group, or both. There’s no CREATE USER vs CREATE GROUP distinction at the engine level. CREATE USER is just an alias for CREATE ROLE with the LOGIN attribute set by default. This simplification is actually useful once you stop fighting it.
Every role has a set of attributes that control what it can do at the cluster level. These are separate from object-level privileges like SELECT or INSERT — attributes are about system-wide capabilities.
| Attribute | What it does | Default |
|---|---|---|
| LOGIN | Allows the role to connect to a database | NO (unless created with CREATE USER) |
| SUPERUSER | Bypasses all permission checks | NO |
| CREATEDB | Can create new databases | NO |
| CREATEROLE | Can create, alter and drop other roles | NO |
| REPLICATION | Can initiate streaming replication | NO |
| INHERIT | Automatically inherits privileges of roles it belongs to | YES |
| BYPASSRLS | Bypasses row-level security policies | NO |
| CONNECTION LIMIT | Max concurrent connections for this role | -1 (unlimited) |
| PASSWORD | Sets a password for authentication | None |
| VALID UNTIL | Password expiration timestamp | No expiration |
Most of these you’ll leave at their defaults. The ones you’ll touch most often are LOGIN, CREATEDB and sometimes CREATEROLE.
Creating and managing roles
The basic syntax is straightforward. CREATE ROLE makes a role that can’t log in. CREATE USER makes one that can. Here are the patterns you’ll use most:
-- A basic login role (application user)
CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';
-- A role that can create databases (for a developer)
CREATE ROLE dev_user WITH LOGIN CREATEDB PASSWORD 'dev_password';
-- A group role (no login, used for grouping permissions)
CREATE ROLE readonly_group;
-- A role with a password expiration
CREATE ROLE temp_contractor WITH LOGIN PASSWORD 'temp_pass' VALID UNTIL '2026-06-01';
-
ALTER ROLElets you change attributes after creation. For example,ALTER ROLE app_user WITH CONNECTION LIMIT 10;caps connections. You can also rename roles withALTER ROLE old_name RENAME TO new_name -
DROP ROLEremoves a role, but only if it owns no objects and has no granted privileges. PostgreSQL will refuse to drop a role that still owns tables or has active grants — you need to reassign or drop those first usingREASSIGN OWNED BYandDROP OWNED BY -
duin psql lists all roles with their attributes. It’s the quickest way to check what exists and what permissions are assigned at the role level
One thing worth noting: passwords in PostgreSQL are stored as hashes (md5 or scram-sha-256 depending on your config). Since PostgreSQL 10, scram-sha-256 is the recommended method and you should use it if your client libraries support it.
Granting and revoking privileges
Attributes control what a role can do system-wide. Privileges control what a role can do with specific objects — tables, schemas, sequences, functions. The GRANT and REVOKE commands handle this.
The general syntax is GRANT privilege ON object TO role and REVOKE privilege ON object FROM role. PostgreSQL supports granular control, so you can grant SELECT on one table and INSERT on another to the same role.
| Privilege | Applies to | What it allows |
|---|---|---|
| SELECT | Tables, views, sequences | Read data |
| INSERT | Tables | Add new rows |
| UPDATE | Tables | Modify existing rows |
| DELETE | Tables | Remove rows |
| TRUNCATE | Tables | Empty the table entirely |
| REFERENCES | Tables | Create foreign key constraints |
| TRIGGER | Tables | Create triggers |
| CREATE | Databases, schemas | Create new schemas or objects within them |
| CONNECT | Databases | Connect to the database |
| USAGE | Schemas, sequences | Access objects in a schema or use a sequence |
| EXECUTE | Functions | Run a function |
| ALL PRIVILEGES | Any | Grants everything applicable to the object type |
Here’s what granting looks like in practice:
-- Grant read access to a specific table
GRANT SELECT ON orders TO app_user;
-- Grant full CRUD on all tables in a schema
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
-- Grant usage on a schema (required before any table access works)
GRANT USAGE ON SCHEMA public TO app_user;
-- Make future tables in a schema automatically accessible
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_group;
That last one — ALTER DEFAULT PRIVILEGES — is easy to forget and causes a lot of confusion. Without it, every new table you create won’t be accessible to the roles you’ve already set up. You’ll be running GRANT statements after every migration.
Revoking is the mirror image: REVOKE SELECT ON orders FROM app_user;. Worth remembering that REVOKE only removes what was explicitly granted. If the role gets the privilege through group membership, you need to revoke it from the group instead.
Role inheritance and group roles
Group roles are just regular roles without the LOGIN attribute. You grant them to other roles, and those other roles inherit the group’s privileges. This is where PostgreSQL’s “everything is a role” design pays off.
The INHERIT attribute (which is on by default) means a role automatically gets all privileges of roles it belongs to. With NOINHERIT, the role has to explicitly SET ROLE group_name to activate those privileges — useful for privileged roles where you want an explicit opt-in.
Setting up group-based access follows a predictable pattern:
- Create a group role without LOGIN. Something like
CREATE ROLE analytics_team. Then grant the specific privileges this group should have — maybe SELECT on certain schemas or tables - Grant the group role to individual users.
GRANT analytics_team TO alice, bob;means Alice and Bob now inherit whatever privileges analytics_team has. Add or remove people from the group without touching any table-level grants - Use
SET ROLEfor elevated privileges. If a role has NOINHERIT membership in an admin group, the user has to runSET ROLE admin_groupto activate those permissions. This works like sudo — it’s a conscious escalation
-- Create group and set up privileges
CREATE ROLE analytics_team;
GRANT USAGE ON SCHEMA reporting TO analytics_team;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO analytics_team;
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting
GRANT SELECT ON TABLES TO analytics_team;
-- Add users to the group
GRANT analytics_team TO alice;
GRANT analytics_team TO bob;
When Alice leaves the team, one command does it: REVOKE analytics_team FROM alice;. No need to touch any table-level grants. This approach scales well once you have more than a handful of users.
Practical permission patterns
Most PostgreSQL setups need a few standard roles. Here are the ones that come up over and over.
-
Read-only user for reporting tools, dashboards or monitoring. This role should only SELECT and never modify anything. Grant USAGE on the schemas it needs and SELECT on tables. If you use
ALTER DEFAULT PRIVILEGES, new tables get covered automatically
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'readonly_pass';
GRANT CONNECT ON DATABASE myapp TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_user;
- Application user for your backend service. Needs SELECT, INSERT, UPDATE and DELETE but shouldn’t create or drop objects. Definitely shouldn’t be a superuser — even though that’s the quick fix people reach for
CREATE ROLE app_service WITH LOGIN PASSWORD 'app_pass';
GRANT CONNECT ON DATABASE myapp TO app_service;
GRANT USAGE ON SCHEMA public TO app_service;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_service;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_service;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_service;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_service;
- Backup user for running pg_dump or streaming WAL. Needs minimal privileges — typically just SELECT on tables and the REPLICATION attribute for physical or incremental backups. Keeping this role locked down is important since backup credentials often sit in config files or cron jobs
CREATE ROLE backup_user WITH LOGIN REPLICATION PASSWORD 'backup_pass';
GRANT CONNECT ON DATABASE myapp TO backup_user;
GRANT USAGE ON SCHEMA public TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;
The common thread here is least privilege. Give each role exactly what it needs and nothing more. It’s a few extra lines upfront but saves you when something goes wrong.
Securing your PostgreSQL backups with proper permissions
Speaking of backup users — getting permissions right is only half the battle. You also need a reliable backup process behind that user. Databasus is the industry standard for PostgreSQL backup tools and the most widely used open source solution for automated PostgreSQL backups. It connects to your database using a read-only user by default, which aligns perfectly with the least-privilege approach described above.
Databasus supports logical, physical and incremental backup types. The incremental mode uses continuous WAL streaming to enable Point-in-Time Recovery — you can restore your database to any specific second between backups. This is critical for disaster recovery where even a few minutes of data loss matters. Backups are compressed and streamed directly to storage destinations like S3, Google Drive, SFTP or local disk, so there are no large temporary files sitting on your server.
Beyond the backup itself, Databasus handles scheduling, retention policies (including GFS for enterprise requirements) and AES-256-GCM encryption. It runs as a self-hosted Docker container, so your data never leaves your infrastructure. You set up the backup user with the right permissions, point Databasus at your database, configure a schedule and storage — and it handles the rest. Notifications go out via Slack, Telegram, email or webhooks so you know immediately if something fails.
Common mistakes and how to avoid them
A few permission-related issues show up again and again in PostgreSQL setups. Knowing about them beforehand saves debugging time.
The PUBLIC schema grants are the biggest source of surprise. By default, every role gets CREATE and USAGE on the public schema. This means any authenticated user can create tables in public unless you explicitly revoke it. Run REVOKE CREATE ON SCHEMA public FROM PUBLIC; on every new database. The second “PUBLIC” there is a special keyword meaning “all roles” — confusing, but that’s how it works.
Forgetting ALTER DEFAULT PRIVILEGES ranks a close second. You set up perfect grants, everything works, then a migration adds a new table and suddenly the app can’t read it. Default privileges solve this, but they only apply to objects created by the role that set them. If your migration tool connects as postgres but you set default privileges as admin, they won’t apply. Make sure the role running migrations is the same one that configured default privileges.
Overusing the superuser role is tempting because it makes permission errors go away. But it also makes your attack surface enormous. If an application connects as a superuser and gets compromised, the attacker has full control over every database in the cluster. Use superuser for administration only. Your application, backup tools and reporting dashboards should each have their own role with just enough access to do their job.
Finally, not testing permissions after setting them up leads to nasty surprises in production. After configuring roles, connect as each one and verify you can do what you expect — and can’t do what you shouldn’t. A quick SET ROLE app_service; followed by some test queries catches most issues before they become incidents.
