search

jreyesr / steampipe-plugin-postgres

A Steampipe plugin that provides access to data stored in plain Postgres servers
Apache License 2.0
6 stars 2 forks source link

Support for postgres ssl mode with certificates. #4

Open abhishekgahlot2 opened 3 weeks ago

abhishekgahlot2 commented 3 weeks ago

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 commented 3 weeks ago

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:

image

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:

image

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:

image

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.