Set Up Roles and Permissions

Setting up roles and permissions in PostgreSQL can feel like walking through a dark room — you know there are doors (roles) and locks (privileges), but you can't see where they are. Get it wrong and you'll either lock everyone out of your database or, worse, leave the vault wide open. In this lesson, you'll move from confusion to clarity: you'll learn how to create roles, grant the right permissions, and keep your data both accessible and secure. By the end, you'll confidently set up roles and permissions in your own projects, exactly as a production database admin would.

The problem this lesson solves

You've built a database with tables, views, and maybe a function or two. Now the real world hits: your application needs a connection string, your teammate needs read access for reporting, and your automated CI pipeline needs to create tables in a test schema. If every connection uses postgres — the superuser — you've created a single point of failure and a horrible security habit.

The pain is concrete. Without proper roles and permissions:

• Every developer can drop tables — one wrong DROP TABLE wipes out production data.
• Unauthorized users can read sensitive data — think customer emails, payment records, or internal metrics.
• You can't revoke access granularly — when a contractor leaves, you must change the superuser password and every app that uses it.
• Auditing is impossible — you can't tell who did what because everyone is 'postgres'.

This lesson gives you the toolset to solve exactly that. You'll learn how to create roles (users and groups), grant privileges (on tables, schemas, databases), and manage access with the principle of least privilege — giving each entity only the permissions it absolutely needs.

Core concept / mental model

Think of PostgreSQL as a large office building.

• Roles are the employees and badges. A role can be a single person (a login user) or a group (a department) that shares permissions.
• Privileges are the doors each badge can open. Some doors are open to all (public), some require a key card (grant), and some are locked even for the owner (revoke).
• The postgres superuser is the building manager with a master key — but you wouldn't give that key to everyone.

In PostgreSQL, a role is an entity that can own database objects and hold permissions. Roles can log in (LOGIN attribute), have a password, and be grouped into other roles. When you create a table, you own it — and as owner, you have all privileges on it. To let others use your table, you grant them specific privileges like SELECT, INSERT, UPDATE, or DELETE.

Here's the mental model in action:

1. Create a role for each person or application (e.g., app_user, report_reader).
2. Create a group role when you have a common set of permissions (e.g., read_only group).
3. Grant privileges on schemas and objects to those roles.
4. Assign members to group roles so they inherit permissions.

This layering lets you grant once, reuse everywhere, and revoke in one place.

The principle of least privilege is your guiding star: every role gets only the permissions required for its function, nothing more.

How it works step by step

Setting up roles and permissions follows a logical sequence. Here's how it works from the ground up.

Step 1: Connect as superuser

Start with your postgres user or another superuser role. You need this to create roles and grant privileges that affect other users.

psql -U postgres -h localhost -d your_database

Step 2: Create login roles

A login role is what an application or person uses to connect. Decide on a username and password.

CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password_123';
CREATE ROLE analyst WITH LOGIN PASSWORD 'another_password';

Step 3: Create group roles

Group roles don't log in; they bundle permissions so you can manage access as a unit.

CREATE ROLE read_only_group NOLOGIN;
CREATE ROLE read_write_group NOLOGIN;

Step 4: Grant privileges on schemas

Before anyone can touch tables, they need access to the schema. In PostgreSQL, public schema is accessible by default, but in production you might restrict it.

GRANT USAGE ON SCHEMA public TO read_only_group;
GRANT USAGE ON SCHEMA public TO read_write_group;

Step 5: Grant privileges on tables (and future tables)

For existing tables, grant specific privileges. For future tables, use ALTER DEFAULT PRIVILEGES so new tables automatically get the right permissions.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_group;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write_group;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only_group;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO read_write_group;

Step 6: Assign members

Add your login roles to the group roles so they inherit the permissions.

GRANT read_only_group TO analyst;
GRANT read_write_group TO app_user;

Now app_user can read and write tables, while analyst can only read.

Pro tip: Always set a strong password for login roles—never use the same password as your postgres superuser.

Hands-on walkthrough

Let's put this into practice with a concrete example. We'll create a simple inventory database, add roles, and verify permissions.

