GRANT and REVOKE Permission Management

-- Create roles
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE app_user LOGIN PASSWORD 'secure_password';

-- Schema usage
GRANT USAGE ON SCHEMA public TO readonly;
GRANT USAGE ON SCHEMA public TO readwrite;

-- Readonly: SELECT
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

-- Readwrite: full DML
GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES
  IN SCHEMA public TO readwrite;

-- Assign roles
GRANT readonly TO analyst_user;
GRANT readwrite TO app_user;

-- Revoke specific permission
REVOKE DELETE ON sensitive_table FROM readwrite;

-- Check grants
SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
ORDER BY grantee, table_name;