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

For our Ubuntu VM and RHEL deployments with Postgres running externally, we support passwordless connections by SSL certs.

postgresql.conf:

ssl = on
ssl_cert_file = '/tools/server_certs/server.crt'
ssl_ca_file='/tools/server_certs/root.crt'
ssl_key_file = '/tools/server_certs/server.key'

In visiblethread.env you can set any Postgres JDBC args by prepending with vtdocs.database. e.g.

vtdocs.database.sslmode=require

 

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"