Database Security: Roles, Privileges & Row-Level Security

-- Database Roles: WHO can access the database

-- Create roles (users are roles with LOGIN privilege)
CREATE ROLE app_readonly;
CREATE ROLE app_readwrite;
CREATE ROLE app_admin;

-- Create login users and assign roles
CREATE USER api_service WITH PASSWORD 'strong_password_here';
CREATE USER analyst WITH PASSWORD 'another_strong_pwd';
CREATE USER dba_admin WITH PASSWORD 'super_secure_pwd';

GRANT app_readwrite TO api_service;
GRANT app_readonly TO analyst;
GRANT app_admin TO dba_admin;

-- Role 
...

-- Column-Level Security: hide sensitive data

-- Revoke access to salary column from readonly role:
REVOKE SELECT ON employees FROM app_readonly;
GRANT SELECT (id, name, department, hire_date) ON employees TO app_readonly;
-- app_readonly can see id, name, dept, hire_date — but NOT salary

-- Create a view that masks sensitive data:
CREATE VIEW v_employees_safe AS
SELECT
    id,
    name,
    department,
    hire_date,
    -- Mask email: show first 3 chars + domain
    LEFT(email, 3) || '***@' 
...

-- Row-Level Security (RLS): filter rows per user
-- "Each user sees only their own data"

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy: users see only their own orders
CREATE POLICY user_orders_policy ON orders
    FOR ALL
    TO app_readwrite
    USING (customer_id = current_setting('app.current_user_id')::int)
    WITH CHECK (customer_id = current_setting('app.current_user_id')::int);

-- USING: filters rows on SELECT, UPDATE, DELETE
-- WITH CHECK: validates rows on INSERT, UPDATE
...

-- Data Encryption strategies

-- 1. pgcrypto extension for column-level encryption
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Encrypt sensitive data:
INSERT INTO patient_records (patient_name, ssn_encrypted, diagnosis)
VALUES (
    'John Doe',
    pgp_sym_encrypt('123-45-6789', 'encryption_key_here'),
    pgp_sym_encrypt('Confidential diagnosis', 'encryption_key_here')
);

-- Decrypt when needed (requires the key):
SELECT patient_name,
    pgp_sym_decrypt(ssn_encrypted, 'encryption_key_here')
...

-- Security Auditing: track who did what

-- 1. Enable logging in postgresql.conf:
-- log_statement = 'all'        -- log every SQL statement
-- log_connections = on          -- log connections
-- log_disconnections = on       -- log disconnections
-- log_line_prefix = '%m [%p] %u@%d '  -- timestamp, PID, user@db

-- 2. Audit trigger (detailed per-row tracking)
CREATE TABLE security_audit_log (
    id BIGSERIAL PRIMARY KEY,
    table_name TEXT,
    operation TEXT,
    user_name TEXT DEFAULT curr
...