Activity 12: Basic Database User Authentication

# Create PostgreSQL container with admin access
docker run -d \
  --name security-db \
  -e POSTGRES_DB=secure_bank \
  -e POSTGRES_USER=admin \
  -e POSTGRES_PASSWORD=admin123 \
  -p 5432:5432 \
  postgres:15

# Connect as admin user
docker exec -it security-db psql -U admin -d secure_bank

-- Create a simple banking schema
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    account_number VARCHAR(20) UNIQUE NOT NULL,
    balance DECIMAL(12,2) DEFAULT 0.00,
    account_type VARCHAR(20) DEFAULT 'checking',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO customers (name, email, phone) VALUES
('John Smith', 'john@email.com', '555-1234'),
('Alice Johnson', 'alice@email.com', '555-5678'),
('Bob Wilson', 'bob@email.com', '555-9012');

INSERT INTO accounts (customer_id, account_number, balance, account_type) VALUES
(1, 'ACC-001-001', 5000.00, 'checking'),
(1, 'ACC-001-002', 15000.00, 'savings'),
(2, 'ACC-002-001', 3500.00, 'checking'),
(3, 'ACC-003-001', 8000.00, 'checking');

-- Verify sample data
SELECT * FROM customers;
SELECT * FROM accounts;

-- 1. Create a read-only role for customer service
CREATE ROLE customer_service;

-- 2. Create a limited access role for tellers
CREATE ROLE teller;

-- 3. Create a manager role with broader access
CREATE ROLE branch_manager;

-- View all roles
\du

-- Customer Service: Can only read customer and account information
GRANT CONNECT ON DATABASE secure_bank TO customer_service;
GRANT USAGE ON SCHEMA public TO customer_service;
GRANT SELECT ON customers, accounts TO customer_service;

-- Teller: Can read and update account balances
GRANT CONNECT ON DATABASE secure_bank TO teller;
GRANT USAGE ON SCHEMA public TO teller;
GRANT SELECT ON customers, accounts TO teller;
GRANT UPDATE (balance) ON accounts TO teller;

-- Branch Manager: Can read, insert, update, and delete (full access)
GRANT CONNECT ON DATABASE secure_bank TO branch_manager;
GRANT USAGE ON SCHEMA public TO branch_manager;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO branch_manager;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO branch_manager;

-- Create users for customer service team
CREATE USER sarah_cs WITH PASSWORD 'cs_password123';
CREATE USER mike_cs WITH PASSWORD 'cs_password456';

-- Assign customer service role
GRANT customer_service TO sarah_cs;
GRANT customer_service TO mike_cs;

-- Create teller users
CREATE USER tom_teller WITH PASSWORD 'teller_pass789';
CREATE USER jenny_teller WITH PASSWORD 'teller_pass101';

-- Assign teller role
GRANT teller TO tom_teller;
GRANT teller TO jenny_teller;

-- Create manager user
CREATE USER manager_jane WITH PASSWORD 'manager_secure2024';

-- Assign manager role
GRANT branch_manager TO manager_jane;

-- View all users and their roles
\du

-- Exit current session (or open new terminal)
\q

# Connect as customer service user
docker exec -it security-db psql -U sarah_cs -d secure_bank

-- Test read permissions (should work)
SELECT * FROM customers;
SELECT name, account_number, balance FROM customers c 
JOIN accounts a ON c.id = a.customer_id;

-- Test write permissions (should fail)
UPDATE accounts SET balance = 6000.00 WHERE id = 1;
-- Expected: ERROR: permission denied for table accounts

INSERT INTO customers (name, email) VALUES ('New Customer', 'new@email.com');
-- Expected: ERROR: permission denied for table customers

-- Exit and connect as teller
\q

docker exec -it security-db psql -U tom_teller -d secure_bank

-- Test read permissions (should work)
SELECT * FROM accounts WHERE account_number = 'ACC-001-001';

-- Test balance update (should work)
UPDATE accounts SET balance = 5100.00 WHERE account_number = 'ACC-001-001';

-- Verify update worked
SELECT account_number, balance FROM accounts WHERE account_number = 'ACC-001-001';

-- Test adding new customer (should fail)
INSERT INTO customers (name, email) VALUES ('Teller Customer', 'teller@email.com');
-- Expected: ERROR: permission denied for table customers

-- Test updating customer info (should fail)
UPDATE customers SET phone = '555-0000' WHERE id = 1;
-- Expected: ERROR: permission denied for table customers

-- Exit and connect as manager
\q

docker exec -it security-db psql -U manager_jane -d secure_bank

-- Test full access (all should work)
SELECT * FROM customers;

-- Insert new customer
INSERT INTO customers (name, email, phone) VALUES 
('Manager Added', 'manager@email.com', '555-MGMT');

-- Update customer information
UPDATE customers SET phone = '555-UPDATED' WHERE email = 'john@email.com';

-- Create new account
INSERT INTO accounts (customer_id, account_number, balance, account_type) VALUES 
(1, 'ACC-001-003', 2500.00, 'savings');

-- Delete account (be careful in real systems!)
DELETE FROM accounts WHERE account_number = 'ACC-001-003';

-- Verify all operations worked
SELECT * FROM customers;
SELECT * FROM accounts;

-- Connect as admin to check permissions
\q

docker exec -it security-db psql -U admin -d secure_bank

-- Check table privileges for specific users
SELECT 
    grantee,
    table_name,
    privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'public'
  AND grantee IN ('sarah_cs', 'tom_teller', 'manager_jane')
ORDER BY grantee, table_name;

-- Check role memberships
SELECT 
    r.rolname as role_name,
    m.rolname as member_name
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON am.member = m.oid
WHERE r.rolname IN ('customer_service', 'teller', 'branch_manager')
ORDER BY role_name, member_name;

-- Create audit table
CREATE TABLE access_log (
    id SERIAL PRIMARY KEY,
    username TEXT,
    table_accessed TEXT,
    action TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address INET DEFAULT inet_client_addr()
);

-- Create function to log access
CREATE OR REPLACE FUNCTION log_table_access()
RETURNS TRIGGER AS $
BEGIN
    INSERT INTO access_log (username, table_accessed, action)
    VALUES (current_user, TG_TABLE_NAME, TG_OP);
    RETURN COALESCE(NEW, OLD);
END;
$ LANGUAGE plpgsql;

-- Add triggers to log access to sensitive tables
CREATE TRIGGER customers_access_log
    AFTER INSERT OR UPDATE OR DELETE ON customers
    FOR EACH ROW EXECUTE FUNCTION log_table_access();

CREATE TRIGGER accounts_access_log
    AFTER INSERT OR UPDATE OR DELETE ON accounts
    FOR EACH ROW EXECUTE FUNCTION log_table_access();

-- Grant access log viewing to managers only
GRANT SELECT ON access_log TO branch_manager;

-- Make some changes as different users to generate log entries
-- (Connect as tom_teller)
UPDATE accounts SET balance = balance + 100 WHERE account_number = 'ACC-001-001';

-- (Connect as manager_jane) 
INSERT INTO customers (name, email) VALUES ('Audit Test', 'audit@test.com');

-- (Connect as admin or manager to view logs)
SELECT 
    username,
    table_accessed,
    action,
    timestamp
FROM access_log
ORDER BY timestamp DESC;