logo

Postgres

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
πŸ’‘
This type of connection accepts only MD5 or scram-sha-256 authentication methods.

Connect to postgres

Open a new terminal and type
plain text
hoop connect my-conn-pg
plain text
connection: 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 text
psql -h 127.0.0.1 -p 5433 mydb
Using DBeaver requires configuring the database connection as simple on Driver properties > preferQueryMode

psql 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 text
psql -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 text
hoop connect psql
In the same connection, one-off process can be run as well
plain text
hoop exec psql <<EOF SLEEP 5; SELECT NOW(); EOF
plain text
hoop 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 text
PGSSLMODE=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.

Powered by Notaku