Authentication, Authorization, Roles, and Least Privilege

-- Reusable privilege bundles do not log in.CREATE ROLE app_reader NOLOGIN;CREATE ROLE app_writer NOLOGIN;CREATE ROLE reporting_reader NOLOGIN;-- Service identities authenticate but do not own application objects.CREATE ROLE checkout_service LOGIN PASSWORD :'managed_secret';CREATE ROLE reporting_service LOGIN PASSWORD :'managed_secret';GRANT app_reader, app_writer TO checkout_service;GRANT reporting_reader TO reporting_service;-- Remove broad implicit access before granting the intended surface.REVOKE ALL ON DATABASE academy FROM PUBLIC;GRANT CONNECT ON DATABASE academy TO checkout_service, reporting_service;REVOKE ALL ON SCHEMA public FROM PUBLIC;GRANT USAGE ON SCHEMA commerce TO app_reader, app_writer;GRANT USAGE ON SCHEMA reporting TO reporting_reader;GRANT SELECT ON commerce.customer, commerce.sales_order TO app_reader;GRANT INSERT, UPDATE ON commerce.sales_order TO app_writer;GRANT SELECT ON reporting.customer_order_summary TO reporting_reader;-- Sequences need separate privileges when generated identifiers use them.GRANT USAGE, SELECT ON SEQUENCE commerce.sales_order_order_id_seq TO app_writer;

CREATE ROLE commerce_owner NOLOGIN;CREATE ROLE migration_runner LOGIN;GRANT commerce_owner TO migration_runner;ALTER SCHEMA commerce OWNER TO commerce_owner;ALTER TABLE commerce.customer OWNER TO commerce_owner;ALTER TABLE commerce.sales_order OWNER TO commerce_owner;-- During a migration session:SET ROLE commerce_owner;ALTER TABLE commerce.sales_order    ADD COLUMN IF NOT EXISTS source_channel text;RESET ROLE;

ALTER TABLE commerce.sales_order ENABLE ROW LEVEL SECURITY;CREATE POLICY order_region_policyON commerce.sales_orderFOR SELECTTO regional_supportUSING (    region = current_setting('app.region', true));-- With RLS enabled and no applicable policy, PostgreSQL applies default deny.-- Owners and roles with BYPASSRLS require separate governance.

CREATE VIEW customer_support_view ASSELECT    customer_id,    substr(email, 1, 2) || '***@' || substr(email, instr(email, '@') + 1) AS masked_email,    region,    CASE WHEN deleted_at IS NULL THEN 'active' ELSE 'deleted' END AS lifecycle_stateFROM customer;SELECT *FROM customer_support_viewORDER BY customer_id;

SELECT    current_user,    session_user,    has_database_privilege(current_user, 'academy', 'CONNECT') AS can_connect,    has_schema_privilege(current_user, 'commerce', 'USAGE') AS can_use_schema,    has_table_privilege(current_user, 'commerce.sales_order', 'SELECT') AS can_read_orders,    has_table_privilege(current_user, 'commerce.sales_order', 'DELETE') AS can_delete_orders;SELECT grantee, table_schema, table_name, privilege_typeFROM information_schema.role_table_grantsWHERE table_schema IN ('commerce', 'reporting')ORDER BY grantee, table_schema, table_name, privilege_type;