Database security and access control

-- Row Level Security (RLS) in PostgreSQL

-- Enable RLS on table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Create policy: Users see only their own documents
CREATE POLICY user_documents ON documents
  FOR ALL
  TO PUBLIC
  USING (user_id = current_user_id());

-- Helper function to get current user
CREATE OR REPLACE FUNCTION current_user_id()
RETURNS INT AS $$
  SELECT NULLIF(current_setting('app.current_user_id', true), '')::INT;
$$ LANGUAGE SQL STABLE;

-- Set current user (application sets this per session)
SET app.current_user_id = '123';

-- Now queries automatically filter
SELECT * FROM documents;
-- Only returns documents where user_id = 123

-- Different policies for different operations
CREATE POLICY documents_select ON documents
  FOR SELECT
  USING (user_id = current_user_id() OR is_public = true);

CREATE POLICY documents_insert ON documents
  FOR INSERT
  WITH CHECK (user_id = current_user_id());

CREATE POLICY documents_update ON documents
  FOR UPDATE
  USING (user_id = current_user_id())
  WITH CHECK (user_id = current_user_id());

CREATE POLICY documents_delete ON documents
  FOR DELETE
  USING (user_id = current_user_id());

-- Admin bypass (specific role can see all)
CREATE POLICY admin_all ON documents
  FOR ALL
  TO admin_role
  USING (true);

-- Multi-tenant isolation
CREATE POLICY tenant_isolation ON orders
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::INT);

-- Force RLS even for table owner
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

-- Disable RLS for specific role (superuser)
ALTER TABLE documents DISABLE ROW LEVEL SECURITY;

-- View active policies
SELECT
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
FROM pg_policies
WHERE tablename = 'documents';

-- Drop policy
DROP POLICY user_documents ON documents;

-- Data masking with views
CREATE VIEW users_safe AS
SELECT
  id,
  username,
  email,
  '***-**-' || RIGHT(ssn, 4) AS ssn_masked,
  CASE
    WHEN current_user_role() = 'admin' THEN phone
    ELSE 'XXX-XXX-' || RIGHT(phone, 4)
  END AS phone
FROM users;

GRANT SELECT ON users_safe TO readonly;
REVOKE ALL ON users FROM readonly;

-- Encryption at rest (PostgreSQL with pgcrypto)
CREATE EXTENSION pgcrypto;

-- Encrypt sensitive data
INSERT INTO users (email, ssn)
VALUES (
  'user@example.com',
  pgp_sym_encrypt('123-45-6789', 'encryption_key')
);

-- Decrypt
SELECT
  email,
  pgp_sym_decrypt(ssn, 'encryption_key') AS ssn
FROM users;

-- Audit logging
CREATE TABLE audit_log (
  id SERIAL PRIMARY KEY,
  table_name VARCHAR(50),
  operation VARCHAR(10),
  user_name VARCHAR(50),
  old_data JSONB,
  new_data JSONB,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log (table_name, operation, user_name, old_data)
    VALUES (TG_TABLE_NAME, 'DELETE', current_user, row_to_json(OLD));
    RETURN OLD;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log (table_name, operation, user_name, old_data, new_data)
    VALUES (TG_TABLE_NAME, 'UPDATE', current_user,
            row_to_json(OLD), row_to_json(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log (table_name, operation, user_name, new_data)
    VALUES (TG_TABLE_NAME, 'INSERT', current_user, row_to_json(NEW));
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_trigger();