PostgreSQL - Hardened SQL Database with pgAdmin Web UI

What is PostgreSQL

PostgreSQL is a powerful open-source object-relational database management system, implemented in C as a robust multi-process server with multi-version concurrency control (MVCC). It offers fully ACID-compliant transactions, the SQL standard plus rich extensions, native JSON and JSONB, full-text search, window functions, common table expressions, materialized views, table partitioning, and GiST/GIN/BRIN indexing. Logical and streaming replication are built in, alongside a mature extension ecosystem - the contrib modules pg_stat_statements, pgcrypto, and uuid-ossp ship in the image. It speaks the standard wire protocol, so it connects from every language through libpq, JDBC, and ODBC drivers. Data is persisted on disk with write-ahead logging and page checksums enabled. This image also bundles the pgAdmin 4 web administration UI for browser-based schema, query, and role management. PostgreSQL license, no vendor lock-in.

Why self-host PostgreSQL

Running PostgreSQL on a VM you control keeps every row of data inside your own tenant rather than a managed service. You avoid per-query and storage premiums, retain full control over extensions, configuration, and major-version timing, and keep sensitive records inside your own perimeter. Ideal for teams with data residency requirements and organisations operating under regulated frameworks such as GDPR, ISO 27001, or HIPAA. The PostgreSQL license is permissive and fully auditable.

What this VM image adds

Security hardening:

• scram-sha-256 auth required - the postgres superuser password plus a sample application database and role are generated per instance at first boot, never a default or empty password
• No trust authentication anywhere - every host connection in pg_hba.conf requires scram-sha-256
• Native TLS at first boot - a per-instance self-signed certificate is generated on the VM; no shared private key is baked into the image, and clients connect with sslmode=require
• Bundled pgAdmin 4 web UI - served only over HTTPS behind an Nginx TLS perimeter on 443 (/pgadmin4/); pgAdmin binds to 127.0.0.1 only and the admin login is generated per instance at first boot
• Network-internal database access - PostgreSQL on 5432 is governed by your network security group, not exposed to the internet
• CVE scan - every image is scanned for vulnerabilities with Trivy before release
• UFW firewall - SSH on 22 and HTTPS on 443 for pgAdmin; 5432 governed by your NSG
• fail2ban - SSH brute-force protection
• AppArmor - mandatory access control

OS hardening (CIS Level 1):

• CIS Level 1 hardened - CIS Ubuntu 24.04 LTS Level 1 Benchmark via ansible-lockdown
• auditd - system call auditing for critical paths
• SSH hardening - PasswordAuthentication disabled, key-only access
• Kernel hardening - SYN cookies, ASLR, rp_filter, TCP BBR
• /tmp as tmpfs - nosuid, nodev, noexec
• Azure IMDS endpoints - egress rules pre-configured (169.254.169.254, 168.63.129.16)

Compliance artifacts (inside the VM):

• SBOM - CycloneDX 1.6 at /etc/lynxroute/sbom.json
• CIS Conformance Report - OpenSCAP HTML at /etc/lynxroute/cis-report.html (Azure tailoring profile, 0 FAIL rules)
• Tailored CIS profile - /usr/share/doc/lynxroute/CIS_TAILORED_PROFILE.md
• Operator credentials file - /root/postgresql-credentials.txt (mode 0600) with the superuser password, the sample appdb/appuser credentials, and the pgAdmin admin login

Quick Start

1. Deploy VM from Azure Marketplace (Standard_D2s_v3 or larger recommended)
2. SSH: ssh -i key.pem azureuser@<PUBLIC_IP>, then sudo cat /root/postgresql-credentials.txt for all generated credentials
3. Open NSG: TCP 5432 from your trusted app-tier sources only, TCP 443 from the admin IPs that will use pgAdmin, TCP 22 from your management IPs
4. Connect: psql "host=<PRIVATE_IP> port=5432 dbname=appdb user=appuser sslmode=require"
5. Open the pgAdmin web UI at https://<PUBLIC_IP>/pgadmin4/ and log in with the admin email and password from the credentials file (the certificate is self-signed by default)

Never expose 5432 to the public internet; for production, replace the self-signed certificate with a CA-signed pair or issue one with the pre-installed certbot.