PostgreSQL is an enterprise-grade, open source database management system. Many companies use PostgreSQL as a low-cost data warehousing solution to deliver efficient analytics and user insights.
RudderStack supports PostgreSQL as a source from which you can ingest data and route it to your desired downstream destinations.
RudderStack requires you to grant certain user permissions on your PostgreSQL warehouse to successfully access data from it.
Run the SQL queries listed in the following sections in the exact order to grant these permissions:
- Create a new user
RUDDERwith a password
<strong_unique_password>in PostgreSQL, as shown:
CREATE USER RUDDER WITH PASSWORD '<strong_unique_password>';
- Create a dedicated schema
CREATE SCHEMA "_rudderstack";
_rudderstack schema is used by RudderStack for storing the state of each data sync. This name should not be changed.
- Grant full access to the schema
_rudderstackfor the user
GRANT ALL ON SCHEMA "_rudderstack" TO RUDDER;
- Grant full access to all the objects in the schema
_rudderstackfor the user
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA "_rudderstack" TO RUDDER;
- To let the user
RUDDERlook up objects within the schema
<YOUR_SCHEMA>, run the command below.
GRANT USAGE ON SCHEMA "<YOUR_SCHEMA>" TO RUDDER;
- Grant access to the user
RUDDERto read data from the table
GRANT SELECT ON TABLE "<YOUR_SCHEMA>"."<YOUR_TABLE>" TO RUDDER;
<YOUR_TABLE> with the exact names of your PostgreSQL schema and table, respectively.
Run the following commands only if you're okay with RudderStack being able to access the data in all the current or future tables residing within your specified schema.
- To allow the user
RUDDERread the data from all the tables in the schema
GRANT SELECT ON ALL TABLES IN SCHEMA "<YOUR_SCHEMA>" TO RUDDER;
- To allow the user
RUDDERto read data from all the future tables in the schema
ALTER DEFAULT PRIVILEGES IN SCHEMA "<YOUR_SCHEMA>" GRANT SELECT ON TABLES TO RUDDER;
<YOUR_SCHEMA> with the exact name of your PostgreSQL schema.
To set up PostgreSQL as a source in RudderStack, follow these steps:
- Log into your RudderStack dashboard.
- From the left navigation bar, go to Source > New Source > Reverse ETL. Then, select PostgreSQL, as shown:
- Assign a name to your source.
- Choose the relevant option from Table or Model to use the source to sync data from either a warehouse table or a model.
For more information on the difference between the Table and Model options when creating a Reverse ETL source, refer to the FAQ section below.
- Enter the relevant connection details in the Connection Credentials, as shown:
- Host: Enter the host name of your PostgreSQL service.
- Database: Enter the database name in your PostgreSQL instance where the data is loaded.
- User: Specify the username which has the required read/write access to the above database.
- Password: Enter the password for the above user.
- Port: Enter the port number of your PostgreSQL service.
- SSL Mode: Choose the SSL mode through which RudderStack will connect to your PostgreSQL instance. RudderStack provides two options - disable and require. For more information on these options, refer to the FAQ section below.
If you've configured a PostgreSQL source before, you can select the existing credentials under the Use existing credentials option.
- Click on Continue. RudderStack will then verify and validate your credentials.
For more information on these validation steps, refer to the FAQ section.
- Once verified, click on Continue to proceed.
- Specify the Schedule Settings to schedule the data syncs from your PostgreSQL instance.
RudderStack lets you schedule data syncs for your Reverse ETL sources and specify how and when the syncs will run. For more information on the Basic, CRON, and Manual schedule types, refer to the Sync Schedule Settings guide.
- After specifying the schedule type and run settings, click on Continue to finish the setup.
PostgreSQL is now successfully configured as a source in your RudderStack dashboard. You can further connect this source to your preferred destination by clicking on Add Destination button, as shown:
If you have already configured a destination in RudderStack, choose the Use Existing Destinations option which will take you to the Schema tab in the source settings. To add a new destination from scratch, select the Create New Destination option which will take you to the destination configuration page.
While connecting a destination to your Reverse ETL source, you can use the default JSON mapping or the Visual Data Mapping feature.
Based on the option(Table/Model) you chose while setting up the Reverse ETL source, follow the relevant guide for detailed steps:
When setting up a PostgreSQL source, RudderStack provides the following two SSL options:
- disable: SSL mode is disabled when you select this option. Use it in cases where security is not an issue and you don't want any encryption overhead.
- require: When you select this option, your data is encrypted and sent to RudderStack. Use it in cases where security is important and you can deal with the resulting encryption overhead.
When setting up a Reverse ETL source, once you proceed after entering the connection credentials, you will see the following three validations under the Verifying Credentials option:
These options are explained below:
- Verifying Connection: This option indicates that RudderStack is trying to connect to the warehouse with the information specified in the connection credentials.
If this option gives an error, it means that one or more fields specified in the connection credentials are incorrect. Verify your credentials in this case.
- Able to List Schema: This option checks if RudderStack is able to fetch all the schema details using the provided credentials.
- Able to Access RudderStack Schema: This option implies that RudderStack is able to access the
_rudderstackschema you have created by successfully running all the commands in the User Permissions section.
If this option gives an error, verify if you have successfully created the
_rudderstack schema and given RudderStack the required permissions to access it. For more information, refer to the User Permissions sections.