Roles and Permissions

Introduction

PostgreSQL uses roles for both login accounts and permission groups—there is no hard split between "user" and "role" like MySQL's 'user'@'host'. This chapter creates roles, grants least privilege on databases, schemas, tables, and columns, introduces row-level security (RLS) for multi-tenant patterns, and touches pg_hba.conf authentication. Compare with MySQL users and permissions.

Prerequisites

• Installation and psql
• Superuser access (e.g. postgres in Docker) for GRANT exercises

Roles and Login

Create a role that can log in:

CREATE ROLE app_reader WITH LOGIN PASSWORD 'StrongPass123!';

CREATE USER is an alias for CREATE ROLE ... LOGIN.

Create a role without login (group role):

CREATE ROLE role_blog_read;

List roles:

-- psql
\du
 
SELECT rolname, rolcanlogin, rolsuper FROM pg_roles ORDER BY rolname;

Code explanation:

• LOGIN allows password or cert authentication
• Group roles hold privileges; login roles inherit from groups they are granted to

GRANT on Database and Schema

Connect to database:

GRANT CONNECT ON DATABASE hello_demo TO app_reader;

Use schema objects:

GRANT USAGE ON SCHEMA public TO app_reader;

Read all tables in public:

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

Code explanation:

• USAGE on schema is required before accessing objects inside it
• ALTER DEFAULT PRIVILEGES applies to future tables created by the current role—run as the table owner or app migration role

Read/write app role:

CREATE ROLE app_writer WITH LOGIN PASSWORD 'WriterPass456!';
 
GRANT CONNECT ON DATABASE hello_demo TO app_writer;
GRANT USAGE ON SCHEMA public TO app_writer;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_writer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_writer;

Sequences are needed for IDENTITY / SERIAL inserts.

Revoke:

REVOKE INSERT ON ALL TABLES IN SCHEMA public FROM app_writer;

Drop role:

DROP ROLE IF EXISTS app_reader;

Table and Column Level

Single table:

GRANT SELECT ON posts TO app_reader;

Hide email—grant only safe columns:

GRANT SELECT (id, display_name) ON users TO app_reader;

No DDL for app users—omit CREATE, DROP, ALTER on production app accounts.

Show grants:

-- psql
\dp posts
 
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'posts';

Role Membership (Groups)

CREATE ROLE role_blog_read;
CREATE ROLE role_blog_write;
 
GRANT SELECT ON ALL TABLES IN SCHEMA public TO role_blog_read;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO role_blog_write;
 
GRANT role_blog_read TO app_reader;
ALTER ROLE app_reader INHERIT;

INHERIT (default)—session privileges include member roles. SET ROLE switches active role:

SET ROLE role_blog_read;
RESET ROLE;

Row-Level Security (Optional)

Multi-tenant SaaS: each tenant sees only their rows.

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY posts_tenant_isolation ON posts
  FOR ALL
  TO app_writer
  USING (user_id = current_setting('app.current_user_id')::bigint);

App sets before queries:

SET app.current_user_id = '42';
SELECT * FROM posts;

Code explanation:

• RLS filters rows even if SELECT is granted on the table
• BYPASSRLS attribute on superuser/owner bypasses policies—never grant to apps

Deep multi-tenant design is advanced—know RLS exists when reading Supabase/Neon docs.

Connection Security and pg_hba.conf

Authentication rules live in pg_hba.conf—see Installation and psql.

Example line (concept):

# TYPE  DATABASE  USER        ADDRESS         METHOD
hostssl hello_demo app_writer  10.0.0.0/8     scram-sha-256

Reload after edits:

docker exec postgres pg_ctl reload -D /var/lib/postgresql/data

Password and Connection URI

URL-encode special characters in passwords:

postgresql://app_writer:WriterPass456%21@127.0.0.1:5432/hello_demo

Store secrets in env vars—not Git—Git gitignore.

PostgreSQL vs MySQL Permissions

FAQ

Role vs user?

Same thing in PostgreSQL—CREATE USER = CREATE ROLE LOGIN.

GRANT not working?

Check CONNECT, USAGE, and object owner. Table owner has all rights; other roles need explicit GRANT.

Why sequence USAGE?

INSERT into SERIAL/IDENTITY calls nextval()—requires USAGE on the sequence.

PUBLIC role?

Default grants to PUBLIC were tightened over versions—do not rely on public create/connect in production.

How does app connect?

JDBC, psycopg, ORMs use a dedicated role—psycopg basics.

Column-level GRANT enough for PII?

Combine with views (chapter 13) and RLS for defense in depth.