Support for postgres ssl mode with certificates.

[abhishekgahlot2]

Hi

Great repo, Wondering if you are planning to add ssl certificate mode or can guide me on how to add that support i am happy to take it up.

thanks

[jreyesr]

Hello! I'm absolutely open to implementing such functionality if required, but maybe it already works.

By the way, I'm assuming that you'd like to use what is usually called sslmode and its associate configs (https://www.postgresql.org/docs/current/libpq-ssl.html). Let me know if I completely misunderstood your request, and you were referring to an entirely different "SSL mode" somewhere else in Postgres.

Assuming that you want to use sslmode and friends, I'm checking https://pkg.go.dev/github.com/jackc/pgx/v5@v5.6.0/pgconn#ParseConfig, which is the package that this plugin uses under the hood, and it appears to already support all the requisite SSL config values. For example, if you're using the URL-style connection string, something like this should already work:

connection "postgres" {
  plugin = "jreyesr/postgres"

  connection_string = "postgres://username:password@localhost:5432/database_name?sslmode=verify-ca&sslrootcert=/somewhere/server-ca.pem&sslcert=/somewhere/client-cert.pem&sslkey=/somewhere/client-key.pem"
}

Or if you're using the key-value style connections, something like this:

connection_string = "user=username password=password host=localhost port=5432 dbname=database_name sslmode=verify-ca sslrootcert=/somewhere/server-ca.pem sslcert=/somewhere/client-cert.pem sslkey=/somewhere/client-key.pem"

Then, if you place the PEM files that SSL validation requires (the CA for the server cert, and the client's cert and private key if you're doing client certificates too) in the /somewhere directory, the plugin should be able to read them. If you're running Steampipe under Docker, you may want to either include those files in the base image (especially now that Turbot no longer provides official Docker images, so you must build them yourself anyways) or use a volume to mount those files from the host into a container path.

For example, when I use the connection string postgres://postgres:postgres@localhost:5432/steampipe_host?sslmode=disable, I see this. Notice how the connection using PID 14284 (which is the one connected to Steampipe) isn't using SSL:

However, if I change the connection string to postgres://postgres:postgres@localhost:5432/steampipe_host?sslmode=require, I get a new PID 14563, which is using SSL:

And just to be sure, I changed the connection string to postgres://postgres:postgres@localhost:5432/steampipe_host?sslmode=verify-full, and I got an error message as expected, since my Postgres server is presenting a self-signed cert, not a valid one:

If you could try something like the connection strings above, that'd be great. Hopefully they work, they appear to wrk as expected for me.