IBM Cloud Docs
Connecting with psql

Connecting with psql

Use psql for direct interaction and monitoring of the data structures that are created within the database. psql is also useful for testing and monitoring queries and performance, installing and modifying scripts, and other management activities.

The admin user comes with the PostgreSQL default role pg_monitor, that allows access to PostgreSQL monitoring views and functions. By default, the admin user does not have permissions on objects that are created by other users.

You must set the admin password before you use it to connect to the database. For more information, see the Setting the Admin Password page.

Installing psql

To use psql, the PostgreSQL client tools need to be installed on the local system. They can be installed with the full PostgreSQL package that is provided from postgresql.org, as a package from your operating system's package manager.

For more information about psql, see the PostgreSQL documentation.

Most instructions for installing the PostgreSQL tools assume you want the database installed too. It's a reasonable assumption if you're dealing with users who don't have access to PostgreSQL in the cloud or on a remote server.

Here are steps for installing psql as a stand-alone tool.

Installing psql on macOS with Homebrew

We recommend Homebrew as a package manager for macOS. With Homebrew, you are able to install numerous applications, usually with the programs available in /usr/local/bin. Homebrew's package for the PostgreSQL client tools is the libpq package. Brew makes it easy to install:

brew install libpq

There's a small catch though: libpq won't install itself in the /usr/local/bin directory. To make that happen, you need to run:

brew link --force libpq

Which will symlink (a file that points to another file or folder) all the tools, not just libpq, into the /usr/local/bin directory.

psql Connection Strings

Connection strings are displayed in the Endpoints panel of your deployment's Overview, and can also be retrieved from the Cloud Databases CLI plug-in, and the API.

The information that you need to make a connection with psql is in the "cli" section of your connection strings. The table contains a breakdown for reference.

Table 1. psql/cli connection information
Field Name Index Description
Bin The recommended binary to create a connection; in this case it is psql.
Composed A formatted command to establish a connection to your deployment. The command combines the Bin executable, Environment variable settings, and uses Arguments as command-line parameters.
Environment A list of key/values you set as environment variables.
Arguments 0... The information that is passed as arguments to the command shown in the Bin field.
Certificate Base64 A self-signed certificate that is used to confirm that an application is connecting to the appropriate server. It is base64 encoded.
Certificate Name The allocated name for the self-signed certificate.
Type The type of package that uses this connection information; in this case cli.
  • 0... indicates that there might be one or more of these entries in an array.

Creating a command-line client connection

Before creating a command-line client connection, ensure that you have set the Admin password for your deployment.

The ibmcloud cdb deployment-connections command handles everything that is involved in creating a command-line client connection. For example, to connect to a deployment named "example-postgres", use the following command:

ibmcloud cdb deployment-connections example-postgres --start

or

ibmcloud cdb cxn example-postgres -s

The command prompts for the admin password and then runs the psql command-line client to connect to the database.

If you have not installed the Cloud Databases CLI plug-in, connect to your PostgreSQL databases using psql by giving it the "composed" connection string. It provides environment variables PGPASSWORD and PGSSLROOTCERT. Set PGPASSWORD to the admin's password and PGSSLROOTCERT to the path or file name for the self-signed certificate.

PGPASSWORD=$PASSWORD PGSSLROOTCERT=0b22f14b-7ba2-11e8-b8e9-568642342d40 psql 'host=4a8148fa-3806-4f9c-b3fc-6467f11b13bd.8f7bfd7f3faa4218aec56e069eb46187.databases.appdomain.cloud port=32325 dbname=ibmclouddb user=admin sslmode=verify-full'

Using the self-signed certificate

  1. Copy the certificate information from the Endpoints panel or the Base64 field of the connection information.
  2. If needed, decode the Base64 string into text.
  3. Save the certificate to a file. (You can use the Name that is provided or your own file name).
  4. Provide the path to the certificate to the ROOTCERT environment variable.

You can display the decoded certificate for your deployment with the CLI plug-in with the command:

ibmcloud cdb deployment-cacert "your-service-name"

The command decodes the base64 into text. Copy and save the command's output to a file and provide the file's path to the ROOTCERT environment variable.

Another option is to add &sslrootcert=/path/to/cert to your connection string, for example:

postgres://$USERNAME:$PASSWORD@6eb96148-90bc-49a0-a5a4-dc2b53334653.btdl8mld0r95fevivv30.databases.appdomain.cloud:32109/ibmclouddb?sslmode=verify-full&sslrootcert=/path/to/cert