Connecting a Postgres Database

CONTENTS

Home

Connect to Postgres

Here is what you need to connect:

  • Read-only user (Username field)
  • Add the bipp IP address to the pg_hba.conf file.
  • Database connection information
  • Credentials
bipp supports PostgreSQL version 9.x onwards. Earlier versions of PostgreSQL may be compatible but are not tested.

Create a Postgres read-only User

bipp recommends using a read-only user for connecting to your Postgres databases.

You must have the GRANT OPTION privilege and the privileges you are granting. For more details, refer to your Postgres documentation.

On a PostgresSQL command line, enter:

sudo -u postgres psql

Select the database:

\c pgdatabase;

Create a new role for the read-only user:

CREATE ROLE bipp_db_user LOGIN PASSWORD 'Secret@123';
Assign your own user name and strong password for bipp_db_user and Secret@123.

Grant the privileges for the new user to connect to your database:

GRANT CONNECT ON DATABASE pgdatabase TO bipp_db_user;
GRANT USAGE ON SCHEMA public TO bipp_db_user;

Grant SELECT on the tables you want accessible to the read-only user:

GRANT SELECT ON mytable TO bipp_db_user;

If you want to Grant SELECT on all the tables:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT ON TABLES TO bipp_db_user;

You can use pgAdmin if you prefer a GUI based tool to create the new user. Refer to the pgAdmin documentation for details.

Add bipp IP Address to pg_hba.conf File

PostgreSQL restricts connections to hosts and networks included in the pg_hba.conf file. If your database is not accessible over the internet, you need to add bipp’s IP address to this file to allow connectivity to your database.

Edit the pg_hba.config file, and add the bipp information using the format:

host databasename username ip_address/mask_length auth_method

  • databasename the Postgre database where you are granting permissions.
  • username the read-only user you created.
  • ip_address/mask_length the IP address. Use **34.121.142.220/**32 for bipp.
  • auth_method the authentication method. bipp recommends md5

Here is an example, where the username is bipp_db_user and the databasename is pgdatabase:

host pgdatabase bipp_db_user 34.121.142.220/32 md5

You may need to restart your PostgreSQL server for the changes to take effect.

For details on the pg_hba.conf file, refer the PostgreSQL documentation.

Use Direct Connection as the Connection type when you create the datasource.

Create a Postgres Datasource in bipp

  1. Select Datasources from the Home Navigation pane to open the Datasources page.

    Datasource Page

  2. Click New Datasource on the Datasources page.

    New Datasource

  3. Enter a Name. For example, TestPostgres.

  4. Select Postgres for SQL Dialect for your database. bipp creates and enforces the rules for the selected dialect.

    Datasource Postgres

  5. Enter the information for your Postgres database instance:

    • Host: name or IP address of the database host.
    • Port: port on the host for the connection.
    • Username: user name for the connection. bipp recommends using a read-only user account.
    • Password: password for the user.
    • DbName: name of the database for the connection.
    • Connection: connection type
      • Direct Connection: database directly available over internet or your bipp platform is deployed on-premise in a private subnet. If your database is behind a firewall, Add the bipp IP address 34.121.142.220 to your pg_hba.config file.
        • SslMode: select the mode for SSL connection.
          • disable: Do not use SSL. The SslCert, SslKey and SslRootCert fields are not used.
          • require: Always use SSL, skip certificate verification.
          • verify-ca: Always use SSL, verify the certificate is signed by a trusted CA.
          • verify-full: Always use SSL, verify the certificate is signed by a trusted CA, and the server host name matches the certificate.
        • SslCert: browse to the location of the PEM format file containing your X509 certificate. This certification identifies the Postgres database to clients during SSL connections.
        • SslKey: browse to the location of your SSL key.
        • SslRootCert: browse to the location of your SSL Root certificate.
      • Direct Secure Connection: secured encrypted TCP connection if your database is behind a firewall. Add the bipp IP address 34.123.9.91 to your pg_hba.config file. TLS is not supported in this mode. The encrypted data is transmitted to prevent in-transit eavesdropping. Direct Secure Connection will be deprecated soon.
      • SSH Tunnel: encrypted connection using Datasource Postgres SSH Port Forwarding. bipp supports both forward and reverse SSH tunnels. You can use the SshTunnelAuth to specify Key or Password for the authorization type. Here are the additional SSH fields:
        • SshTunnelHost: name or IP address of the SSH tunnel authentication host.
        • SshTunnelPort: port on the SSH tunnel authentication host for the connection.
        • SshTunnelUser: user name for SSH tunnel authentication.
        • Ssh TunnelAuth: select Key to use an SSH Private Key and optional SSH Passphrase, or select Password to use your SSH Password.

  6. Click Test to verify the datasource connectivity. If you have an error, fix the connection or credentials problem and click Test again.

  7. Click Create to add the datasource.

Congratulations! You have successfully created a Postgres datasource in bipp.

IN THIS PAGE

Sign up for a forever free account!