Contents x
    PostgreSQL
    • 3 Minutes to read
    • Dark
      Light
    Contents

    PostgreSQL

    • 3 Minutes to read
    • Dark
      Light
    Article summary

    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.

    DATADDO TIP

    Dataddo supports PostgreSQL as both a cloud-managed and an on-premises source or destination.

    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

    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

    1. On the Destinations page, click on the Create Destination button and select the destination from the list.
    2. Select your authorizer from the drop-down menu.
    3. Name your destination and click on Save.
    Need to authorize another connection?

    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

    1. Navigate to Flows and click on Create Flow.
    2. Click on Connect Your Data to add your source(s).
    3. Click on Connect Your Data Destination to add the destination.
    4. Choose the write mode and fill in the other required information.
    5. Check the Data Preview to see if your configuration is correct.
    6. 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. 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.

    Dataddo does not create schemas automatically. Make sure the target schema exists and that you have the necessary write permissions.

    Was this article helpful?
    What's Next