PostgreSQL is the most efficient general-purpose database for creating Data Warehouses and Data Lakes due to its efficiency in complex joins and high availability during insertions of high volumes of information.
SSH pipeline differs from traditional pipeline in that it connects to the database through an intermediate server, also called a tunnel or bastion.
3) Fill in your database information as shown below:
Name: This is the internal description of your destination on the Kondado platform
Server Address: The address of the intermediary server that will access your database
Server port: The passthru server port that will access your database
Server user: The passthru server user who will access database
Server PEM Key: The text of the file with the PEM key for passthru server access
Database address: The server address of your database
Database port: The port of your database
Database User: The username Kondado will use to enter data into your database. This user must have DROP, INSERT, CREATE, ALTER, SELECT and DELETE permissions
Database Password: The database user's password
Database: the name of the database
Schema: inform a schema or leave the default “public” [1]
Click save and your new destination will be created!
[1] Kondado will try to create the schema with the value informed in the “schema” parameter and if this is not possible (for example, due to lack of permission), the public schema will be used. Even if the schema already exists, Kondado must have permission to create schemas (“GRANT CREATE ON DATABASE db TO user”) if you do not want to use the public schema. For existing schemas, it is also important that the user registered at Kondado has permission to use the schema (“GRANT USAGE ON SCHEMA schema_name TO user;”)
Add a PostgreSQL SSH destination on Kondado
Configure a PostgreSQL destination that connects through an SSH tunnel (bastion server) on the Kondado platform.
Prepare network access and permissions
Ensure your database user and server allow access from Kondado's IPs, and enable the intermediary SSH server port. The database user needs DROP, INSERT, CREATE, ALTER, SELECT and DELETE permissions. Review security and IP requirements before proceeding.
Log in and start creating the destination
Log in to the Kondado platform, navigate to the add destinations page, click "New Destination", and select the PostgreSQL SSH destination from the available options.
Configure SSH tunnel (bastion) settings
Enter the intermediary server details: Server Address, Server port, Server user, and paste the Server PEM Key text for SSH authentication to the tunnel server.
Enter database connection details
Fill in the Database address, Database port, Database User, Database Password, Database name, and Schema (default is "public"). The schema requires proper GRANT permissions as noted in the article.
Save and verify the destination
Click save to create your new PostgreSQL SSH destination. Kondado will attempt to create the specified schema; if permissions are insufficient, it falls back to the public schema.
