PostgreSQL Permissions Script

-- =============================================================================
-- AnomalyArmor PostgreSQL Permissions Setup
-- =============================================================================
-- This script creates a minimal-privilege read-only user for AnomalyArmor.
--
-- WHAT THIS GRANTS:
-- - CONNECT: Access the database
-- - USAGE on schemas: View schema metadata
-- - SELECT on tables: Read table data and metadata
-- - Future privileges: Automatically grant on new tables
--
-- WHAT THIS DOES NOT GRANT:
-- - INSERT, UPDATE, DELETE: No data modification
-- - CREATE: No table/schema creation
-- - ADMIN: No user/role management
-- =============================================================================

-- Step 1: Create the read-only user
CREATE USER anomalyarmor WITH PASSWORD 'your-secure-password';

-- Step 2: Grant database connection
GRANT CONNECT ON DATABASE your_database TO anomalyarmor;

-- Step 3: Grant schema access
-- Repeat this section for each schema you want to monitor

-- Schema: public (common default schema)
GRANT USAGE ON SCHEMA public TO anomalyarmor;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO anomalyarmor;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO anomalyarmor;

-- Schema: Add more schemas as needed (copy and modify)
-- GRANT USAGE ON SCHEMA analytics TO anomalyarmor;
-- GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO anomalyarmor;
-- ALTER DEFAULT PRIVILEGES IN SCHEMA analytics GRANT SELECT ON TABLES TO anomalyarmor;

-- Run as anomalyarmor user to verify access

-- Test 1: Can access information_schema (required for discovery)
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' LIMIT 5;

-- Test 2: Can see column metadata (required for schema tracking)
SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'public' LIMIT 5;

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM anomalyarmor;
REVOKE USAGE ON SCHEMA public FROM anomalyarmor;
REVOKE CONNECT ON DATABASE your_database FROM anomalyarmor;
DROP USER anomalyarmor;