Learn SQL with PostgreSQL - Roles, Security Management & Row Level Security (RLS)

#Introduction

Welcome to episode 16 of the Learn SQL with PostgreSQL series! So far we've focused on making the database fast and correct. But there's one question we haven't taken seriously: who is allowed to access what? An application that treats every database connection with the same privileges is an application waiting for disaster — one bug in the app, one leaked credential, and all the data can be accessed.

Database security is layered. In episode 12 we learned to hide columns with views. Today we add a much stronger layer: role management to control who can do what, and Row Level Security (RLS) to control which rows each person can see — the heart of multi-tenant application security.

In this episode, we'll cover the role concept that combines users and groups, the CREATE ROLE, CREATE USER, GRANT, and REVOKE commands, the privilege hierarchy on database, schema, table, and sequence, then Row Level Security with CREATE POLICY, and close with securing the pg_hba.conf configuration file.

#Access Control Management & Roles

#The Role Concept: Users and Groups as One

In most databases, users and groups are two distinct concepts. In PostgreSQL, both are merged into a single entity: the role. A role can act as a user (that logs in) and as a group (that can have members) at the same time.

CREATE ROLE app_user LOGIN PASSWORD 'rahasia123';

CREATE ROLE read_only_role;

CREATE USER is just an alias for CREATE ROLE ... LOGIN. This LOGIN difference is what determines whether a role can enter the database or only serves as a container for permissions.

#GRANT and REVOKE

Once a role is created, give permissions with GRANT and revoke them with REVOKE:

GRANT CONNECT ON DATABASE shop TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;
 
REVOKE DELETE ON orders FROM app_user;

Note the hierarchy: a role needs CONNECT on the database, USAGE on the schema, then privileges on the tables. Tables and views use the SELECT/INSERT/UPDATE/DELETE privileges, while sequences (for auto-increment) use USAGE.

#The Principle of Least Privilege

The golden rule of security: each role is granted only the privileges it truly needs. An application role doesn't need CREATE TABLE; an analyst role doesn't need DELETE; and SUPERUSER is never for application connections. Superuser credentials are only for DBAs.

CREATE ROLE app_role;
GRANT CONNECT ON DATABASE shop TO app_role;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_role;
 
CREATE ROLE service_worker LOGIN PASSWORD 'rahasia456';
GRANT app_role TO service_worker;

#Row Level Security (RLS)

Views and GRANT control access at the table/column level. But there's a finer need: restricting which rows can be seen/changed. That's the job of Row Level Security (RLS) — and it's the foundation of multi-tenant applications where one database serves many organizations.

#Enabling RLS

First, enable RLS on the table, then set a policy:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

ENABLE turns on RLS for normal connections. FORCE tightens it further: even the table owner is subject to the policy (without FORCE, the table owner can bypass RLS).

#Writing an RLS Policy

A policy uses a USING expression that determines which rows may be accessed. The classic multi-tenant example: each user only sees their own tenant's data.

SELECT set_config('app.current_tenant', 'tenant-a', FALSE);

CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.current_tenant'));

Now every SELECT * FROM orders only returns rows whose tenant_id equals the session's app.current_tenant value. Users from other tenants see not a single row — security is enforced at the database level, not in the application.

CREATE POLICY tenant_insert ON orders
FOR INSERT
WITH CHECK (tenant_id = current_setting('app.current_tenant'));
 
CREATE POLICY tenant_update ON orders
FOR UPDATE
USING (tenant_id = current_setting('app.current_tenant'))
WITH CHECK (tenant_id = current_setting('app.current_tenant'));

WITH CHECK validates the new row being written — a user can't insert or change data belonging to another tenant.

#How the Application Sets the Tenant

A common implementation pattern: the application opens one connection per request, runs SELECT set_config('app.current_tenant', <tenant_from_authentication>, FALSE), then all queries run with automatic isolation. Because set_config with the third argument FALSE only applies to the current session, there's no risk of leaking between sessions.

#Securing Configuration Files

Role and RLS security mean nothing if network authentication isn't guarded. The two main configuration files:

• pg_hba.conf (Host-Based Authentication): governs who can connect from where, with which authentication method.
• postgresql.conf: the server's main parameters, including networking and logging.

local   all             all                                     trust
host    all             all             127.0.0.1/32            scram-sha-256
host    all             app_user        10.0.0.0/8              scram-sha-256
host    all             all             0.0.0.0/0               reject

Rules are read top-to-bottom; the first match wins. The example configuration above: local connections (unix socket) use trust, loopback connections use the scram-sha-256 password, only user app_user is allowed from the 10.0.0.0/8 network, and all other connections are rejected. This trailing reject pattern is highly recommended — don't leave PostgreSQL open without authentication to the whole internet.

#Common Mistakes

#Closing

In this episode 16, we've hardened database security: the role concept that unifies users and groups, the CREATE ROLE, GRANT, and REVOKE commands, the privilege hierarchy from database to sequence, Row Level Security with multi-tenant policies, and securing pg_hba.conf and postgresql.conf.

Key takeaways:

• A role in PostgreSQL is both a user and a group — combine them with GRANT role TO user.
• The least privilege principle: a role only has the permissions it needs.
• RLS isolates rows per tenant at the database level — a last line of defense that doesn't depend on application code.
• USING determines which rows can be read; WITH CHECK determines which rows can be written.
• pg_hba.conf governs network authentication — never use trust for non-local connections.

In the next episode, episode 17, we get into search and intelligence: Full-Text Search (FTS) & Vector Extension (pgvector) — from turning text into searchable vectors with to_tsvector and to_tsquery, ranking results with ts_rank, to turning PostgreSQL into a vector database for AI embeddings with pgvector.