1.1Building the Database Server

Where Silver was about getting a server running, Gold raises the bar to a server that can withstand an audit, encrypts traffic, and detects tampering. A production server is only truly "built" once you have encrypted communication, a secure password verification method, an audit trail of who did what, and checksums that catch disk corruption early. This section walks through these four pillars in the order you would actually harden a server.

1.1.1SSL communication and data encryption

• SSL encrypts the communication channel between client and server. Enable it with ssl = on in postgresql.conf and provide a server certificate and private key (ssl_cert_file/ssl_key_file). A hostssl line in pg_hba.conf can enforce SSL-only connections.
• pgcrypto is an extension (CREATE EXTENSION pgcrypto) for encrypting the stored data itself, not the wire. It provides functions like crypt() (password hashing) and pgp_sym_encrypt()/pgp_sym_decrypt() (symmetric encryption)—covering data-at-rest encryption, which SSL does not.
• pg_stat_ssl is a system view showing each backend's SSL connection status (whether SSL is used, protocol version, cipher suite). Joining it with pg_stat_activity on pid lets you audit which sessions are actually SSL-encrypted.

1.1.2Client authentication and audit logging

• SCRAM-SHA-256 is the most secure password-based auth method selectable in pg_hba.conf. Unlike md5, whose hash sits in the catalog and is weaker against replay-style attacks, SCRAM-SHA-256 uses a challenge-response exchange that never sends the password itself. New builds should adopt it as the default (password_encryption = scram-sha-256).
• The core of audit logging is log_statement (none/ddl/mod/all—how much of the executed SQL text itself gets logged). track_functions (whether PL/pgSQL function-call stats are aggregated into pg_stat_user_functions) and track_activities (whether currently running queries are reflected in pg_stat_activity) are activity-statistics/monitoring settings, not audit logging, and should be distinguished from log_statement.
• ALTER ROLE/ALTER DATABASE pin SET parameters per user or per database—e.g., ALTER ROLE app_user SET statement_timeout = '30s' forces that behavior only for a specific role. For an audit requirement like "this user must always log full statements," use ALTER ROLE audit_user SET log_statement = 'all'.

Walk through the practical new-server build flow. First, initialize the cluster with initdb --data-checksums. This is an option that can only be chosen at build time—it cannot be turned on after initdb has run—and from then on every page carries a checksum, letting you detect physical disk corruption (bit rot) on read. Next, set ssl = on in postgresql.conf, install a server certificate, and standardize every pg_hba.conf line on scram-sha-256 authentication (replacing any lingering md5 lines). If there is an audit requirement, scope it appropriately with log_statement = 'mod' (log only modifying statements) or track_functions = pl (aggregate only PL-language functions), and force full-statement logging for a specific sensitive role with ALTER ROLE finance_admin SET log_statement = 'all'. For especially sensitive stored columns (e.g., national ID or credit card numbers), encrypt them with pgcrypto's pgp_sym_encrypt(column, key) and decrypt on read with pgp_sym_decrypt(). After building, confirm SSL is actually in effect with a query like SELECT pid, ssl, version, cipher FROM pg_stat_ssl JOIN pg_stat_activity USING (pid);. You also need to know the cluster's directory layout: pg_tblspc holds symlinks to tablespaces, pg_wal (formerly pg_xlog) holds WAL segment files, and pg_stat_tmp holds temporary statistics files, whose location can be changed with stats_temp_directory (some operators point it at tmpfs to cut disk I/O).

1.1.3Section summary

• SSL = wire encryption / pgcrypto = data-at-rest encryption; check status with pg_stat_ssl. SCRAM-SHA-256 is the most secure auth method
• Audit log = log_statement (SQL text) / activity stats = track_functions (function stats), track_activities (running queries) (track_* are not audit logging); pin per user/DB with ALTER ROLE/DATABASE
• initdb --data-checksums is build-time only. Cluster directories: pg_tblspc (tablespace links), pg_wal (WAL), pg_stat_tmp (stats temp files)