Postgres connection is a native type where queries can be audited and the output redacted. It forwards a passwordless TCP connection locally.

💡

This type of connection accepts only MD5 or scram-sha-256 authentication methods.

The connection is TLS offloaded until it reaches the agent. However, once it reaches the agent, the connection is established without any encryption with the remote server. It is important to ensure that the network running the agent is reliable.

Features

The table below explain which features are available for this kind of connection.

Native - it's when a database client is connecting via protocol (.e.g: IDE, client libraries)

One Off - it's when a hoop client performs ad hoc executions (e.g: webapp, hoop cli)

Feature	Native	One Off	Description
TLS Termination Proxy	✅	✅	The local proxy terminates the connection with TLS, enabling the connection with the remote server to be TLS encrypted.
Audit	✅	✅	The gateway store and audit the queries being issued by the client
Data Masking (DLP)	✅	✅	A policy can be enabled to mask sensitive fields dynamically when performing queries in the database.
Credentials Offload	✅	✅	The user authenticates via SSO instead of using database credentials.
Interactive Access	✅	✅	Interactive access is available when using an IDE or connecting via a terminal to perform analysis exploration.

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. Make sure to url encode the password if it contain any special characters.
`DB`	env-var	The name of the database to connect (Required when using the connection via command line).
`SSLMODE`	env-var	How to connect via tls with the remote host, it defaults to `prefer`. See this document for more information. Available options are: `disable`, `prefer`, `require` and `verify-full`

shell
hoop admin create conn pg --type postgres -a <agent> \
  -e HOST=<host> \
  -e PORT=<port> \
  -e USER=<user> \
  -e PASS=<pass> \
  -e DB=<db> \
  -e SSLMODE=prefer

create the connection

Connect It

Open a new terminal and type

shell
hoop connect pg

plain text
connection: pg | session: 0ebc7492-94bf-498e-ac9c-430f2d5e89b0

--------------------postgres-credentials--------------------
      host=127.0.0.1 port=5433 user=noop password=noop
------------------------------------------------------------
ready to accept connections!

Connect to postgres using any IDE, example:

shell
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.

💡

Creating the postgres type connection allows connecting via IDE and via cli using the same connection.

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

shell
hoop admin create conn psql -a <agent> -- \
  psql -P pager=off -h $HOST -U$USER --port$PORT $DB

💡

The PGPASSWORD is set as an environment variable, so there is no need to include it in the command.

How to Use

Start an interactive session with psql client

shell
hoop connect psql

In the same connection, one-off process can be run as well

shell
hoop exec psql <<EOF
SLEEP 5;
SELECT NOW();
EOF

shell
hoop exec psql -f /tmp/myquery.sql
hoop exec psql -i 'SELECT NOW()'

TLS Certificates (psql only)

To connect with a postgres instance using TLS certificates, use the hoop admin command line to create or overwrite a connection

shell
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 <agent> --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.