Authentication

Chapter 46: Authentication & Users

Managing Database Security

---

46.1 User Management

-- Create user
CREATE USER john WITH PASSWORD 'secret_password';

-- Create with privileges
CREATE USER admin WITH PASSWORD 'admin_pass' CREATEDB CREATEROLE;

-- Create role
CREATE ROLE developer;

-- Grant privileges
GRANT SELECT, INSERT ON orders TO john;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO admin;

-- Grant role to user
GRANT developer TO john;

-- Revoke privileges
REVOKE INSERT ON orders FROM john;

-- Drop user
DROP USER IF EXISTS john;

---

46.2 Authentication Methods

# pg_hba.conf - Host-Based Authentication

# Local connections
local   all             all                                     peer

# IPv4 local connections
host    all             all             127.0.0.1/32            scram-sha-256

# IPv6 local connections
host    all             all             ::1/128                 scram-sha-256

# Replication connections
host    replication     all             10.0.0.0/24             scram-sha-256

---

46.3 Password Policies

-- Check password expiration
ALTER USER john VALID UNTIL '2024-12-31';

-- Require password change
ALTER USER john PASSWORD NULL;

-- Set password (encrypted)
SET password_encryption = 'scram-sha-256';
ALTER USER john PASSWORD 'new_password';

-- Lock account after failed attempts
ALTER USER john WITH LOGIN;

---

Summary

Command	Purpose
CREATE USER	Create database user
GRANT	Give privileges
REVOKE	Remove privileges
pg_hba.conf	Authentication config

---

Next: Chapter 47: Row-Level Security