IBM Cloud Docs
Connecting with psql

Connecting with psql

You can access your IBM Cloud® Databases for EnterpriseDB database directly from its command line client, psql. You can use psql for direct interaction and monitoring of the data structures that are created within the database. It is also useful for testing and monitoring the queries and performance, installing and modifying scripts, and other management activities.

The admin user comes with the Databases for EnterpriseDB default role pg_monitor, allowing access to Databases for EnterpriseDB 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

Install the command line client for Databases for EnterpriseDB, psql. To use psql, the Databases for EnterpriseDB 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, or as a package from your operating system's package manager.

For more information about psql, see the PostgreSQL documentation.

psql Connection Strings

Connection strings are displayed in the Endpoints pane 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
Environment A list of keys or 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.

Connecting

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 plug-in, connect to your Databases for EnterpriseDB databases by using psql and 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 pane 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". It 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.