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
:
> psql 'postgres://application:[email protected]:5432/postgres?sslmode=verify-full&sslrootcert=/path/to/Our_team.pem'
psql (15.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=>
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.
You may also see a similar error if you specify sslmode=verify-full
but don't
provide a path to your team certificate.