Skip to main content

LakeBase with psql

Connecting to LakeBase from psql (with Onehouse Catalog)​

Use this when your LakeBase Cluster is configured with Onehouse Catalog. Authentication uses the username/password shown on the cluster's connection details page, and authorization is controlled by the Onehouse's EKS node role.

Step 1: Install psql​

Install the PostgreSQL client (psql). On macOS: brew install libpq. On Ubuntu/Debian: apt-get install postgresql-client.

Step 2: Get Connection Details​

From the Onehouse console, open the Clusters page, click into your LakeBase cluster, and copy the Endpoint URL, username, and password. Refer to Get Connection Details for more.

If VPN is not enabled

If VPN is not enabled, configure a bastion host to reach the cluster. Refer to Connect to your VPC.

Step 3: Connect and Run a Query​

psql "host=<cluster-host> port=5432 dbname=postgres user=<username> password=<password>"

Once connected, run a query:

SELECT 1;

Connecting to LakeBase from psql (with Glue Catalog)​

Use this when your LakeBase Cluster is configured with Glue Catalog (without Lake Formation). The connection pattern is identical to the Onehouse Catalog case above — authentication uses the username/password from the cluster page, and authorization is controlled by the Onehouse's EKS node role.

Follow the same Onehouse Catalog steps: connect using the host, username, and password from the cluster connection details page.

Connecting to LakeBase from psql (with Glue Catalog and Lake Formation)​

Use this when your LakeBase Cluster is configured with Glue Catalog and Lake Formation. psql (PostgreSQL 18+) authenticates via the OAuth 2.0 Device Authorization flow using your corporate identity provider (IdP), and authorization is enforced by Lake Formation.

Step 1: Install psql (PostgreSQL 18 or later)​

psql must be version 18 or later to support the built-in OAuth client. On macOS: brew install libpq. On Linux, install the PostgreSQL 18 client packages from the PostgreSQL APT/Yum repositories.

Step 2: Get Connection Details​

From the Onehouse console, open the Clusters page, click into your LakeBase cluster, and copy the Endpoint URL. Refer to Get Connection Details for more.

If VPN is not enabled

If VPN is not enabled, configure a bastion host to reach the cluster. Refer to Connect to your VPC.

Step 3: Connect and Run a Query​

Pass the IdP details via the oauth_issuer, oauth_client_id, and oauth_scope connection parameters. For the list of supported IdPs and values, see LakeBase JDBC Driver: Identity Provider Integration.

psql "host=<cluster-host> \
      port=5432 \
      dbname=postgres \
      user=<db_user> \
      oauth_issuer=https://<your-idp>.com \
      oauth_client_id=<your_client_id> \
      oauth_scope='openid profile email'"

When connecting, psql prints a device code and activation URL. Open the URL in a browser and enter the code to complete authentication.

Step 4: (Optional) Specify an IAM Role​

When using Lake Formation for access control, you can optionally specify an IAM role to assume for querying. If you don't specify a role, the first IAM role from your authentication token is used by default.

To specify a role explicitly, pass the options connection parameter:

psql "host=<cluster-host> \
      port=5432 \
      dbname=postgres \
      user=<db_user> \
      options='-c onehouse.lakebase_iam_role=arn:aws:iam::<account>:role/<role-name>' \
      oauth_issuer=https://<your-idp>.com \
      oauth_client_id=<your_client_id> \
      oauth_scope='openid profile email'"