Roles and the Superuser

Create roles, grant permissions, and apply least privilege

Think of access control like a building with locked doors. Some people carry a master key that opens everything — including rooms that should never be touched accidentally. Most people carry a keycard that only opens the doors they actually need.\n\nPostgreSQL's privilege model works the same way. At the top: the postgres superuser, which can bypass every access control in the system. In the middle: roles with specific privileges. At the bottom: tightly scoped application users that can only do exactly what they need.\n\nUsing postgres for daily work is like handing everyone a master key. It works — until one wrong move destroys everything. This lab fixes that habit before it forms.

Roles vs Users: Same Thing, Different Attributes

PostgreSQL has one concept: role. A "user" is just a role with the LOGIN attribute. A "group" is a role without LOGIN — it can hold privileges and be granted to other roles, but cannot itself connect.

CREATE ROLE dev_user WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;
-- This is a "user" — it has LOGIN, so it can connect.

CREATE ROLE readonly_group;
-- This is a "group" — no LOGIN, used to bundle privileges.
GRANT readonly_group TO dev_user;
-- Now dev_user inherits all of readonly_group's privileges.

The Attributes That Matter

  SUPERUSER      → bypasses all permission checks (dangerous)
  LOGIN          → can initiate a connection (required for a "user")
  CREATEDB       → can create new databases
  CREATEROLE     → can create and modify other roles
  INHERIT        → automatically uses privileges of roles it belongs to (default on)
  REPLICATION    → can use pg_basebackup and streaming replication

The Four-Step Least-Privilege Grant Pattern

Creating a role is only the first step. Access must be explicitly granted at each level:

-- 1. Create the role
CREATE ROLE dev_user WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;

-- 2. Allow it to connect to the database
GRANT CONNECT ON DATABASE beer_db TO dev_user;

-- 3. Allow it to see objects in the schema
GRANT USAGE ON SCHEMA public TO dev_user;

-- 4. Allow it to read (or write) specific tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dev_user;

Miss any one of these steps and the role gets an "access denied" error — even if step 1 worked.

Why postgres Superuser Is Dangerous for Daily Work

The postgres superuser bypasses every access control. A single fat-finger DROP TABLE runs instantly. There is no confirmation, no second check.

A least-privilege dev_user role with SELECT-only access literally cannot run DROP TABLE, even if they try. The database refuses before any damage is done.

Role

The universal PostgreSQL concept for both users and groups. A role with LOGIN can connect (it's a "user"). A role without LOGIN can hold privileges and be granted to other roles (it's a "group").

GRANT

Gives a specific privilege to a role. Privileges are granular: CONNECT on a database, USAGE on a schema, SELECT/INSERT/UPDATE/DELETE on a table. Without an explicit GRANT, access is denied.

📋 List All Roles with \du

Connect explicitly as postgres for this administrative exercise; the default student role cannot create roles. Run \du to see every role on the server and their attributes. Find postgres and notice the "Superuser" flag in its attributes.

psql -h localhost -U postgres -d postgres
\du

List of roles Role name | Attributes ------------+------------------------------------------------------------ beer_db | postgres | Superuser, Create role, Create DB, Replication, Bypass RLS social_db | student | tv_db | weather_db |

🧑‍💻 Create a Non-Superuser Role

Create a role called dev_user with LOGIN but no superuser, no createdb, and no createrole. This is the principle of least privilege: start with nothing, add only what's needed.

CREATE ROLE dev_user WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;

CREATE ROLE

✅ Verify the Role in \du

Run \du again and find dev_user. Confirm its attributes column is empty — no special privileges, exactly as intended.

\du

List of roles Role name | Attributes ------------+------------------------------------------------------------ beer_db | dev_user | postgres | Superuser, Create role, Create DB, Replication, Bypass RLS social_db | student | tv_db | weather_db |

🚫 Revoke Default CONNECT

Remove the default CONNECT privilege from the database so dev_user cannot connect yet. PostgreSQL may allow all roles to connect through the built-in PUBLIC role, so this step makes the next failure deterministic.

REVOKE CONNECT ON DATABASE beer_db FROM PUBLIC;

REVOKE

🔒 Try Connecting to beer_db

Attempt to connect as dev_user before any explicit CONNECT grant has been given. This should now fail.

\c beer_db dev_user

connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: permission denied for database "beer_db" DETAIL: User does not have CONNECT privilege. Previous connection kept

🔑 Grant CONNECT on beer_db

Grant dev_user the right to connect to beer_db.

GRANT CONNECT ON DATABASE beer_db TO dev_user;

GRANT

✅ Connect to beer_db After Grant

Reconnect as dev_user after the CONNECT privilege has been granted. This time the connection should succeed.

\c beer_db dev_user

You are now connected to database "beer_db" as user "dev_user". beer_db=>

👀 See Table Names, But Not Table Data

Now that dev_user can connect to beer_db, check what that access actually includes. You can list table names with \dt because PostgreSQL exposes table metadata separately from table data, but reading rows still requires an explicit SELECT grant on the table itself.

\dt
SELECT * FROM beers;

List of tables Schema | Name | Type | Owner --------+----------------------+-------+--------- public | beer_ratings | table | beer_db public | beers | table | beer_db public | breweries | table | beer_db public | festival_appearances | table | beer_db public | festivals | table | beer_db public | ingredients | table | beer_db public | styles | table | beer_db public | tap_assignments | table | beer_db (8 rows) ERROR: permission denied for table beers

🔍 Verify: dev_user Cannot Create Databases

Connect as dev_user and attempt to create a database. Confirm the error message — the privilege model is working.

\q
psql -h localhost -U dev_user -d beer_db
CREATE DATABASE test_db;

ERROR: permission denied to create database

Lab complete! dev_user exists with least-privilege access to beer_db:\n\n\n ROLE : dev_user\n LOGIN : ✅ (can connect)\n SUPERUSER: ❌ (cannot bypass checks)\n CREATEDB : ❌ (cannot create databases)\n CONNECT : ✅ on beer_db\n

Enable JavaScript to run the live terminal and track your progress.