Connecting to a database in AWS using SSL

vitoco has asked for the wisdom of the Perl Monks concerning the following question:

Hello. I built a small local database in Postgres and use some perl scripts to load and update data, and then to extract for some reports. My scripts use the standard DBI module:

use DBI;
$dbh = DBI->connect("dbi:Pg:dbname=mydb;host=localhost;port=5432;",
                    $username,
                    $password,
                    {AutoCommit => 0, RaiseError => 1, PrintError => 0
+}
                   );
[download]

Now, I have to move my local database model to an AmazonAWS Postgres database, which it is using an SSL tunnel (a second hostname and port). Also, as my localhost is outside of the HQ, I had to connect my box to there using a VPN in order to be able to access AWS.

I was given some credentials and a PEM file, so I can connect to that database server using the pgAdmin tool, but I cannot figure out how to implement that kind of connection in my scripts. I'm not sure if the sslmode connect string option is useful in this case...

Any idea on how to configure the connect string? Should I use another module as a wrapper?

Thanks...

Comment on Connecting to a database in AWS using SSL Select or Download Code

Replies are listed 'Best First'.
Re: Connecting to a database in AWS using SSL by NERDVANA (Priest) on May 30, 2025 at 18:24 UTC
The Postgres client library supports SSL directly, so what you're really looking to do here is just pass configuration parameters to libpostgres. Pretty much every configuration parameter can be specified on the `$dsn`, so you should be able to borrow configuration examples from any language, not just perl. You could even choose to pass some of them by environment variable, so that your perl script doesn't need to specify them directly. Another great option is to reference configured services in the standard postgres `~/.pg_service.conf` file. This is documented in connect of DBD::Pg: You can also connect by using a service connection file. Service names can be defined in either a per-user service file or a system-wide file. If the same service name exists in both the user and the system file, the user file takes precedence. By default, the per-user service file is named ~/.pg_service.conf. On Microsoft Windows, it is named %APPDATA% \postgresql\.pg_service.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile). A different file name can be specified by setting the environment variable PGSERVICEFILE. The system-wide file is named pg_service.conf. The location of this file can be controlled by setting the PGSYSCONFDIR environment variable. To use one of the named services within the file, set the name by using either the service parameter or the environment variable PGSERVICE. Note that when connecting this way, only the minimum parameters should be used. For example, to connect to a service named "zephyr", you could use: `$dbh = DBI->connect("dbi:Pg:service=zephyr", '', '');` [download] You could also set $ENV{PGSERVICE} to "zephyr" and connect like this: `$dbh = DBI->connect("dbi:Pg:", '', '');` [download] To isolate problems related to perl, you can use that same config file for standard pg commandline tools like 'psql'. If `psql -d service=myservicename` can connect, then perl should also. This also saves you from needing to alter the script when you deploy it in different environments. And with a single environment variable, you can override whether it's connecting to the production DB or your local copy for testing.	[reply] [d/l] [select]
Re: Connecting to a database in AWS using SSL by vitoco (Hermit) on Jun 12, 2025 at 02:38 UTC
Thanks and just an update.... The connection to AWS services requires an SSH Tunnel and a plain connection through it. I just have to use the PEM file and `ssh` command line to open a session whice enables the tunnel between a local port and the remote port in the server where Postgres runs. The only thing I had to do in my scripts was to change the local port number in the connection string in order to get into the tunnel and, as I supposed, the `sslmode` was not required. Well, when I started this thread, I was thinking that there probably existed a module to provide the SSH tunnel without the dummy shell, but now I have to call an external non-perl script to set up the tunnel before trying to connect to the remote database, and deal with an unexpected close of the tunnel. BTW, `psql` tool also requires the tunnel to reach AWS postgresql databases.	[reply] [d/l] [select]
Re^2: Connecting to a database in AWS using SSL by hippo (Archbishop) on Jun 12, 2025 at 11:41 UTC
I was thinking that there probably existed a module to provide the SSH tunnel without the dummy shell The module Net::OpenSSH will let you manage such tunnels (as well as many other operations besides). Give that a look and see if it will allow you to handle everything from inside your Perl script. 🦛	[reply]