Skip to main content

Connect to Aiven for PostgreSQL® with LibreDB Studio

Use LibreDB Studio to connect to your Aiven for PostgreSQL® service.

LibreDB Studio is an open source SQL client that you host yourself and reach from a browser.

Prerequisites​

  • Access to the Aiven Console
  • At least one running Aiven for PostgreSQL service
  • LibreDB Studio running on your machine or your network

Run LibreDB Studio​

Start LibreDB Studio with Docker:

docker run -p 3000:3000 -v libredb:/app/data \
  -e STORAGE_PROVIDER=sqlite \
  -e STORAGE_ENCRYPTION_KEY='ENCRYPTION_KEY' \
  ghcr.io/libredb/libredb-studio:0.17.0

Replace ENCRYPTION_KEY with at least 32 characters, for example the output of openssl rand -base64 32. A shorter value stops the container from starting. Keep the quotes. Unquoted, the shell reads a $ or a backtick in the value, and the container starts on a different key.

Keep the value itself, somewhere other than the volume. Start the container on a different value later and every saved password becomes unreadable. The connection then fails with an access-denied error from the service, and the container log carries Stored connection secrets could not be decrypted. Without the variable, LibreDB Studio derives the key from a secret it writes into the same volume, so one snapshot of the volume carries both.

STORAGE_PROVIDER=sqlite keeps saved connections on the server, so they survive a new browser or a different machine. The volume keeps them when you replace the container.

The command runs in the foreground. On the first run it prints the admin email and a generated password, so read them there before you sign in. A later run reads the password from auth-bootstrap.json on the volume and does not print it again, so keep a copy. Deleting that file generates a new password, and without STORAGE_ENCRYPTION_KEY it also discards the key that protects saved passwords.

Add -e AUTH_COOKIE_SECURE=false when the browser reaches LibreDB Studio over plain HTTP at an address other than localhost, 127.0.0.1, or ::1. A network address such as http://192.168.1.10:3000 is one. Without the variable the sign-in request succeeds, the browser drops the session cookie, and the page returns to the sign-in form with no error.

The variable turns off the protection it names. On plain HTTP the session cookie and the service password cross the network in the clear. Put LibreDB Studio behind TLS anywhere but the machine you are sitting at.

Get the service URI from the Aiven Console​

  1. Log in to the Aiven Console and go to your organization > project > Aiven for PostgreSQL service.
  2. On the Overview page, go to the Connection information section.
  3. Copy the Service URI. It carries the host, port, user, password, database name, and sslmode=require.

Connect to the service URI from LibreDB Studio​

  1. Open LibreDB Studio, sign in, click Editor, then New connection.
  2. Click Paste URL, paste the service URI, and click Parse. LibreDB Studio names the connection after the database and fills in the host, port, user, password, and database name. It sets SSL Mode to require, because the URI carries sslmode=require.
  3. Click Test Connection, then click Establish Connection to save the connection.

The object browser lists your tables, views, materialized views, sequences, functions, procedures, and triggers. The SQL editor runs statements and EXPLAIN plans against the service.

Aiven gives each service its own port. Take the port from the Aiven Console rather than the 5432 the form starts with.

Typed in by hand instead of pasted, the URI leaves SSL Mode at disable. Aiven for PostgreSQL refuses that with no pg_hba.conf entry for host ..., no encryption, so set the mode to require or higher yourself.

Each saved connection keeps a pool of up to 10 server connections while it is active. A few saved connections take a noticeable share of a small plan. See Connection limits per plan for what your plan allows. Read Connections on the Overview page when other clients connect at the same time.

Verify the server certificate​

require encrypts the connection but accepts any certificate, and pasting one does not change that. To verify the certificate against the CA of your project:

  1. On the Overview page, go to the Connection information section and download CA Certificate as ca.pem.
  2. In the connection, expand SSL / TLS and set SSL Mode to verify-ca.
  3. Open ca.pem in a text editor and paste its contents into CA Certificate (PEM). LibreDB Studio reads the certificate text, not a path to ca.pem.
  4. Click Test Connection.

With nothing pasted, the connection fails with Failed to connect to PostgreSQL: self-signed certificate in certificate chain. Aiven signs with a CA of its own for each project.

verify-full and verify-system accept the same pasted certificate and are no stronger against an Aiven service. On PostgreSQL all three also verify the hostname. A name missing from the certificate fails with Hostname/IP does not match certificate's altnames.

Related pages