Postgres
Postgres connection is a native type where queries can be audited and the output redacted. It forwards a passwordless TCP connection locally.
Connection Configuration
Name | Type | Description |
HOST | env-var | The IP or Host of the Postgres server |
PORT | env-var | The port of the Postgres server |
USER | env-var | The user to connect in the Postgres server |
PASS | env-var | The password to connect in the Postgres server |
Connect to postgres
Open a new terminal and type
plain texthoop connect my-conn-pg
plain textconnection: my-conn-pg | session: 0ebc7492-94bf-498e-ac9c-430f2d5e89b0 --------------------postgres-credentials-------------------- host=127.0.0.1 port=5433 user=noop password=noop ------------------------------------------------------------ ready to accept connections!
Use It
Connect to postgres using any IDE, example:
plain textpsql -h 127.0.0.1 -p 5433 mydb
Using DBeaver requires configuring the database connection as
simple
on Driver properties > preferQueryModepsql cli
An optional way to connect into postgres is using the
psql
client. It’s possible to create a interactive session or execute one-off commands.Connection Configuration
Name | Type | Description |
HOST | env-var | The IP or Host of the Postgres server |
PORT | env-var | The port of the Postgres server |
USER | env-var | The user to connect in the Postgres server |
PGPASSWORD | env-var | The password to connect in the Postgres server |
DB | env-var | The name of the database to connect into |
Connnection Command
plain textpsql -P pager=off -h $HOST -U$USER --port$PORT $DB
:::info NOTE The
PGPASSWORD
is mapped as an environment variable, thus there’s no need to use it in the command. :::How to Use
Start an interactive session with psql client
plain texthoop connect psql
In the same connection, one-off process can be run as well
plain texthoop exec psql <<EOF SLEEP 5; SELECT NOW(); EOF
plain texthoop exec psql -f /tmp/myquery.sql hoop exec psql -i 'SELECT NOW()'
TLS Certificates
To connect with a postgres instance using TLS certificates, use the hoop admin command line to create or overwrite a connection
plain textPGSSLMODE=verify-full PGSSLROOTCERT=$(cat ca.crt |base64) PGSSLKEY=$(cat client.key |base64) PGSSLCERT=$(cat client.crt |base64) PGUSER=client1 PGPASSWORD=1a2b3c4d PGPORT=5432 PGHOST=127.0.0.1 hoop admin create conn psql -a default --overwrite \ --env "b64-filesystem:PGSSLROOTCERT=$PGSSLROOTCERT" \ --env "b64-filesystem:PGSSLKEY=$PGSSLKEY" \ --env "b64-filesystem:PGSSLCERT=$PGSSLCERT" \ --env "envvar:PGSSLMODE=$PGSSLMODE" \ --env "envvar:PGUSER=$PGUSER" \ --env "envvar:PGPASSWORD=$PGPASSWORD" \ --env "envvar:PGPORT=$PGPORT" \ --env "envvar:PGHOST=$PGHOST" -- psql
The postgres environment variables will be available in the runtime execution, allowing the
psql
command line utility to connect in the postgres instance.- The
b64-filesystem
prefix type will accept the content input as base64 and save the decoded value in a temporary file. The value of the variable will contain the path of the temporary file.
- The
envvar
prefix type will make the value available as an environment variable.