Setup

First, connect as postgres and create a test database.

psql -U postgres

CREATE DATABASE inventory;
\c inventory

Now create a table that we'll protect.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2)
);

INSERT INTO products (name, price) VALUES ('Laptop', 999.99), ('Mouse', 19.99);

Create roles and permissions

-- Create login roles
CREATE ROLE app_user WITH LOGIN PASSWORD 'app_pass_123';
CREATE ROLE analyst WITH LOGIN PASSWORD 'analyst_pass_456';

-- Create group roles
CREATE ROLE read_only NOLOGIN;
CREATE ROLE read_write NOLOGIN;

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO read_only, read_write;

-- Grant privileges on existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;

-- Ensure future tables get the same
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO read_write;

-- Assign members
GRANT read_only TO analyst;
GRANT read_write TO app_user;

Verify permissions

Now connect as analyst and try to insert a row. It should fail because analyst only has read access.

psql -U analyst -d inventory -h localhost

-- This should work
SELECT * FROM products;

-- This should fail with permission denied
INSERT INTO products (name, price) VALUES ('Tablet', 299.99);

Expected output:

 id |  name  |  price  
----+--------+---------
  1 | Laptop |  999.99
  2 | Mouse  |   19.99
(2 rows)

ERROR:  permission denied for table products

As app_user, the insert should succeed.

psql -U app_user -d inventory -h localhost

INSERT INTO products (name, price) VALUES ('Tablet', 299.99);
-- Output: INSERT 0 1

Pro tip: Use \du inside psql to list all roles and their attributes. Use \dp products to see the privilege breakdown for a table.

Compare options / when to choose what

Not every setup needs the same granularity. Here's a quick comparison of common role strategies.

For most projects, group roles are the sweet spot. They give you the flexibility to grant permissions once and manage many users efficiently. If you need isolation at the data level (e.g., customers only see their own rows), then combine group roles with row-level security.

When to choose what:

• If you have more than 2–3 users or apps, use group roles.
• If you need different permissions per feature area, create separate schemas and grant accordingly.
• If you have a strict compliance requirement (e.g., EU data isolation), use row-level security.

Pro tip: Always start with the principle of least privilege. You can always grant more later; revoking is messy.

Troubleshooting & edge cases

Even with careful setup, things can break. Here are the most common issues and how to fix them.

1. "Permission denied for schema public"

If you see this error, the role lacks USAGE on the schema.

GRANT USAGE ON SCHEMA public TO your_role;

Remember: USAGE on a schema doesn't grant table access; it only allows the role to reference objects in it.

2. "Permission denied for table" but you granted it

This often happens because the table was created after you ran GRANT. Use ALTER DEFAULT PRIVILEGES to cover future tables, or re-run the grant after every migration.

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only;

3. Forgot to grant on sequences

If your app uses SERIAL or IDENTITY columns, inserting may fail with "permission denied for sequence". Every sequence needs USAGE, SELECT for insert-capable roles.

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO read_write;

4. Role can't connect

Login roles need the LOGIN attribute and a password. If you created a role without it, you can add it later:

ALTER ROLE app_user WITH LOGIN PASSWORD 'new_password';

5. Inherited permissions not working

Group roles must have INHERIT (default), and membership must be granted. Check with \du and ensure you used GRANT group_role TO member_role.

6. Revoking access but users can still connect

Revoking CONNECT on the database might be needed if you want to block logins entirely.

REVOKE CONNECT ON DATABASE inventory FROM app_user;

Pro tip: If your app connects but fails on a query, test with psql using the same role and password to isolate the problem.

What you learned & what's next

You now understand how to set up roles and permissions in PostgreSQL. You've learned:

• Roles can be login users or group containers for permissions.
• Privileges control who can read, write, or manage objects.
• Least privilege is the key to secure, maintainable access.
• Default privileges handle future tables automatically.

With this foundation, you're ready to move to the next lesson in the track: Advanced Role Management and Auditing. You'll dive deeper into row-level security, ownership chains, and how to track who did what in your database.

Set up roles and permissions in your own project today — your future self (and your security auditor) will thank you.