Users, Roles & Security

Postgres has no separate concept of a "user" — a role that has been granted LOGIN privilege is a user. Everything else (permissions, row-level security, transport encryption) builds on that one primitive.

what a connection has to clear, in order

SSL/TLSis the wire itself trusted?

Authenticationpg_hba.conf — which method, from where

Role PrivilegesGRANT — which tables/columns

Row-Level SecurityPOLICY — which rows

Roles

-- a role that can log in — this is what people mean by "user"
CREATE ROLE app_user WITH LOGIN PASSWORD 'change-me';

-- a role that can't log in — a group other roles can be granted
CREATE ROLE readonly;
GRANT readonly TO app_user;

-- the superuser this repo's compose file creates on first boot
-- (from .env: POSTGRES_USER=admin / POSTGRES_PASSWORD=admin123)

Roles can own objects, hold privileges directly, and be members of other roles (which is how Postgres does "groups" — a group is just a role other roles inherit from).

Privileges

Privileges are granted on specific objects — databases, schemas, tables, even individual columns — not globally by default.

GRANT CONNECT ON DATABASE learning TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
GRANT SELECT (id, name) ON customers TO readonly;  -- column-level

-- everything a role can currently do:
\du+ app_user   -- psql shortcut
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'app_user';

Common mistake

New tables don't automatically inherit privileges you granted on existing ones — GRANT SELECT ON orders TO app_user only covers orders. Use ALTER DEFAULT PRIVILEGES if you want privileges to apply to tables created later, or every new table becomes a silent access gap.

Row-Level Security

Table-level GRANTs control which tables a role can touch. Row-Level Security (RLS) goes further and controls which rows — the same query, run by two different roles, can see two different sets of rows from the same table.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

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

Once RLS is enabled, every query against orders — including ones the application didn't anticipate, like an ad-hoc SELECT * from a debugging session — is filtered by the policy. This is the standard way to build multi-tenant Postgres safely: application bugs that forget a WHERE tenant_id = ... clause fail closed instead of leaking another tenant's rows.

Under the hood

RLS policies don't apply to the table owner or superusers by default (FORCE ROW LEVEL SECURITY changes that). This is deliberate — admin and migration tooling usually needs unfiltered access — but it means testing RLS as a superuser will look like it isn't working at all.

Compare this to ClickHouse's row policies: the mechanism (a USING predicate scoped to a table and role) is nearly identical — RLS is one of the places the two databases' SQL genuinely converges, since both borrow the same standard SQL feature.

SSL/TLS

By default, a local connection like this module's docker exec ... psql never leaves the container, so encryption isn't in play. Any connection over a network should set sslmode on the client:

# client connection string
postgresql://app_user:***@db.example.com:5432/learning?sslmode=verify-full

sslmode has a range from disable up through verify-full (which also validates the server's certificate matches the hostname, closing off man-in-the-middle attacks). require alone encrypts the connection but doesn't verify who's on the other end of it — enough to stop passive eavesdropping, not enough to stop impersonation.

Authentication methods

Whether a connection is accepted at all — and which method it must authenticate with — isn't decided here; it's decided per-connection by matching rules in pg_hba.conf, covered next in Configuration & Extensions.