Implementing Database Security

# postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file  = 'server.key'
ssl_ca_file   = 'root.crt'
ssl_min_protocol_version = 'TLSv1.2'

psql 'host=db sslmode=verify-full sslrootcert=root.crt'

# postgresql.conf
password_encryption = scram-sha-256

# pgaudit extension
shared_preload_libraries = 'pgaudit'
pgaudit.log              = 'ddl, role, write'
pgaudit.log_catalog      = off
pgaudit.log_relation     = on

REVOKE ALL ON SCHEMA public FROM PUBLIC;
CREATE ROLE app_reader; CREATE ROLE app_writer;
GRANT  USAGE  ON SCHEMA app TO app_reader, app_writer;
GRANT  SELECT ON ALL TABLES IN SCHEMA app TO app_reader;
GRANT  INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_writer;
GRANT  app_reader TO app_writer;

# pg_hba.conf
hostssl all all 10.0.0.0/24 scram-sha-256
host    all all 0.0.0.0/0   reject

# mTLS
hostssl all alice 10.0.0.0/24 cert clientcert=verify-full

CREATE VIEW customers_masked AS
SELECT id, regexp_replace(email,'(?<=.{2}).(?=.*@)','*','g') AS email
  FROM customers;
REVOKE SELECT ON customers FROM support;
GRANT  SELECT ON customers_masked TO support;
-- Or use anon extension for production-grade masking

SELECT * FROM pg_stat_ssl;
SELECT * FROM pg_stat_activity WHERE backend_type='client backend' AND ssl IS NOT TRUE;
-- Plus: parse pgaudit log lines into a SIEM