postgres-self-cert-ssl

A Node.js CLI tool to enable and maintain SSL for a local PostgreSQL server (macOS & Linux).
It generates a self-signed certificate, updates Postgres configs, and restarts the service using the right method for your platform.

Perfect for local dev environments where you want ssl=on without messing around manually.

Features

• 🔍 Detects running Postgres (socket or common TCP ports)
• ⚡ Starts Postgres automatically (supports brew services on macOS, systemctl on Linux)
• 📦 Auto-installs Postgres if missing (brew, apt, dnf, yum)
• 🛠 Auto-initializes Homebrew clusters if the data dir is empty
• 🔒 Generates or renews self-signed certs with proper permissions (server.key chmod 0600)
• ✍️ Updates postgresql.conf (ssl=on, ssl_cert_file, ssl_key_file)
• 📝 Optionally appends hostssl rules to pg_hba.conf
• 🔄 Restarts Postgres with the right controller (brew services restart preferred on macOS, pg_ctl fallback)
• 🗂 Backs up modified config files with .bak timestamps
• 🧪 Includes --check-only mode to view status without making changes

Install

Global CLI (recommended)

npm i -g postgres-self-cert-ssl

Now run:

postgres-self-cert-ssl --help

Local project devDependency

npm i -D postgres-self-cert-ssl

Then add to your package.json:

{
  "scripts": {
    "pg:ssl": "postgres-self-cert-ssl"
  }
}

Run with:

npm run pg:ssl -- --check-only

Usage

postgres-self-cert-ssl [options]

Options

--check-only            Print current SSL and cert status then exit
--force                 Regenerate certs even if valid
--renew-days=N          Renew if cert expires within N days (default 30)
--cert-days=N           Validity period for new certs in days (default 3650)
--update-hba            Add hostssl rules to pg_hba.conf (127.0.0.1 and ::1)
--no-restart            Skip restarting PostgreSQL after changes
--auto-start            Start PostgreSQL automatically if it is not running
--auto-install          Install PostgreSQL automatically if it is missing
--assume-yes            Noninteractive yes to prompts
--port-scan=CSV         Ports to scan, default 5432,5433,5434
--data-dir=PATH         Use this data dir when server is not reachable
--help, -h              Show usage

Environment

PGURL                   Optional libpq connection string passed to psql
                        Example: PGURL="host=localhost user=postgres"

Examples

Check SSL and cert status:

postgres-self-cert-ssl --check-only

Enable SSL and update pg_hba.conf:

postgres-self-cert-ssl --update-hba

Force fresh cert valid for one year:

postgres-self-cert-ssl --force --cert-days=365

Run noninteractive on a clean macOS dev machine:

postgres-self-cert-ssl --auto-install --auto-start --assume-yes

Operate offline against a known data dir:

postgres-self-cert-ssl --data-dir=/opt/homebrew/var/postgresql@14 --update-hba

What it changes

• Creates or renews server.key and server.crt in your Postgres data directory
• Sets server.key to 0600 permissions
• Updates postgresql.conf with:
  • ssl = on
  • ssl_cert_file = 'server.crt'
  • ssl_key_file = 'server.key'
• If --update-hba is passed, appends:
  • hostssl all all 127.0.0.1/32 md5
  • hostssl all all ::1/128 md5
• Restarts Postgres via:
  • brew services restart postgresql@XX (macOS)
  • systemctl restart postgresql or pg_ctl (Linux)
• Backups configs with .bak suffix and timestamp

macOS Homebrew notes

• Prefers brew services restart when available
• If a data dir exists but isn’t initialized, runs initdb for you
• If restart hangs, try manually:

  brew services restart postgresql@14

Linux notes

• Tries sudo systemctl start postgresql and common versioned unit names
• If your distro uses a different unit, restart manually and rerun --check-only

Security

• Self-signed certs are fine for local development
• For production, replace server.crt and server.key with your CA-issued certs
• Run with the user that owns your Postgres data dir (or sudo -u postgres on Linux)

Troubleshooting

• `psql: could not connect`
  Postgres isn’t running or uses a nonstandard port. Try `--auto-start` or set `PGURL`.

• `pg_ctl: server does not shut down` on macOS
  Launchd likely holds the service. Use `brew services restart postgresql@XX`.

• Stale `postmaster.pid`
  If the file exists but no postgres process is running, remove it: ```bash rm -f /path/to/pgdata/postmaster.pid ```

• Permission denied writing configs
  Run as the Postgres user or `sudo -u postgres`.

License

MIT