Command line (psql)

Connecting from the command line with `psql`

The psql command line client is part of the standard PostgreSQL distribution, and is a powerful tool used for both interactive sessions and for scripting. For this walkthrough we assume you already have psql installed on your machine.

Find your Connection String

If you haven't created your Crunchy Bridge Team or Cluster, the Getting Started page will guide you through the process.

Once you've created the cluster, you'll use the connection string in order to connect via psql. A connection string looks like this:

postgres://user:password@host:port/database?setting=value

From the command line you can use the CLI command cb psql with the name of your cluster to connect directly, for example cb psql fancy-hippo-5000. You can also use the cb uri command to fetch the connection string for a cluster.

Alternatively, from a cluster’s details page on your dashboard you can find the connection string in the Connections tab. You can choose the database role and format for your connection string. The psql format includes the command itself, so that you can just click copy and paste it straight into your terminal.

Modifying the connection string

The connection string contains all the information necessary to connect to your PostgreSQL database. Sometimes you may need to change parts of the connection string to suit your needs.

You can choose a different role from the dashboard (or by using the --role argument to the cb uri command) and we will generate a connection string with the right username and password.

You can manually change any other part of the connection string as required: for example changing the database value to connect to a specific database on your server rather than the default database named postgres, or changing the port from 5432 to 5431 to connect via a pgbouncer Connection Pool instead.

SSL connections with certificate verification

Crunchy Bridge database connections are secured using SSL/TLS. Each team has its own self-signed root certificate. Connections made via the Crunchy Bridge CLI with cb psql will automatically use this team certificate to verify your server's certificate for every connection.

If you're connecting with psql directly, then for an added layer of security you can enable certificate verification to ensure that the database you are connecting to belongs to your team.

First, download a copy of your team certificate from the team settings page, and store this file somewhere that your psql client can access.
Next, add the parameters ?sslmode-verify-full&sslrootcert=/path/to/cert.pem to the end of your connection string:
- sslmode=verify-full means that psql will only try an SSL connection, verify that the server certificate is issued by a trusted CA and that the requested server host name matches that in the certificate.
- sslrootcert=/path/to/cert.pem means that we will trust only server certificates which belong to the team whose downloaded certificate we saved as /path/to/cert.pem.
Finally, when using a connection string with parameters on the command line we need to wrap the whole thing in single quotes so that special characters like ? and & aren't interpreted by the shell.

In the following example we are connecting to the default postgres database on our Crunchy Bridge cluster as the application user, and checking that this server has a valid certificate belonging to "Our Team", whose root certificate we saved in the file /path/to/Our_team.pem:

&gt; psql &#39;postgres://application:[email protected]:5432/postgres?sslmode=verify-full&amp;sslrootcert=/path/to/Our_team.pem&#39;

psql (15.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type &quot;help&quot; for help.

postgres=&gt;

Troubleshooting

Here are some common connection errors and suggested fixes:

psql: error: connection to server at "p.abcd1234.db.postgresbridge.com" (192.0.2.11), port 5432 failed: Operation timed out
Is the server running on that host and accepting TCP/IP connections?

Check that:

The hostname and port specified are correct.
The server is not currently suspended.
There are no firewall rules in place which would prevent you from connecting.

psql: error: connection to server at "p.abcd1234.db.postgresbridge.com" (192.0.2.11), port 5432 failed: root certificate file "/path/to/Our_team.pem" does not exist
Either provide the file or change sslmode to disable server certificate verification.

This error indicates that the path to your downloaded team certificate is incorrect. Make sure that you replace /path/to/Our_team.pem with the path to your team certificate, and that this file is on the same host which is running psql.

You may also see a similar error if you specify sslmode=verify-full but don't provide a path to your team certificate.

psql: error: connection to server at "p.abcd1234.db.postgresbridge.com" (192.0.2.11), port 5432 failed: could not read root certificate file "/path/to/Our_team.pem": Permission denied

Check that the user running psql has permission to access the team certifcate file.

psql: error: connection to server at "p.abcd1234.db.postgresbridge.com" (192.0.2.11), port 5432 failed: SSL error: certificate verify failed

Check that you are using the correct certificate. For example, check that the server you're trying to connect to belongs to the team whose certificate file you are using.