Postico can execute a shell script before connecting to a server. The most common use case is when you use an identity management service like AWS IAM or Microsoft Entra ID. These services typically use temporary passwords or access tokens to connect to the PostgreSQL server. You can use a pre-connect shell script to generate these temporary passwords automatically whenever you want to connect to a server.
This feature is not available in the Mac App Store version of Postico.
The pre-connect shell script is executed just before Postico connects to the PostgreSQL server. If you are using an SSH tunnel, the script is executed after the SSH tunnel has been established.
The connection parameters configured in Postico are passed to the shell script via the environment. Postico uses the same environment variables as psql: $PGHOST, $PGUSER, $PGPASSWORD, etc.
In the simplest case, the script just prints the password. Postico will then try to use that password to connect to the server.
To override other connection parameters than the password, the script can instead print a JSON document. All PostgreSQL connection parameter key words such as host, user, password, dbname are supported.
Postico automatically detects if the output of the shell script is a valid JSON document.
If you have installed the AWS CLI tools, you can use this pre-connect shell script to automatically fetch an auth token for RDS:
Postico 2
aws rds generate-db-auth-token \
--profile default \
--region "${${PGHOST%.rds.amazonaws.com}##*.}" \
--hostname "$PGHOST" \
--port "$PGPORT" \
--username "$PGUSER"
If you have installed the AWS CLI tools, you can use this pre-connect shell script to automatically fetch credentials for Amazon Redshift:
aws redshift get-cluster-credentials \
--profile default \
--region "${${PGHOST%.redshift.amazonaws.com}##*.}" \
--db-user "$PGUSER" \
--cluster-identifier "${PGHOST%%.*}"
Use this pre-connect shell script to fetch a temporary password for Azure:
az account get-access-token --resource-type oss-rdbms
You can also use a pre-connect shell script to override PostgreSQL connection parameters or set connection parameters that Postico does not support natively.
For example, here is a script that will disable TLS certificate verification:
cat <<eof
{
"sslmode":"disable",
}
eof
Postico allows overriding the following keys in the JSON document: host, hostaddr, port, dbname, user, password, passfile, require_auth, channel_binding, connect_timeout, client_encoding, options, application_name, fallback_application_name, keepalives, keepalives_idle, keepalives_interval, keepalives_count, tcp_user_timeout, replication, gssencmode, sslmode, requiressl, sslcompression, sslcert, sslkey, sslpassword, sslcertmode, sslcrldir, sslsni, requirepeer, ssl_min_protocol_version, ssl_max_protocol_version, krbsrvname, gsslib, gssdelegation, service, target_session_attrs, load_balance_hosts.
For compatibility with the AWS CLI, Postico also accepts DbUser and DbPassword instead of user and password.
For compatibility with Entra ID, Postico also accepts accessToken instead of password.
If the shell script returns with a non-zero exit code, any messages printed to standard error (stderr) will be displayed. This means that error handling should work out of the box for many commands.
If you would like your script to display a friendly error message, just redirect it to stderr. A simple example would be something like this:
echo "An error occurred" >&2 exit 1
If your script runs successfully, any messages printed to stderr are ignored. Feel free to print detailed debug messages to stderr. They will be shown only if the script finishes with a non-zero exit code.
To see all environment variables available to the script, you can use the command below. But be careful: this will include the value of PGPASSWORD, so we recommend to use this only when debugging particularly thorny issues.
env >&2 exit 1
The shell script is executed with your default shell. If you haven't changed it, that is /bin/zsh. The shell is executed with the -l option (login shell), so the environment should be very similar to the environment you have when you enter commands in Terminal.app.
If you would like to execute the script with a different shell, you can include a shebang, for example #!/bin/bash. When the script starts with the characters #!, Postico stores the script in a temporary file and executes it. This allows you to write your pre-connect script in any language you have installed on your computer -- Bash, Python, Ruby, Perl, or even in Swift!
The pre-connect script is stored unencrypted, so please make sure not to include any secrets such as access keys.
The easiest way to store secrets securely is to use the password field. The value stored in the password field will be made available to the script in the $PGPASSWORD environment variable.
Unfortunately accessing the macOS keychain with the security command is currently not possible, because the shell script is executed by an XPC service. macOS does not allow XPC services to show dialogs required for accessing the keychain.