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 ROLE lets you change attributes after creation. For example, ALTER ROLE app_user WITH CONNECTION LIMIT 10; caps connections. You can also rename roles with ALTER ROLE old_name RENAME TO new_name
• DROP ROLE removes 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 using REASSIGN OWNED BY and DROP OWNED BY
• \du in 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 ROLE for elevated privileges. If a role has NOINHERIT membership in an admin group, the user has to run SET ROLE admin_group to 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.