Passwordless PostgreSQL SSL Authentication from DataGrip

1. Confirm PostgreSQL configuration paths

Connect to the Linux server using your normal Linux account:

ssh danv@172.25.0.16

Open PostgreSQL as the local postgres operating-system account:

sudo -u postgres psql -X -d postgres

Run:

SELECT
    current_setting('config_file') AS config_file
,   current_setting('hba_file') AS hba_file
,   current_setting('data_directory') AS data_directory
,   version();

For this server, the returned paths were:

/etc/postgresql/16/main/postgresql.conf
/etc/postgresql/16/main/pg_hba.conf
/var/lib/postgresql/16/main

Exit psql:

\q

2. Create certificates on Linux

Create a protected workspace

sudo -i
mkdir -p /root/postgres-cert-setup
chmod 700 /root/postgres-cert-setup
cd /root/postgres-cert-setup

Create the private certificate authority

openssl genpkey \
    -algorithm RSA \
    -pkeyopt rsa_keygen_bits:4096 \
    -out ca.key

chmod 600 ca.key

openssl req \
    -x509 \
    -new \
    -sha256 \
    -days 3650 \
    -key ca.key \
    -out ca.crt \
    -subj "/CN=Internal PostgreSQL CA"

Create the server certificate configuration

vim server-cert.cnf

Enter:

[req]
prompt = no
distinguished_name = distinguished_name
req_extensions = requested_extensions

[distinguished_name]
CN = internalsystemsdocker

[requested_extensions]
subjectAltName = @subject_alternative_names

[subject_alternative_names]
DNS.1 = internalsystemsdocker
IP.1 = 172.25.0.16

Create and sign the server certificate

openssl genpkey \
    -algorithm RSA \
    -pkeyopt rsa_keygen_bits:3072 \
    -out server.key

openssl req \
    -new \
    -sha256 \
    -key server.key \
    -out server.csr \
    -config server-cert.cnf

openssl x509 \
    -req \
    -sha256 \
    -days 825 \
    -in server.csr \
    -CA ca.crt \
    -CAkey ca.key \
    -CAcreateserial \
    -out server.crt \
    -extfile server-cert.cnf \
    -extensions requested_extensions

Create the client certificate for PostgreSQL role qs4rw

openssl genpkey \
    -algorithm RSA \
    -pkeyopt rsa_keygen_bits:3072 \
    -out qs4rw.key

openssl req \
    -new \
    -sha256 \
    -key qs4rw.key \
    -out qs4rw.csr \
    -subj "/CN=qs4rw"

Create the client extension file:

vim client-cert.cnf

Enter:

basicConstraints = critical,CA:FALSE
keyUsage = critical,digitalSignature,keyEncipherment
extendedKeyUsage = clientAuth

Sign the client certificate:

openssl x509 \
    -req \
    -sha256 \
    -days 825 \
    -in qs4rw.csr \
    -CA ca.crt \
    -CAkey ca.key \
    -CAcreateserial \
    -out qs4rw.crt \
    -extfile client-cert.cnf

Verify the certificates

openssl verify -CAfile ca.crt server.crt
openssl verify -CAfile ca.crt qs4rw.crt

openssl x509 \
    -in server.crt \
    -noout \
    -subject \
    -ext subjectAltName

openssl x509 \
    -in qs4rw.crt \
    -noout \
    -subject \
    -ext extendedKeyUsage

Expected highlights:

server.crt: OK
qs4rw.crt: OK
IP Address:172.25.0.16
subject=CN = qs4rw
TLS Web Client Authentication

3. Install certificates and enable PostgreSQL SSL

Install the server files

install \
    -o postgres \
    -g postgres \
    -m 600 \
    server.key \
    /etc/postgresql/16/main/server.key

install \
    -o postgres \
    -g postgres \
    -m 644 \
    server.crt \
    /etc/postgresql/16/main/server.crt

install \
    -o postgres \
    -g postgres \
    -m 644 \
    ca.crt \
    /etc/postgresql/16/main/root.crt

Back up and edit postgresql.conf

cp \
    /etc/postgresql/16/main/postgresql.conf \
    /etc/postgresql/16/main/postgresql.conf.before-ssl

vim /etc/postgresql/16/main/postgresql.conf

Set these four values:

ssl = on
ssl_ca_file = '/etc/postgresql/16/main/root.crt'
ssl_cert_file = '/etc/postgresql/16/main/server.crt'
ssl_key_file = '/etc/postgresql/16/main/server.key'

Confirm the effective paths before restarting

sudo -u postgres /usr/lib/postgresql/16/bin/postgres \
    -D /var/lib/postgresql/16/main \
    -C ssl_cert_file \
    -c config_file=/etc/postgresql/16/main/postgresql.conf

sudo -u postgres /usr/lib/postgresql/16/bin/postgres \
    -D /var/lib/postgresql/16/main \
    -C ssl_key_file \
    -c config_file=/etc/postgresql/16/main/postgresql.conf

sudo -u postgres /usr/lib/postgresql/16/bin/postgres \
    -D /var/lib/postgresql/16/main \
    -C ssl_ca_file \
    -c config_file=/etc/postgresql/16/main/postgresql.conf

Expected:

/etc/postgresql/16/main/server.crt
/etc/postgresql/16/main/server.key
/etc/postgresql/16/main/root.crt

