SIA - PostgreSQL SSL/TLS Setup

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Announcements
Scheduled Maintenance: November 14–15 (starting Nov. 14 at 12:00 AM PST)
LIVEcommunity will temporarily operate in read-only mode as we prepare to welcome Idira!
Public content will remain accessible, but log-ins, posting and community interactions will be paused during this period. Thank you for your patience!
Secure Infrastructure Access
4 min read
L0 Member
No ratings

This document covers server-side TLS configuration only (what PostgreSQL needs to present a certificate to SIA).

Steps

1. Required Files on the PostgreSQL Server

FilePurposeRequired?Typical NameFormatPermissions (both OS)
server.crtServer certificate (public)Yesserver.crtPEMReadable by postgres user
server.keyServer private keyYesserver.keyPEM600 (owner read/write only)
root.crtTrusted root/CA certs for client auth (optional)Noroot.crtPEM (concatenated)Readable by postgres user
 
 

2. Default Storage Locations

Operating SystemDefault Data DirectoryTypical SSL Files Location
WindowsC:\Program Files\PostgreSQL\<version>\dataSame 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;"

3. Step-by-Step Configuration (Windows & Linux)

Step 1: Generate or Obtain Certificates

For testing/lab (both OS – run as root/postgres user on Linux, or admin on Windows):

Bash
 
# 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.

Step 2: Place Files in Data Directory

Windows:

  1. Stop PostgreSQL service (Services.msc → postgresql-x64-<version> → Stop)
  2. Copy files to C:\Program Files\PostgreSQL\<version>\data\
    • server.crt
    • server.key
  3. Set permissions on server.key:
    • Right-click → Properties → Security
    • Remove inheritance
    • Grant Read & Execute only to the PostgreSQL service account (NETWORK SERVICE or custom)
    • Deny to Everyone else

Linux:

  1. Stop PostgreSQL service
    Bash
     
    # RHEL/CentOS
    sudo systemctl stop postgresql-<version>
    
    # Debian/Ubuntu
    sudo systemctl stop postgresql
     
     
  2. Copy files (as root or postgres user):
    Bash
     
    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
     
     
  3. Set ownership & permissions:
    Bash
     
    sudo chown postgres:postgres /var/lib/pgsql/<version>/data/server.*
    sudo chmod 600 /var/lib/pgsql/<version>/data/server.key
     
     

Step 3: Edit postgresql.conf

Open postgresql.conf in the data directory (use vi, nano, or Notepad++).

Add/uncomment:

conf
 
# 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'
 
 

Step 4: Update pg_hba.conf (Authentication Rules)

In the same data directory, edit pg_hba.conf:

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 auth
 
 
  • hostssl forces TLS
  • SIA proxy connects from cloud IPs — use host lines with appropriate CIDR if you know them

Step 5: Restart PostgreSQL

Windows:

  • Services.msc → Start the PostgreSQL service

Linux:

Bash
 
# RHEL/CentOS
sudo systemctl restart postgresql-<version>

# Debian/Ubuntu
sudo systemctl restart postgresql
 
 

Step 5: Test TLS Locally on the Server

Both OS (run in terminal/Command Prompt):

Bash
 
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.

Step 6: Idira SIA Side (Strong Account / Target Verification)

  1. In Idira portal → Strong accounts → Databases → your strong account
  2. Go to Target set → edit target
  3. Under Certificate validation:
    • Enable "Verify with certificate"
    • Upload or select the root CA certificate (or full chain) that signs your PostgreSQL server.crt
    • Do not upload server.key or the leaf server.crt — only the trusted root/CA
  4. Save and test connectivity from the portal (if available)

Summary – Where Each Cert Lives

CertificateLocation / StorageUsed ByPurpose in SIA Flow
Full chain (from portal)Client machine (your PC) – any folderpgAdmin / psql clientVerify SIA proxy
server.crt + server.keyPostgreSQL server – data dirPostgreSQL server processPresented to SIA proxy
Root/CA cert for strong acctUploaded to Idira portal (TLS certificates page)SIA proxy / connectorSIA verifies PostgreSQL server identity

 

Rate this article:
  • 123 Views
  • 0 comments
  • 0 Likes
Article Dashboard
Version history
Last Updated:
‎11-05-2026 11:44 AM
Updated by: