- Access exclusive content
- Connect with peers
- Share your expertise
- Find support resources
This document covers server-side TLS configuration only (what PostgreSQL needs to present a certificate to SIA).
| File | Purpose | Required? | Typical Name | Format | Permissions (both OS) |
|---|---|---|---|---|---|
| server.crt | Server certificate (public) | Yes | server.crt | PEM | Readable by postgres user |
| server.key | Server private key | Yes | server.key | PEM | 600 (owner read/write only) |
| root.crt | Trusted root/CA certs for client auth (optional) | No | root.crt | PEM (concatenated) | Readable by postgres user |
| Operating System | Default Data Directory | Typical SSL Files Location |
|---|---|---|
| Windows | C:\Program Files\PostgreSQL\<version>\data | Same as data directory |
| Linux | /var/lib/pgsql/<version>/data (RHEL/CentOS) /var/lib/postgresql/<version>/main (Debian/Ubuntu) | Same as data directory |
To confirm exact data directory on any system: psql -U postgres -c "SHOW data_directory;"
For testing/lab (both OS – run as root/postgres user on Linux, or admin on Windows):
# Self-signed example (replace CN with your server's FQDN/IP)
openssl req -new -x509 -days 365 -nodes -text \
-out server.crt \
-keyout server.key \
-subj "/CN=target01.cyberarklab.local" \
-addext "subjectAltName = DNS:target01.cyberarklab.local,IP:192.168.x.x"
For production: Use a certificate signed by a trusted CA (internal PKI or public CA) with correct CN/SAN.
Windows:
Linux:
# RHEL/CentOS
sudo systemctl stop postgresql-<version>
# Debian/Ubuntu
sudo systemctl stop postgresql
sudo cp server.crt server.key /var/lib/pgsql/<version>/data/ # RHEL/CentOS
sudo cp server.crt server.key /var/lib/postgresql/<version>/main/ # Debian/Ubuntu
sudo chown postgres:postgres /var/lib/pgsql/<version>/data/server.*
sudo chmod 600 /var/lib/pgsql/<version>/data/server.key
Open postgresql.conf in the data directory (use vi, nano, or Notepad++).
Add/uncomment:
# Both Windows and Linux
ssl = on # Required for TLS
ssl_cert_file = 'server.crt' # Relative to data directory
ssl_key_file = 'server.key' # Relative to data directory
# Optional – client certificate verification (rare with SIA)
# ssl_ca_file = 'root.crt'
# ssl_crl_file = 'root.crl'
# Recommended security settings (both OS)
ssl_prefer_server_ciphers = on
ssl_ciphers = 'HIGH:!aNULL'In the same data directory, edit pg_hba.conf:
# Local admin/testing (both OS)
hostssl all all 127.0.0.1/32 scram-sha-256
hostssl all all ::1/128 scram-sha-256
# Allow SIA proxy connections – replace with actual SIA connector IP range if known
# For testing: allow all (tighten in production)
hostssl all all 0.0.0.0/0 scram-sha-256 # or cert if client cert authWindows:
Linux:
# RHEL/CentOS
sudo systemctl restart postgresql-<version>
# Debian/Ubuntu
sudo systemctl restart postgresql
Both OS (run in terminal/Command Prompt):
psql "host=localhost port=5432 dbname=postgres user=postgres sslmode=require"
Or in pgAdmin on the server: create a local server entry with sslmode=require.
| Certificate | Location / Storage | Used By | Purpose in SIA Flow |
|---|---|---|---|
| Full chain (from portal) | Client machine (your PC) – any folder | pgAdmin / psql client | Verify SIA proxy |
| server.crt + server.key | PostgreSQL server – data dir | PostgreSQL server process | Presented to SIA proxy |
| Root/CA cert for strong acct | Uploaded to Idira portal (TLS certificates page) | SIA proxy / connector | SIA verifies PostgreSQL server identity |