Restart the real PostgreSQL cluster

systemctl restart postgresql@16-main
systemctl --no-pager --full status postgresql@16-main

The service must show:

Active: active (running)

4. Configure certificate authentication in pg_hba.conf

Back up the file:

cp \
    /etc/postgresql/16/main/pg_hba.conf \
    /etc/postgresql/16/main/pg_hba.conf.before-cert

Edit it:

vim /etc/postgresql/16/main/pg_hba.conf

Add this line above any broader rule that could match the same user and client address:

hostssl    all    qs4rw    172.25.2.38/32    cert

Example ordering:

# Certificate authentication for Daniel's workstation
hostssl    all    qs4rw    172.25.2.38/32    cert

# Existing general password rules below
host       all    all      172.25.0.0/16     scram-sha-256

Validate the rule

sudo -u postgres psql -X -d postgres -c "
SELECT
    line_number
,   type
,   database
,   user_name
,   address
,   auth_method
,   error
FROM pg_hba_file_rules
WHERE error IS NOT NULL
   OR user_name @> ARRAY['qs4rw'];
"

You should see:

type:         hostssl
address:      172.25.2.38
auth_method:  cert
error:        blank

Reload PostgreSQL after changing only pg_hba.conf:

systemctl reload postgresql@16-main

5. Copy the client certificate files to Windows

Stage only the three client-side files in the Linux user's home directory:

install \
    -o danv \
    -g danv \
    -m 644 \
    /root/postgres-cert-setup/ca.crt \
    /home/danv/ca.crt

install \
    -o danv \
    -g danv \
    -m 644 \
    /root/postgres-cert-setup/qs4rw.crt \
    /home/danv/qs4rw.crt

install \
    -o danv \
    -g danv \
    -m 600 \
    /root/postgres-cert-setup/qs4rw.key \
    /home/danv/qs4rw.key

On Windows PowerShell:

New-Item `
    -ItemType Directory `
    -Force `
    -Path "$HOME\.postgresql"

scp danv@172.25.0.16:/home/danv/ca.crt `
    "$HOME\.postgresql\ca.crt"

scp danv@172.25.0.16:/home/danv/qs4rw.crt `
    "$HOME\.postgresql\qs4rw.crt"

scp danv@172.25.0.16:/home/danv/qs4rw.key `
    "$HOME\.postgresql\qs4rw.key"

After confirming the files exist on Windows, remove the temporary Linux copies:

rm \
    /home/danv/ca.crt \
    /home/danv/qs4rw.crt \
    /home/danv/qs4rw.key

The Windows paths should be:

C:\Users\dan.vandenbosch\.postgresql\ca.crt
C:\Users\dan.vandenbosch\.postgresql\qs4rw.crt
C:\Users\dan.vandenbosch\.postgresql\qs4rw.key

6. Configure DataGrip

General tab

SSH/SSL tab

Click Test Connection.

7. Verify that SSL and certificate authentication are in use

After DataGrip connects, run:

SELECT
    current_user
,   inet_client_addr()
,   ssl
,   version
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();

Expected highlights:

current_user = qs4rw
inet_client_addr = 172.25.2.38
ssl = true

8. Troubleshooting

Error: “The server requested SCRAM-based authentication, but no password was provided.”

This means PostgreSQL matched a password rule instead of the certificate rule.

• Move the hostssl ... qs4rw ... cert rule above broader SCRAM rules.
• Confirm the client address is 172.25.2.38.
• Disable the DataGrip SSH tunnel.
• Reload PostgreSQL after editing pg_hba.conf.

Inspect rule ordering:

sudo -u postgres psql -X -d postgres -c "
SELECT
    line_number
,   type
,   database
,   user_name
,   address
,   auth_method
FROM pg_hba_file_rules
WHERE type IN
(
    'host'
,   'hostssl'
)
ORDER BY line_number;
"

PostgreSQL fails to start after enabling SSL

Check the actual cluster status and journal:

systemctl --no-pager --full status postgresql@16-main

journalctl \
    -u postgresql@16-main \
    --no-pager \
    -n 50

Common causes:

• ssl_cert_file accidentally points to a private key.
• ssl_key_file points to the wrong key.
• The certificate and key do not match.
• The key permissions are too open.

Check file ownership and permissions:

ls -l \
    /etc/postgresql/16/main/server.crt \
    /etc/postgresql/16/main/server.key \
    /etc/postgresql/16/main/root.crt

Expected:

-rw-r--r-- postgres postgres server.crt
-rw------- postgres postgres server.key
-rw-r--r-- postgres postgres root.crt

Confirm the certificate and key match

openssl x509 \
    -noout \
    -modulus \
    -in /etc/postgresql/16/main/server.crt \
    | openssl sha256

openssl rsa \
    -noout \
    -modulus \
    -in /etc/postgresql/16/main/server.key \
    | openssl sha256

The two SHA-256 values must be identical.

Final checklist

• PostgreSQL cluster is active (running)
• Server certificate contains IP SAN 172.25.0.16
• Client certificate CN is exactly qs4rw
• pg_hba.conf certificate rule appears before SCRAM rules
• DataGrip SSH tunnel is disabled
• DataGrip SSL is enabled
• CA, client certificate, and client key paths are correct
• PostgreSQL password field is blank
• Test query returns ssl = true