PostgreSQL

PostgreSQL, often referred to as Postgres, is an open-source relational database management system. It offers advanced features for data storage, retrieval, and management, making it a powerful and flexible choice for a wide range of applications, from small projects to large-scale enterprise systems.

Prerequisites

Authorize Connection to PostgreSQL

In PostgreSQL

Please keep in mind that this guide provides general instructions, and your individual setup might need a different approach or some extra steps. Always take your organization's security guidelines into account when you're making these changes. Additionally, it's a good idea to seek assistance from a database administrator or an IT professional to make sure everything is set up correctly.

Create a PostgreSQL User

1. Log into PostgreSQL shell.
2. Run a command to create a new user.

psql -U postgres

CREATE USER dataddo WITH PASSWORD '***';

Set Permissions

1. Log into PostgreSQL shell.
2. Grant CREATE, SELECT, UPDATE, INSERT, DELETE permissions for the schema you plan to use (the example below presumes that public schema is used).

GRANT CREATE, USAGE ON SCHEMA public TO dataddo;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO dataddo;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dataddo;

Allow Network Connection

For this destination, whitelisting IP addresses is not available in the database system. Instead, you can configure the network access at the firewall level. Here are the general steps:

1. Identify the firewall that's protecting your service. This could be a Windows or Linux firewall running on the server itself, or it could be a network firewall.
2. Open the firewall's configuration settings. This process will vary depending on the type of your firewall.
3. Look for the setting that allows you to define inbound rules or policies.
4. Create a new rule or policy that allows inbound traffic on the port that the service is using (the default is 5432) from the Dataddo IP addresses.
5. Save your changes and test the connection from Dataddo to make sure everything is working as intended.

For PostgreSQL, the ability to whitelist IP addresses isn't built into the database system. Instead, you can control network access at the firewall level. Here's a general process:

In Dataddo

1. In the Authorizers tab, click on Authorize New Service and select Universal PostgreSQL.
2. You will be asked to fill the following fields
   1. Server IP or Hostname: Public IP or hostname of your PostgreSQL Server.
   2. Database: Name of the database used for writing or reading data.
   3. Username: Username of your PostgreSQL user.
   4. Password: Password to your PostgreSQL user.
   5. Port: Port to connect to PostgreSQL. The default value is 5432.
   6. TLS/SSL Settings: Keep the value on PREFER, this will ensure the SSL connection when available. If you want to enforce SSL without a fallback to unencrypted connection, use REQUIRED.
3. [Optional] Configure the connection via SSH tunnel. To do so, please follow these steps.
4. Click on Save.

Create a New PostgreSQL Destination

Creating a Flow to PostgreSQL

Schema specification

While configuring the flow, it's essential to specify both the schema and the table name in the Table Name field. Make sure that you have the necessary permissions to write data to the specified schema.

Example:

• Target schema name: my_schema
• Target table name: my_table
• Table name in Dataddo: my_schema.my_table

If you don't specify schema and table name, Dataddo will use dbo as the default schema for writing.