NOTE: This feature is available in VT Docs v6.1.0 and later

There are several ways to go passwordless when connecting to Postgres. Some of the ways are only available:

• to a particular OS (e.g. SSPI only available on Windows; Unix Sockets only available on Linux)
• when connecting to a local Postgres instance (e.g. Unix Sockets)
• when connecting to a cloud PaaS managed-Postgres instance (e.g. Azure Postgres; Amazon RDS Postgres)

After configuring for passwordless auth, you should set vtdocs.database.password= in the visiblethread.env file for your OS and restart the services.

The environment file can be found here: 

Linux
/etc/default/visiblethread.env

# restart vtdocs
systemctl restart visiblethread-docs

# restart postgres
systemctl restart postgresql

Windows
C:\Program Files\VisibleThread\vtdocs\visiblethread.env

# Open service, look for vtdocs-tomcat and restart the service
# Open service, look for vtdocs-postgres and restart the service

Windows

Our default installation on Windows runs Tomcat and Postgres on the same box. Our passwordless support for this configuration is to use SSPI (Windows Integrated Auth):

"C:\Program Files\VisibleThread\vtdocs\PostgreSQL\14\data\pg_hba.conf"

host all visiblethread 127.0.0.1/32 sspi map=SSPI
host all visiblethread ::1/128 sspi map=SSPI

"C:\Program Files\VisibleThread\vtdocs\PostgreSQL\14\data\pg_ident.conf"

SSPI "LOCAL SERVICE@NT AUTHORITY" visiblethread
SSPI "john.doe@your-azure-domain.com" visiblethread

Note: I’ve added an Azure AD user john.doe@your-azure-domain.com above so that that user can run scripts (backup/restore etc.). You can add as many users/service accounts as required.

Linux + Local Postgres

For our Ubuntu VM and RHEL deployments with Postgres running locally, we support passwordless connections by using Unix Sockets and ident authentication.

Out of the box, Postgres on Linux will accept local connections via a “unix socket” - this is Inter-Process Communication, not TCP sockets. The following Postgres config authenticates clients based on their OS username:

postgresql.conf (optional - this stops tcp listen, only accepts local socket comms):
    listen_addresses = ''
pg_hba.conf:
    local all visiblethread ident map=vtdocs
pg_ident.conf
    vtdocs visiblethread visiblethread
    vtdocs youruser visiblethread # For admin to run scripts e.g. backup

The following changes must be made to visiblethread.env:

vtdocs.database.host=localhost ##important that it's localhost!
vtdocs.database.socketFactory=org.newsclub.net.unix.AFUNIXSocketFactory$FactoryArg
vtdocs.database.socketFactoryArg=/var/run/postgresql/.s.PGSQL.5432
vtdocs.database.password=

Linux + External Postgres (SSL Certificate Auth)

For Linux deployments with an external database (non-RDS), we can use certificates for authentication.

1. Generate Certificates (on the PostgreSQL server)

mkdir ~/pg-certs && cd ~/pg-certs

# CA
openssl genrsa -out pg_root.key 4096
openssl req -x509 -new -nodes -key pg_root.key -sha256 -days 3650 -out pg_root.crt \
  -subj "/CN=VTDocs-CA"

# Server cert (with SAN for IP)
cat > server.cnf << EOF
[req]
distinguished_name = req_distinguished_name
req_extensions = v3_req

[req_distinguished_name]

[v3_req]
subjectAltName = IP:<POSTGRES_SERVER_IP>
EOF

openssl genrsa -out pg_server.key 4096
openssl req -new -key pg_server.key -out pg_server.csr -subj "/CN=<POSTGRES_SERVER_IP>" -config server.cnf
openssl x509 -req -in pg_server.csr -CA pg_root.crt -CAkey pg_root.key -CAcreateserial \
  -out pg_server.crt -days 3650 -sha256 -extensions v3_req -extfile server.cnf

# Client cert (CN must match db user)
openssl genrsa -out client.key 4096
openssl req -new -key pg_client.key -out pg_client.csr -subj "/CN=visiblethread"
openssl x509 -req -in pg_client.csr -CA root.crt -CAkey pg_root.key -CAcreateserial \
  -out client.crt -days 3650 -sha256

# Convert client key to PKCS8 DER format for Java
openssl pkcs8 -topk8 -inform PEM -outform DER -in pg_client.key -out pg_client.pk8 -nocrypt

2. PostgreSQL Server Configuration

Copy certs to a location Postgres can read:

sudo mkdir /var/lib/pgsql/certs
sudo cp pg_root.crt pg_server.crt pg_server.key /var/lib/pgsql/certs/
sudo chown -R postgres:postgres /var/lib/pgsql/certs
sudo chmod 600 /var/lib/pgsql/certs/pg_server.key
sudo chmod 644 /var/lib/pgsql/certs/pg_server.crt /var/lib/pgsql/certs/pg_root.crt

postgresql.conf:

ssl = on
ssl_cert_file = '/var/lib/pgsql/certs/server.crt'
ssl_key_file = '/var/lib/pgsql/certs/server.key'
ssl_ca_file = '/var/lib/pgsql/certs/root.crt'

pg_hba.conf (add before other host rules):

hostssl visiblethread visiblethread 0.0.0.0/0 cert map=visiblethread

pg_ident.conf:

visiblethread visiblethread visiblethread

3. VT Docs Server Configuration

Copy client certs (you will need to transfer these to the VT Docs server first):

sudo mkdir /opt/visiblethread/cert
sudo cp pg_root.crt pg_client.crt pg_client.pk8 /opt/visiblethread/cert/
sudo chown -R visiblethread:visiblethread /opt/visiblethread/cert
sudo chmod 600 /opt/visiblethread/cert/pg_client.pk8

/etc/default/visiblethread.env:

vtdocs.database.host=<POSTGRES_SERVER_IP>
vtdocs.database.sslmode=require
vtdocs.database.sslcert=/opt/visiblethread/cert/pg_client.crt
vtdocs.database.sslkey=/opt/visiblethread/cert/pg_client.pk8
vtdocs.database.sslrootcert=/opt/visiblethread/cert/pg_root.crt
vtdocs.database.password=
vtdocs.hostname=yourserver.example.com

Restart VT Docs:

sudo systemctl restart visiblethread-docs

AWS RDS Postgres

When a customer is deployed within AWS and using RDS Postgres, we support passwordless via the AWS Secret Manager.

Note: we support password rotation.

Modify visiblethread.env and supply the secret ARN:

vtdocs.aws.database.secret_arn=arn:aws:secretsmanager:eu-west-1:78xxxxxxxxxxx:secret:vtdocs-postgres-user-xxxx

To access psql without a password, use the below approach. The customer will need to update the scripts in VisibleThreadTools to use this approach to grab the password from AWS:

(PGPASSWORD=aws secretsmanager get-secret-value --secret-id arn:aws:secretsmanager:eu-west-1:78xxxxxxxxxxx:secret:test-rds-secret-visiblethread-hnQYRx | jq --raw-output '.SecretString' | jq -r .password && psql -h your.rds.host -U visiblethread)

Azure

See Migrate an application to use passwordless connections with Azure Database for PostgreSQL - Java on Azure for Azure setup.

Configure visiblethread.env:

vtdocs.database.authenticationPluginClassName=com.azure.identity.extensions.jdbc.postgresql.AzurePostgresqlAuthenticationPlugin
vtdocs.database.sslmode=require
vtdocs.database.username=john.doe@your-azure-domain.com

To access psql without a password, use the below approach. You will need to update the scripts in VisibleThreadTools to use this approach to grab the password/access token from Azure:

(export PGPASSWORD="$(az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken)" && psql "host=your.db.host port=5432 dbname=vtdocs user=john.doe@your-azure-domain.com sslmode=require")
#or
psql "host=your.db.host port=5432 dbname=postgres user=john.doe@your-azure-domain.com password='$(az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken)' sslmode=require"