1-- ============================================================================
2-- PostgreSQL Security and Row-Level Security (RLS) Examples
3-- ============================================================================
4-- This script demonstrates PostgreSQL security features including:
5-- - Role management and privileges
6-- - Row-Level Security (RLS) policies
7-- - Multi-tenant data isolation
8-- - Audit logging with triggers
9-- - Encryption and hashing with pgcrypto
10--
11-- Prerequisites:
12-- - PostgreSQL 9.5+ (for RLS)
13-- - Superuser or role creation privileges
14-- - pgcrypto extension
15--
16-- Usage:
17-- psql -U postgres -d your_database -f 07_security_rls.sql
18--
19-- IMPORTANT: Run this script as a superuser (postgres) to create roles.
20-- ============================================================================
21
22-- Clean up from previous runs
23DROP TABLE IF EXISTS projects CASCADE;
24DROP TABLE IF EXISTS documents CASCADE;
25DROP TABLE IF EXISTS audit_log CASCADE;
26DROP TABLE IF EXISTS sensitive_data CASCADE;
27
28-- Drop roles if they exist (ignore errors if they don't)
29DO $$
30BEGIN
31 IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'app_admin') THEN
32 REASSIGN OWNED BY app_admin TO postgres;
33 DROP OWNED BY app_admin;
34 DROP ROLE app_admin;
35 END IF;
36 IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'app_user') THEN
37 REASSIGN OWNED BY app_user TO postgres;
38 DROP OWNED BY app_user;
39 DROP ROLE app_user;
40 END IF;
41 IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'app_readonly') THEN
42 REASSIGN OWNED BY app_readonly TO postgres;
43 DROP OWNED BY app_readonly;
44 DROP ROLE app_readonly;
45 END IF;
46 IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'tenant_a_user') THEN
47 REASSIGN OWNED BY tenant_a_user TO postgres;
48 DROP OWNED BY tenant_a_user;
49 DROP ROLE tenant_a_user;
50 END IF;
51 IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'tenant_b_user') THEN
52 REASSIGN OWNED BY tenant_b_user TO postgres;
53 DROP OWNED BY tenant_b_user;
54 DROP ROLE tenant_b_user;
55 END IF;
56END
57$$;
58
59-- ============================================================================
60-- 1. Role Management and Privilege Levels
61-- ============================================================================
62
63-- Create roles with different privilege levels
64CREATE ROLE app_admin WITH LOGIN PASSWORD 'admin_password_123';
65CREATE ROLE app_user WITH LOGIN PASSWORD 'user_password_123';
66CREATE ROLE app_readonly WITH LOGIN PASSWORD 'readonly_password_123';
67
68-- Grant connection privilege to database
69GRANT CONNECT ON DATABASE postgres TO app_admin, app_user, app_readonly;
70
71-- Create a schema for application tables
72CREATE SCHEMA IF NOT EXISTS app;
73GRANT USAGE ON SCHEMA app TO app_admin, app_user, app_readonly;
74
75-- Create a sample table
76CREATE TABLE app.projects (
77 id SERIAL PRIMARY KEY,
78 name TEXT NOT NULL,
79 description TEXT,
80 owner TEXT NOT NULL,
81 created_at TIMESTAMPTZ DEFAULT NOW()
82);
83
84-- ============================================================================
85-- 2. GRANT and REVOKE Privileges
86-- ============================================================================
87
88-- Admin: Full access (SELECT, INSERT, UPDATE, DELETE)
89GRANT ALL PRIVILEGES ON TABLE app.projects TO app_admin;
90GRANT USAGE, SELECT ON SEQUENCE app.projects_id_seq TO app_admin;
91
92-- User: Read and write access (SELECT, INSERT, UPDATE)
93GRANT SELECT, INSERT, UPDATE ON TABLE app.projects TO app_user;
94GRANT USAGE, SELECT ON SEQUENCE app.projects_id_seq TO app_user;
95
96-- Readonly: Read-only access (SELECT)
97GRANT SELECT ON TABLE app.projects TO app_readonly;
98
99-- Grant schema-level privileges for future tables
100ALTER DEFAULT PRIVILEGES IN SCHEMA app
101 GRANT ALL PRIVILEGES ON TABLES TO app_admin;
102ALTER DEFAULT PRIVILEGES IN SCHEMA app
103 GRANT SELECT, INSERT, UPDATE ON TABLES TO app_user;
104ALTER DEFAULT PRIVILEGES IN SCHEMA app
105 GRANT SELECT ON TABLES TO app_readonly;
106
107-- Revoke specific privilege (e.g., prevent DELETE for app_user)
108REVOKE DELETE ON TABLE app.projects FROM app_user;
109
110-- Check current privileges
111SELECT
112 grantee,
113 privilege_type
114FROM information_schema.role_table_grants
115WHERE table_schema = 'app' AND table_name = 'projects'
116ORDER BY grantee, privilege_type;
117
118-- ============================================================================
119-- 3. Row-Level Security (RLS) Basics
120-- ============================================================================
121
122-- Create a documents table with RLS
123CREATE TABLE app.documents (
124 id SERIAL PRIMARY KEY,
125 title TEXT NOT NULL,
126 content TEXT,
127 owner TEXT NOT NULL, -- Stores the role name of the owner
128 created_at TIMESTAMPTZ DEFAULT NOW()
129);
130
131-- Insert sample data
132INSERT INTO app.documents (title, content, owner) VALUES
133 ('Admin Document', 'Confidential admin content', 'app_admin'),
134 ('User Document 1', 'User content 1', 'app_user'),
135 ('User Document 2', 'User content 2', 'app_user'),
136 ('Readonly Document', 'Public content', 'app_readonly');
137
138-- Enable Row-Level Security on the table
139ALTER TABLE app.documents ENABLE ROW LEVEL SECURITY;
140
141-- Without policies, no non-owner can access rows (default deny)
142-- Superusers and table owners bypass RLS by default
143
144-- Create policy: Users can only see their own documents
145CREATE POLICY documents_isolation_policy ON app.documents
146 FOR ALL
147 TO PUBLIC
148 USING (owner = current_user);
149
150-- Create policy: Allow SELECT for all authenticated users
151CREATE POLICY documents_select_policy ON app.documents
152 FOR SELECT
153 TO PUBLIC
154 USING (true);
155
156-- Create policy: Users can only modify their own documents
157CREATE POLICY documents_modify_policy ON app.documents
158 FOR INSERT
159 TO PUBLIC
160 WITH CHECK (owner = current_user);
161
162-- Grant privileges to roles
163GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE app.documents TO app_admin, app_user, app_readonly;
164GRANT USAGE, SELECT ON SEQUENCE app.documents_id_seq TO app_admin, app_user;
165
166-- Test RLS policies (in separate sessions):
167-- SET ROLE app_user;
168-- SELECT * FROM app.documents; -- Returns all documents (SELECT policy)
169-- INSERT INTO app.documents (title, content, owner) VALUES ('New Doc', 'Content', 'app_user'); -- Success
170-- INSERT INTO app.documents (title, content, owner) VALUES ('Fake Doc', 'Content', 'app_admin'); -- Fails (owner mismatch)
171-- RESET ROLE;
172
173-- ============================================================================
174-- 4. Multi-Tenant Isolation using Session Variables
175-- ============================================================================
176
177-- Create multi-tenant table
178CREATE TABLE app.tenant_data (
179 id SERIAL PRIMARY KEY,
180 tenant_id INTEGER NOT NULL,
181 data TEXT NOT NULL,
182 created_at TIMESTAMPTZ DEFAULT NOW()
183);
184
185-- Insert sample data for multiple tenants
186INSERT INTO app.tenant_data (tenant_id, data) VALUES
187 (1, 'Tenant 1 - Record A'),
188 (1, 'Tenant 1 - Record B'),
189 (2, 'Tenant 2 - Record A'),
190 (2, 'Tenant 2 - Record B'),
191 (3, 'Tenant 3 - Record A');
192
193-- Enable RLS
194ALTER TABLE app.tenant_data ENABLE ROW LEVEL SECURITY;
195
196-- Create policy using session variable
197CREATE POLICY tenant_isolation_policy ON app.tenant_data
198 FOR ALL
199 TO PUBLIC
200 USING (tenant_id = current_setting('app.current_tenant_id')::INTEGER);
201
202-- Grant privileges
203GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE app.tenant_data TO app_user;
204GRANT USAGE, SELECT ON SEQUENCE app.tenant_data_id_seq TO app_user;
205
206-- Test multi-tenant isolation:
207-- SET app.current_tenant_id = '1';
208-- SELECT * FROM app.tenant_data; -- Returns only tenant 1 data
209--
210-- SET app.current_tenant_id = '2';
211-- SELECT * FROM app.tenant_data; -- Returns only tenant 2 data
212--
213-- RESET app.current_tenant_id;
214
215-- Create tenant-specific roles
216CREATE ROLE tenant_a_user WITH LOGIN PASSWORD 'tenant_a_password';
217CREATE ROLE tenant_b_user WITH LOGIN PASSWORD 'tenant_b_password';
218
219GRANT CONNECT ON DATABASE postgres TO tenant_a_user, tenant_b_user;
220GRANT USAGE ON SCHEMA app TO tenant_a_user, tenant_b_user;
221GRANT SELECT, INSERT, UPDATE ON TABLE app.tenant_data TO tenant_a_user, tenant_b_user;
222
223-- Set default tenant_id for each role using ALTER ROLE
224ALTER ROLE tenant_a_user SET app.current_tenant_id = '1';
225ALTER ROLE tenant_b_user SET app.current_tenant_id = '2';
226
227-- ============================================================================
228-- 5. Audit Logging with Triggers
229-- ============================================================================
230
231-- Create audit log table
232CREATE TABLE app.audit_log (
233 id SERIAL PRIMARY KEY,
234 table_name TEXT NOT NULL,
235 operation TEXT NOT NULL, -- INSERT, UPDATE, DELETE
236 record_id INTEGER,
237 old_data JSONB,
238 new_data JSONB,
239 changed_by TEXT DEFAULT current_user,
240 changed_at TIMESTAMPTZ DEFAULT NOW()
241);
242
243-- Create audit trigger function
244CREATE OR REPLACE FUNCTION app.audit_trigger_func()
245RETURNS TRIGGER AS $$
246BEGIN
247 IF (TG_OP = 'DELETE') THEN
248 INSERT INTO app.audit_log (table_name, operation, record_id, old_data)
249 VALUES (TG_TABLE_NAME, TG_OP, OLD.id, row_to_json(OLD)::jsonb);
250 RETURN OLD;
251 ELSIF (TG_OP = 'UPDATE') THEN
252 INSERT INTO app.audit_log (table_name, operation, record_id, old_data, new_data)
253 VALUES (TG_TABLE_NAME, TG_OP, NEW.id, row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb);
254 RETURN NEW;
255 ELSIF (TG_OP = 'INSERT') THEN
256 INSERT INTO app.audit_log (table_name, operation, record_id, new_data)
257 VALUES (TG_TABLE_NAME, TG_OP, NEW.id, row_to_json(NEW)::jsonb);
258 RETURN NEW;
259 END IF;
260 RETURN NULL;
261END;
262$$ LANGUAGE plpgsql SECURITY DEFINER;
263
264-- Attach audit trigger to projects table
265CREATE TRIGGER audit_projects_trigger
266 AFTER INSERT OR UPDATE OR DELETE ON app.projects
267 FOR EACH ROW EXECUTE FUNCTION app.audit_trigger_func();
268
269-- Test audit logging
270INSERT INTO app.projects (name, description, owner) VALUES
271 ('Test Project', 'Test description', 'app_admin');
272
273UPDATE app.projects SET description = 'Updated description' WHERE name = 'Test Project';
274
275DELETE FROM app.projects WHERE name = 'Test Project';
276
277-- View audit log
278SELECT
279 id,
280 table_name,
281 operation,
282 record_id,
283 changed_by,
284 changed_at,
285 old_data->>'name' AS old_name,
286 new_data->>'name' AS new_name
287FROM app.audit_log
288ORDER BY changed_at DESC;
289
290-- Grant read-only access to audit log
291GRANT SELECT ON TABLE app.audit_log TO app_admin;
292
293-- ============================================================================
294-- 6. Encryption and Hashing with pgcrypto
295-- ============================================================================
296
297-- Enable pgcrypto extension
298CREATE EXTENSION IF NOT EXISTS pgcrypto;
299
300-- Create table for sensitive data
301CREATE TABLE app.sensitive_data (
302 id SERIAL PRIMARY KEY,
303 username TEXT NOT NULL UNIQUE,
304 password_hash TEXT NOT NULL, -- Store hashed passwords
305 email_encrypted BYTEA, -- Store encrypted email
306 api_key_encrypted BYTEA, -- Store encrypted API key
307 created_at TIMESTAMPTZ DEFAULT NOW()
308);
309
310-- ============================================================================
311-- 6.1 Password Hashing
312-- ============================================================================
313
314-- Insert user with hashed password (using bcrypt)
315INSERT INTO app.sensitive_data (username, password_hash)
316VALUES ('alice', crypt('alice_secret_password', gen_salt('bf')));
317
318INSERT INTO app.sensitive_data (username, password_hash)
319VALUES ('bob', crypt('bob_secret_password', gen_salt('bf', 8))); -- Cost factor 8
320
321-- Verify password (returns true/false)
322SELECT
323 username,
324 (password_hash = crypt('alice_secret_password', password_hash)) AS password_valid
325FROM app.sensitive_data
326WHERE username = 'alice';
327
328-- Login function
329CREATE OR REPLACE FUNCTION app.authenticate_user(
330 p_username TEXT,
331 p_password TEXT
332) RETURNS BOOLEAN AS $$
333DECLARE
334 v_password_hash TEXT;
335BEGIN
336 SELECT password_hash INTO v_password_hash
337 FROM app.sensitive_data
338 WHERE username = p_username;
339
340 IF NOT FOUND THEN
341 RETURN FALSE;
342 END IF;
343
344 RETURN (v_password_hash = crypt(p_password, v_password_hash));
345END;
346$$ LANGUAGE plpgsql SECURITY DEFINER;
347
348-- Test authentication
349SELECT app.authenticate_user('alice', 'alice_secret_password'); -- true
350SELECT app.authenticate_user('alice', 'wrong_password'); -- false
351
352-- ============================================================================
353-- 6.2 Symmetric Encryption (PGP)
354-- ============================================================================
355
356-- Encryption key (in production, store securely outside database)
357DO $$
358DECLARE
359 encryption_key TEXT := 'my-secret-encryption-key-2024';
360BEGIN
361 -- Encrypt and store email
362 UPDATE app.sensitive_data
363 SET email_encrypted = pgp_sym_encrypt('alice@example.com', encryption_key)
364 WHERE username = 'alice';
365
366 UPDATE app.sensitive_data
367 SET email_encrypted = pgp_sym_encrypt('bob@example.com', encryption_key)
368 WHERE username = 'bob';
369END $$;
370
371-- Decrypt email (in application code, not directly in queries)
372DO $$
373DECLARE
374 encryption_key TEXT := 'my-secret-encryption-key-2024';
375BEGIN
376 RAISE NOTICE 'Alice email: %', (
377 SELECT pgp_sym_decrypt(email_encrypted, encryption_key)
378 FROM app.sensitive_data
379 WHERE username = 'alice'
380 );
381END $$;
382
383-- ============================================================================
384-- 6.3 Asymmetric Encryption (Public/Private Key)
385-- ============================================================================
386
387-- Generate key pair (in production, use external tools like GPG)
388DO $$
389DECLARE
390 public_key TEXT := '-----BEGIN PGP PUBLIC KEY BLOCK-----
391...
392-----END PGP PUBLIC KEY BLOCK-----';
393 private_key TEXT := '-----BEGIN PGP PRIVATE KEY BLOCK-----
394...
395-----END PGP PRIVATE KEY BLOCK-----';
396 passphrase TEXT := 'key-passphrase';
397BEGIN
398 -- In practice, you would use real PGP keys here
399 -- This is a placeholder to demonstrate the API
400
401 -- Encrypt with public key
402 -- UPDATE app.sensitive_data
403 -- SET api_key_encrypted = pgp_pub_encrypt('sk-1234567890abcdef', dearmor(public_key))
404 -- WHERE username = 'alice';
405
406 -- Decrypt with private key
407 -- SELECT pgp_pub_decrypt(api_key_encrypted, dearmor(private_key), passphrase)
408 -- FROM app.sensitive_data
409 -- WHERE username = 'alice';
410
411 RAISE NOTICE 'Asymmetric encryption example (use real PGP keys in production)';
412END $$;
413
414-- ============================================================================
415-- 6.4 Hashing (One-way)
416-- ============================================================================
417
418-- SHA-256 hash for data integrity checks
419SELECT encode(digest('sensitive-data', 'sha256'), 'hex') AS sha256_hash;
420
421-- HMAC for message authentication
422SELECT encode(hmac('message', 'secret-key', 'sha256'), 'hex') AS hmac_sha256;
423
424-- Example: API key hashing for secure storage
425CREATE TABLE app.api_keys (
426 id SERIAL PRIMARY KEY,
427 user_id INTEGER NOT NULL,
428 key_hash TEXT NOT NULL, -- Store hash, not plaintext
429 key_prefix TEXT NOT NULL, -- Store prefix for user identification (e.g., 'sk-1234...')
430 created_at TIMESTAMPTZ DEFAULT NOW()
431);
432
433-- Function to create API key
434CREATE OR REPLACE FUNCTION app.create_api_key(p_user_id INTEGER)
435RETURNS TEXT AS $$
436DECLARE
437 v_api_key TEXT;
438 v_key_hash TEXT;
439 v_key_prefix TEXT;
440BEGIN
441 -- Generate random API key
442 v_api_key := 'sk-' || encode(gen_random_bytes(32), 'hex');
443
444 -- Hash for storage
445 v_key_hash := encode(digest(v_api_key, 'sha256'), 'hex');
446
447 -- Store prefix for user reference
448 v_key_prefix := substring(v_api_key from 1 for 10) || '...';
449
450 -- Save to database
451 INSERT INTO app.api_keys (user_id, key_hash, key_prefix)
452 VALUES (p_user_id, v_key_hash, v_key_prefix);
453
454 -- Return plaintext key (only time it's visible)
455 RETURN v_api_key;
456END;
457$$ LANGUAGE plpgsql;
458
459-- Function to validate API key
460CREATE OR REPLACE FUNCTION app.validate_api_key(p_api_key TEXT)
461RETURNS INTEGER AS $$
462DECLARE
463 v_user_id INTEGER;
464BEGIN
465 SELECT user_id INTO v_user_id
466 FROM app.api_keys
467 WHERE key_hash = encode(digest(p_api_key, 'sha256'), 'hex');
468
469 RETURN v_user_id;
470END;
471$$ LANGUAGE plpgsql;
472
473-- Test API key management
474DO $$
475DECLARE
476 new_key TEXT;
477 user_id INTEGER;
478BEGIN
479 -- Create API key for user 1
480 new_key := app.create_api_key(1);
481 RAISE NOTICE 'Generated API key: %', new_key;
482
483 -- Validate the key
484 user_id := app.validate_api_key(new_key);
485 RAISE NOTICE 'Key belongs to user: %', user_id;
486
487 -- Try invalid key
488 user_id := app.validate_api_key('sk-invalid-key');
489 RAISE NOTICE 'Invalid key user: %', user_id; -- NULL
490END $$;
491
492-- View stored API keys (hashes only)
493SELECT id, user_id, key_prefix, created_at
494FROM app.api_keys;
495
496-- ============================================================================
497-- 7. Security Best Practices Summary
498-- ============================================================================
499
500-- View all role privileges
501SELECT
502 r.rolname,
503 r.rolsuper,
504 r.rolinherit,
505 r.rolcreaterole,
506 r.rolcreatedb,
507 r.rolcanlogin
508FROM pg_roles r
509WHERE r.rolname LIKE 'app_%' OR r.rolname LIKE 'tenant_%'
510ORDER BY r.rolname;
511
512-- View RLS policies
513SELECT
514 schemaname,
515 tablename,
516 policyname,
517 permissive,
518 roles,
519 cmd,
520 qual,
521 with_check
522FROM pg_policies
523WHERE schemaname = 'app'
524ORDER BY tablename, policyname;
525
526-- ============================================================================
527-- Security Checklist
528-- ============================================================================
529-- ✓ 1. Use roles with least privilege principle
530-- ✓ 2. Grant specific privileges (SELECT, INSERT) instead of ALL
531-- ✓ 3. Enable Row-Level Security for multi-tenant applications
532-- ✓ 4. Use session variables for dynamic tenant isolation
533-- ✓ 5. Implement audit logging for compliance
534-- ✓ 6. Hash passwords with bcrypt (NEVER store plaintext)
535-- ✓ 7. Encrypt sensitive data (email, API keys, PII)
536-- ✓ 8. Use prepared statements to prevent SQL injection
537-- ✓ 9. Regularly rotate encryption keys and credentials
538-- ✓ 10. Monitor and review pg_stat_activity for suspicious queries
539-- ✓ 11. Use SSL/TLS for client connections
540-- ✓ 12. Restrict network access with pg_hba.conf
541-- ✓ 13. Keep PostgreSQL updated with security patches
542-- ✓ 14. Use connection pooling (pgBouncer) to limit connections
543-- ✓ 15. Enable query logging for security audits
544-- ============================================================================