-- PostgreSQL AI Access Security Kit
-- Updated: 2026-07-19
-- Source: https://datamcp.app/resources/postgres-ai-access-security-kit
--
-- Review every identifier and statement before using this file.
-- Run role administration as an authorized database administrator.
-- Do not store a production password in this file.

\set ai_role 'ai_reader'
\set database_name 'app_production'
\set owner_role 'app_owner'
\set approved_schema 'reporting'

-- 1. Create a dedicated non-owner login.
CREATE ROLE :"ai_role"
  WITH LOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  CONNECTION LIMIT 5;

-- Set the password separately through your secret-management workflow or psql:
-- \password ai_reader

-- 2. Permit only the approved database and schema boundary.
GRANT CONNECT ON DATABASE :"database_name" TO :"ai_role";
GRANT USAGE ON SCHEMA :"approved_schema" TO :"ai_role";

-- 3. Grant an explicit list of reviewed relations.
-- Replace these example names before running.
GRANT SELECT ON TABLE
  reporting.customer_summary,
  reporting.ticket_summary,
  reporting.subscription_status
TO :"ai_role";

-- 4. Optional: grant future tables created by the actual owner role.
-- This affects only future objects created by owner_role in approved_schema.
-- Remove this block when every new relation should require explicit review.
ALTER DEFAULT PRIVILEGES
  FOR ROLE :"owner_role"
  IN SCHEMA :"approved_schema"
  GRANT SELECT ON TABLES TO :"ai_role";

-- 5. Operational limits. These reduce impact but do not replace privileges.
ALTER ROLE :"ai_role" IN DATABASE :"database_name"
  SET statement_timeout = '15s';

ALTER ROLE :"ai_role" IN DATABASE :"database_name"
  SET idle_in_transaction_session_timeout = '30s';

ALTER ROLE :"ai_role" IN DATABASE :"database_name"
  SET default_transaction_read_only = 'on';

-- 6. Optional RLS example for one fixed tenant.
-- Review the identity model before adapting this example.
-- The AI role must not own the table and must not have BYPASSRLS.
--
-- ALTER TABLE app.tickets ENABLE ROW LEVEL SECURITY;
-- CREATE POLICY ai_reader_tenant_42
--   ON app.tickets
--   FOR SELECT
--   TO ai_reader
--   USING (organization_id = 42);

-- 7. Inspect explicit grants and role attributes.
SELECT
  r.rolname,
  r.rolsuper,
  r.rolinherit,
  r.rolcreaterole,
  r.rolcreatedb,
  r.rolcanlogin,
  r.rolreplication,
  r.rolbypassrls,
  r.rolconnlimit
FROM pg_roles AS r
WHERE r.rolname = :'ai_role';

SELECT
  table_schema,
  table_name,
  privilege_type,
  is_grantable
FROM information_schema.role_table_grants
WHERE grantee = :'ai_role'
ORDER BY table_schema, table_name, privilege_type;

-- Also inspect in psql:
-- \du ai_reader
-- \dn+ reporting
-- \dp reporting.*
-- \ddp

-- 8. Run these validation statements one at a time while connected as ai_role.
-- Replace example identifiers and use a disposable fixture for denial tests.
--
-- Expected: allowed.
-- SELECT * FROM reporting.ticket_summary LIMIT 5;
--
-- Expected: permission denied.
-- SELECT * FROM auth.users LIMIT 1;
-- INSERT INTO reporting.ticket_summary DEFAULT VALUES;
-- UPDATE app.customers SET display_name = display_name WHERE false;
-- DELETE FROM app.customers WHERE false;
-- CREATE TABLE reporting.agent_permission_test(id integer);
--
-- Expected: zero rows or permission denied, according to the policy design.
-- SELECT * FROM app.tickets WHERE organization_id <> 42 LIMIT 1;

-- 9. Emergency disablement and revocation examples.
-- Coordinate these actions because they affect every client using this role.
--
-- Stop new logins:
-- ALTER ROLE ai_reader NOLOGIN;
--
-- Remove one relation:
-- REVOKE SELECT ON reporting.ticket_summary FROM ai_reader;
--
-- Remove future-table defaults for one owner and schema:
-- ALTER DEFAULT PRIVILEGES
--   FOR ROLE app_owner
--   IN SCHEMA reporting
--   REVOKE SELECT ON TABLES FROM ai_reader;
--
-- Re-enable after review and credential rotation:
-- ALTER ROLE ai_reader LOGIN;
