Use psql to connect to ADB

Anton Vasilev

The psql tool is a standard PostgreSQL client application that you can use to connect to ADB. It is an interactive terminal that allows you to run SQL queries and view their results. To interact with ADB, you should connect to the master host. Segments are not intended to accept client connections.

IMPORTANT

The default username and default database name required for connection can be configured before you install ADB in the following ADB configuration parameters:

  • System user name

  • Name of default database

If not changed, the system user for the cluster hosts and the corresponding database role default to gpadmin, and the default database name is adb.

Local connection

Assuming you use the default settings:

  1. Connect to the ADB master host (via SSH, etc.) and log in under the default user gpadmin:

    $ sudo su - gpadmin

  2. Connect to the adb database:

    $ psql adb

As a result, the psql prompt is displayed with the psql version and the database name:

psql (9.4.26)
Type "help" for help.

adb=#

The hash sign (#) indicates that you are connected as a superuser. You can now type an SQL command and press Enter to execute it. For example, the following command creates a user that will be used for a remote connection:

CREATE USER alice WITH PASSWORD 'secure_password';

Remote connection

Make sure the master host accepts connections from your client hosts.

Allow remote connections

Add the address from which you want to connect to the pg_hba.conf file. You can use the ADCM web interface to do this:

  1. Open the configuration parameters of the ADB service.

  2. In the Custom pg_hba section field, add a line in the following format:

    host    <database_name>  <user_name>       <client_address>      <auth_method>

    For example:

    host    adb    alice    192.0.2.2/32    md5
    NOTE

    With the md5 authentication method, you’ll need to enter the user password. Refer to the Greengage DB documentation for descriptions of other authentication methods. Refer to Use MIT Kerberos KDC in ADB for an example of using the GSSAPI method.

  3. Click Save to save the configuration. Then click Action → Reconfigure to apply the settings.

After the Reconfigure action is run successfully, you can connect to the specified database from the specified host.

Optionally, check the added address on the master side: either open the $MASTER_DATA_DIRECTORY/pg_hba.conf file or query the pg_hba_file_rules system view, which gets information from this file. For example:

SELECT * FROM pg_hba_file_rules WHERE address='192.0.2.2';

The output should contain a connection rule for the specified client host address:

 line_number | type | database | user_name |  address   |     netmask     | auth_method | options | error
-------------+------+----------+-----------+------------+-----------------+-------------+---------+-------
          23 | host | {adb}    | {alice}   | 192.0.2.2 | 255.255.255.255 | md5         |         |
(1 row)

Connect remotely

  1. Pass the master host address, the database name, and the username to the psql command:

    $ psql -h <master_address> -U <user_name> -d <database_name>

    For example:

    $ psql -h 192.0.2.123 -U alice -d adb

    You will be prompted to enter the user password:

    Password for user alice:

  2. Enter the password.

When the connection is established, the psql prompt is displayed with the psql version of the client and PostgreSQL version on the server and the database name:

psql (9.4.26 (Homebrew), server 9.4.26)
Type "help" for help.

adb=>

=> indicates that you are connected as a regular user, not a superuser.

Found a mistake? Seleсt text and press Ctrl+Enter to report it