Authentication

-- Create user with password
CREATE USER alice WITH PASSWORD 'secure_password_123';

-- Create user with encrypted password
CREATE USER bob WITH ENCRYPTED PASSWORD 'md5...' VALID UNTIL '2025-12-31';

-- Set password policy
ALTER SYSTEM SET password_min_length = 12;
ALTER SYSTEM SET password_require_uppercase = true;
ALTER SYSTEM SET password_require_numbers = true;
ALTER SYSTEM SET password_require_special = true;
ALTER SYSTEM SET password_expiry_days = 90;

-- Force password change on next login
ALTER USER alice PASSWORD_EXPIRE;

-- Check authentication attempts
SELECT * FROM entropy_auth_log 
WHERE username = 'alice' 
AND timestamp > NOW() - INTERVAL '1 hour';

# Configure SAML SSO
# entropydb.conf
authentication_mode = 'saml'
saml_idp_metadata_url = 'https://sso.example.com/metadata'
saml_sp_entity_id = 'entropydb.example.com'
saml_acs_url = 'https://entropydb.example.com/saml/acs'

# Or OAuth 2.0
authentication_mode = 'oauth'
oauth_provider = 'okta'  # or 'auth0', 'azure_ad', 'google'
oauth_client_id = 'your_client_id'
oauth_client_secret = 'your_client_secret'
oauth_authorization_endpoint = 'https://dev-12345.okta.com/oauth2/v1/authorize'
oauth_token_endpoint = 'https://dev-12345.okta.com/oauth2/v1/token'
oauth_userinfo_endpoint = 'https://dev-12345.okta.com/oauth2/v1/userinfo'

# Map SSO attributes to roles
CREATE ROLE MAPPING sso_admin
  FOR SSO ATTRIBUTE 'groups' VALUE 'admin'
  GRANT admin_role;

CREATE ROLE MAPPING sso_readonly
  FOR SSO ATTRIBUTE 'groups' VALUE 'readonly'
  GRANT readonly_role;

# Configure LDAP authentication
# entropydb.conf
authentication_mode = 'ldap'
ldap_server = 'ldap://ldap.example.com:389'
ldap_bind_dn = 'cn=entropydb,ou=services,dc=example,dc=com'
ldap_bind_password = 'ldap_password'
ldap_search_base = 'ou=users,dc=example,dc=com'
ldap_search_filter = '(&(objectClass=person)(uid=%u))'
ldap_group_base = 'ou=groups,dc=example,dc=com'

# Enable LDAPS (LDAP over SSL)
ldap_server = 'ldaps://ldap.example.com:636'
ldap_require_ssl = true
ldap_ca_cert = '/etc/entropydb/ldap-ca.crt'

# Map LDAP groups to entropyDB roles
CREATE ROLE MAPPING ldap_admins
  FOR LDAP GROUP 'cn=db-admins,ou=groups,dc=example,dc=com'
  GRANT admin_role;

-- Test LDAP connection
SELECT entropy_test_ldap_connection();

# Enable certificate authentication
# entropydb.conf
ssl = on
ssl_ca_file = '/etc/entropydb/ca.crt'
ssl_cert_file = '/etc/entropydb/server.crt'
ssl_key_file = '/etc/entropydb/server.key'
ssl_client_auth = 'verify-full'

# Create user mapped to certificate
CREATE USER alice WITH CERTIFICATE;

# Map certificate CN to user
ALTER SYSTEM SET ssl_cert_mapping = 'cn';

# Connect with client certificate
psql "host=entropydb.example.com \
      sslmode=verify-full \
      sslcert=client.crt \
      sslkey=client.key \
      sslrootcert=ca.crt \
      user=alice"

-- Enable MFA for user
ALTER USER alice ENABLE MFA;

-- User enrolls MFA device
-- This generates QR code for TOTP app
SELECT entropy_mfa_enroll('alice');

-- Verify MFA setup
SELECT entropy_mfa_verify('alice', '123456');

-- Require MFA for specific roles
ALTER ROLE admin REQUIRE MFA;

-- Connect with MFA
-- Client provides both password and TOTP code
const client = await pool.connect({
  user: 'alice',
  password: 'password123',
  mfa_token: '123456'
});

-- Check MFA status
SELECT 
  username,
  mfa_enabled,
  mfa_enrolled_at,
  last_mfa_verification
FROM entropy_users
WHERE mfa_enabled = true;