logo

Postgres

A Postgres connection is a native type, allowing for the auditing of queries and redaction of their output. It facilitates a local TCP connection without requiring a password.
💡
This type of connection accepts only MD5 or scram-sha-256 authentication methods.

Features

The table below outlines the features available for this type of connection.
  • Native - This refers to when a database client connects through a specific protocol, such as an IDE or client libraries.
  • One Off - This term refers to when a Hoop client performs ad hoc executions such as through a web app or 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
Required
Description
HOST
env-var
yes
The IP or Host of the Postgres server
USER
env-var
yes
The user to connect in the Postgres server
PASS
env-var
yes
The password to connect in the Postgres server. Make sure to url encode the password if it contain any special characters.
PORT
env-var
yes
The port of the Postgres server
DB
env-var
yes
The name of the database to connect (Required when using the connection via command line).
SSLMODE
env-var
no
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 database/postgres -a <agent> \ -e HOST=<host> \ -e PORT=<port> \ -e USER=<user> \ -e PASS=<pass> \ -e DB=<db> \ -e SSLMODE=prefer
create the connection

How to Use

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
In the same connection, one-off processes can be executed.
shell
hoop exec pg <<EOF SLEEP 5; SELECT NOW(); EOF
shell
hoop exec pg -f /tmp/myquery.sql hoop exec pg -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
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 pg --type database/postgres -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.

Powered by Notaku