Roles and security

Postgres uses roles for login users and groups. Least-privilege grants limit damage from compromised app credentials.

Create roles and grants

CREATE ROLE app_reader LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE practice TO app_reader;
GRANT USAGE ON SCHEMA public TO app_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader;

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

Practice: Run on a local Postgres instance you own. Avoid changing production cluster settings.

Row Level Security preview

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::int);

RLS filters rows per session—multi-tenant SaaS pattern.

Connection security

Use TLS in production, rotate passwords, prefer IAM/auth proxy on managed clouds. Never embed superuser creds in application repos.

Important interview questions and answers

1. Q: ROLE vs USER?
   A: In modern Postgres, USER is CREATE ROLE with LOGIN—roles are unified.
2. Q: RLS purpose?
   A: Enforces per-tenant or per-user row visibility inside the database.

Self-check

1. How do you grant read-only access to all public tables?
2. Why enable RLS on multi-tenant tables?

Tip: App roles should lack SUPERUSER and CREATE on public in production.