- 3 Minutes to read
- DarkLight
PostgreSQL
- 3 Minutes to read
- DarkLight
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
- You have a running instance of PostgreSQL with public IP or hostname.
- You have configured a PostgreSQL user with
CREATE
,SELECT
,UPDATE
,INSERT
,DELETE
permissions. - You have configured your network firewall to allow incoming connection from Dataddo IPs.
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
- Log into PostgreSQL shell.
- Run a command to create a new user.
psql -U postgres
CREATE USER dataddo WITH PASSWORD '***';
Set Permissions
- Log into PostgreSQL shell.
- 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:
- 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.
- Open the firewall's configuration settings. This process will vary depending on the type of your firewall.
- Look for the setting that allows you to define inbound rules or policies.
- 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.
- 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
- In the Authorizers tab, click on Authorize New Service and select Universal PostgreSQL.
- You will be asked to fill the following fields
- Server IP or Hostname: Public IP or hostname of your PostgreSQL Server.
- Database: Name of the database you will use for writing or reading the data.
- Username: Username of your PostgreSQL user.
- Password: Password to your PostgreSQL user.
- Port: Port to connect to PostgreSQL. The default value is 5432.
- 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.
- [Optional] Configure the connection via SSH tunnel. To do so, please follow these steps.
- Click on Save.
Create a New PostgreSQL Destination
- On the Destinations page, click on the Create Destination button and select the destination from the list.
- Select your authorizer from the drop-down menu.
- Name your destination and click on Save.
Click on Add new Account in drop-down menu during authorizer selection and follow the on-screen prompts. You can also go to the Authorizers tab and click on Add New Service.
Creating a Flow to PostgreSQL
- Navigate to Flows and click on Create Flow.
- Click on Connect Your Data to add your source(s).
- Click on Connect Your Data Destination to add the destination.
- Choose the write mode and fill in the other required information.
- Check the Data Preview to see if your configuration is correct.
- Name your flow and click on Create Flow to finish the setup.
Schema specification
While configuring the flow, it's essential to specify both the schema and the table name in the Table Name field. If the schema you are targeting is my_schema
, and the table is my_table
, then you should input my_schema.my_table
as the table name. Failing to do so will cause Dataddo to use public as the default schema for writing. Ensure that you have the appropriate permissions to write to the specified schema.
Dataddo does not automatically create schemas. Ensure that the schema you intend to write to already exists and that you have the necessary write permissions for it.