-- Create roles
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE admin WITH LOGIN PASSWORD 'secure_password';
-- Grant permissions to roles
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
-- Grant future tables automatically
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly;
-- Read-write permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public
TO readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readwrite;
-- Create user and assign role
CREATE USER app_user WITH LOGIN PASSWORD 'password123';
GRANT readwrite TO app_user;
-- Revoke permissions
REVOKE DELETE ON users FROM readwrite;
REVOKE ALL ON TABLE sensitive_data FROM PUBLIC;
-- Column-level permissions
GRANT SELECT (id, email, name) ON users TO readonly;
-- Can't see password_hash, ssn, etc.
-- View permissions
SELECT
grantee,
table_schema,
table_name,
privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'readonly';
-- Schema-level permissions
CREATE SCHEMA app_schema;
GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT ALL ON ALL TABLES IN SCHEMA app_schema TO app_user;
-- Function execution permissions
CREATE FUNCTION get_user_count() RETURNS INT AS $$
SELECT COUNT(*) FROM users;
$$ LANGUAGE SQL SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION get_user_count() TO readonly;
-- SECURITY DEFINER: Runs with creator's permissions
-- SECURITY INVOKER: Runs with caller's permissions (default)
-- Remove login capability
ALTER ROLE readonly NOLOGIN;
-- Set connection limits
ALTER ROLE app_user CONNECTION LIMIT 10;
-- Password policies
ALTER ROLE app_user VALID UNTIL '2025-12-31';
-- Require SSL connections
ALTER ROLE app_user SET ssl TO on;
-- 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();
