How to Connect to a Postgres Database using a Bastion?

This topic was automatically generated from Slack. You can find the original thread here.

Hey how would you go about connecting to a postgres database that you need to use a bastion to connect via?

To connect to a PostgreSQL database through a bastion host from a Pipedream workflow, you would typically use an SSH tunnel to securely forward traffic from your local machine (or the environment where the workflow is running) to the database server via the bastion host. However, setting up an SSH tunnel directly within a Pipedream workflow is not supported due to the limitations of the execution environment and the nature of serverless platforms. Instead, you can consider the following alternative approaches: 1. Direct Connection: If your PostgreSQL database allows direct connections (not recommended for production environments due to security concerns), you could use the database’s public endpoint and ensure that the security groups or firewall rules allow connections from Pipedream’s IP addresses. 2. VPN Connection: If your organization uses a VPN, you might be able to connect the Pipedream environment to your VPN, thus giving it access to the database through the bastion host. This would depend on the VPN setup and whether Pipedream can support such a configuration. 3. External Tunneling Service: Use an external tunneling service that can create a secure connection from Pipedream to your PostgreSQL database via the bastion. This service would expose a secure endpoint that Pipedream can access directly. This approach requires careful consideration of security implications. 4. Database as a Service (DBaaS): If you’re using a cloud provider, consider leveraging their managed database offerings (e.g., Amazon RDS, Google Cloud SQL). These services often provide secure, direct access without the need for a bastion host, and you can restrict access to specific IP ranges or set up VPC peering. 5. Custom Proxy Service: As a last resort, you could deploy a custom proxy service in your infrastructure that Pipedream can access directly. This service would then forward requests to the PostgreSQL database via the bastion host. This approach requires additional infrastructure and careful management of security concerns. For connecting to PostgreSQL databases in general, Pipedream supports direct connections using the pg npm package. Here’s an example code snippet for making a connection (without considering a bastion host): ``` import { Client } from “pg”; export default defineComponent({ props: { postgresql: { type: “app”, app: “postgresql”, }, }, async run({ steps, $ }) { const { host, user, password, port, database } = this.postgresql

is there any intention to make this work in future?

To provide you with the most accurate and helpful response, could you please clarify what specific feature or functionality you are referring to with “this”? Additional details will help me understand your needs and address your question more effectively.

this is supported in a couple of ways. If you already have a bastion host, you can use the ssh2-promise npm package to setup a tunnel. You can also use our native Pipedream VPCs to get a dedicated VPC for your workspace, with its own egress IP.

@U05A7KLNALC can you show me the code for the ssh2-promise option and link to the docs on VPCs?

Certainly! Below, you’ll find an example code snippet using the ssh2-promise npm package to set up an SSH tunnel. This example assumes you have already installed the ssh2-promise package and have a bastion host configured for SSH access. Additionally, I’ll provide links to the Pipedream documentation on Virtual Private Clouds (VPCs) for more information on setting up a dedicated VPC for your workspace. ### Example using ssh2-promise for SSH Tunneling import SSH from 'ssh2-promise'; import { axios } from "@pipedream/platform"; export default defineComponent({ async run({ steps, $ }) { const sshConfig = { host: 'your.bastion.host', username: 'your_username', privateKey: `your_private_key`, }; const ssh = new SSH(sshConfig); await ssh.connect(); // Example of forwarding a local port to a remote server const localPort = 8000; // Local port to forward from const remoteHost = '[remote.server.com](http://remote.server.com)'; // Remote host to forward to const remotePort = 80; // Remote port to forward to await ssh.forwardOut('127.0.0.1', localPort, remoteHost, remotePort, async (err, stream) => { if (err) throw err; // Use the stream as you would in any axios request return await axios($, { method: 'GET', url: `http://localhost:${localPort}/path`, // Example request to the forwarded port responseType: 'stream', }).then(response => { // Handle the response console.log(response.data); }); }); }, }); This code demonstrates setting up an SSH tunnel to a remote host using ssh2-promise and forwarding a local port to a remote server. You’ll need to replace 'your.bastion.host', 'your_username', and 'your_private_key' with your actual SSH bastion host details. Also, adjust the localPort, remoteHost, and remotePort to match your specific forwarding needs. ### Pipedream VPC Documentation For setting up a dedicated VPC for your workspace with its own egress IP, please refer to the following Pipedream documentation: - [Controlling egress traffic from P

Here are the docs on VPCs: https://pipedream.com/docs/workflows/vpc#managing-a-vpc

Nice, thanks !

We’re also very close to shipping the ability to connect to a Postgres DB via a shared static IP, in case you don’t need to run your entire workflow in a VPC, in addition to a much improved interface for working with SQL DBs :slightly_smiling_face:

Okay that would be great, as this would be the only reason for me to setup a VPC at this stage (as need to add the inbound rule to the bastion).

Would that only be on the enterprise plan? As currently pretty happy on the plan we’re on, but VPC is only on Enterprise.

It’ll likely come out on Advanced

Okay sweet. That would be superb.

Can I be that annoying person and ask if you have any more guidance that “close to shipping” in terms of time? Just will help me make a choice on whether I wait or take an alternative route in the interim.

Np at all — happy to hear we’re building features you’re excited about! We should be announcing these features in the next couple weeks.

Thanks Danny, will keep my eyes peeled :slightly_smiling_face: