PostgreSQL Connection

Introduction

This document offers a detailed guide on how to create a PostgreSQL connection, providing step-by-step instructions.

Prerequisites

• You must first Whitelist our IPs - IP whitelisting is a security measure that permits network access exclusively to designated IP addresses.
  Whitelist IPs are only used for sources that have Internet access and a public IP address.
• Configure PostgreSQL for Rivery - Establish a user account specifically for Rivery and assign the necessary privileges to the user.

Configure PostgreSQL for Rivery

Before integrating PostgreSQL with Rivery, certain configuration steps need to be followed. Please note that the instructions provided here are for Standard extraction. If Change Data Capture extraction is required, please refer to the specific configuration documentation.

Create a User for Rivery

Execute the following SQL command to create a new user for Rivery, replacing <'myUsername'> with the desired username and <'password'> with an encrypted password:

CREATE USER <myUsername> WITH ENCRYPTED PASSWORD '<password>';

Grant Permissions to the User

Grant the new user access to the table or schema from which you want to extract data. Use the following commands, replacing <'database'>, <'schema'>, and <'table'> with the appropriate names:

• Grant connect privilege on the database:

  GRANT CONNECT ON DATABASE <database> TO <myUsername>;
  

• Grant usage privilege on the schema:

  GRANT USAGE ON SCHEMA <schema> TO <myUsername>;
  

• Grant select privilege on all tables in the schema:

  GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO <myUsername>;
  

  Please Note:

  If you wish to assign privileges to specific columns, you can utilize the following SQL script:

  GRANT SELECT (Column_1, Column_2, Column_3, Primary_Key, ...) ON <schema.table_name> TO <myUsername>;
  

  Please make sure to replace "Column_1" and the others with your chosen column names. If a Primary Key exists in the table, be sure to include it in the script.

• Set default privileges for future tables in the schema:

  ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT SELECT ON TABLES TO <myUsername>;
  

Establishing a Connection

1.Choose your Connection Name.
2. Enter Host.
3. Fill in the Port Number.
4. Enter your Database name.
5. Input your Username and Password.
6. Use the Test Connection function to see if your connection is up to the task.
If the connection succeeded, you can now use this connection in Rivery.

Disconnect Handling

There are two approaches to dealing with database disconnects:

• Optimistic - The optimistic approach to dealing with stale / closed connections is to let SQLAlchemy handle disconnects as they happen, at which point all connections in the pool are invalidated, which means they're assumed to be stale and will be refreshed upon the next checkout.
  In general, the pessimistic approach adds some overhead but provides a more stable connection, whereas the optimistic approach is faster but is recommended for databases that are less likely to be disconnected or restarted.

• Pessimistic - This approach entails issuing a test statement on the SQL connection at the beginning of each connection pool checkout to ensure that the database connection is still operational.

SSH Connection

Rivery has the capability to establish connections with on-premises servers by employing a hybrid approach for data processing. While we suggest using the SSH Tunneling Method, it is important to note that it is not mandatory.

Rivery can establish connections to on-prem servers by employing a hybrid data processing approach. While we suggest utilizing the SSH Tunneling Method, it's essential to know that its adoption is not obligatory.

To use the SSH Tunneling via Rivery, please configure your SSH Tunnel server in your network. This server will have the ability to get SSH connections from Rivery IPs, and will be able to connect to your database server via the internal IP or address. Rivery connects to that SSH server using a private key.

After you've configured SSH tunneling, you can set it up in the Rivery Connection:

SSL Connection

In order to establish a connection, certain PostgreSQL hosts may necessitate the use of an SSL certificate for validation and enabling.

Please Note:

• Utilizing an SSL connection is optional
• SSL connections are exclusively available when using PostgreSQL databases hosted on Amazon RDS/Aurora and Google Cloud SQL.

Custom File Zone

Setting up a Custom File Zone in Rivery is an optional feature, with the default option relying on the Managed File Zone provided by Rivery, which requires no setup.
The primary benefit of establishing a Custom File Zone lies in enabling organizations to guarantee that their data is stored within their designated file zones, instead of being stored in Rivery's Managed File Zone.

To use a Custom File Zone, please follow the instructions provided below:

1. Toggle the Custom File Zone to true.

2. You have the option to choose the preconfigured FileZone Connection by clicking on FileZone Connection (please be aware that only Custom File Zone in Amazon S3 is compatible).

3. Choose a Default Bucket from the drop-down list.

4. Use the Test Connection function to see if your connection is up to the task.
   If the connection was successful, click Save.