use arbitrary connection strings

[wulczer]

The module is great, but it would be useful to have more flexibility whne choosing the way Nginx connects to Postgres.

I'm working on adding a way to pass arbitrary libpq connection strings, so you could do things like:

postgres_connstring host=/var/run/postgresql dbname=mydb

to get a connection over UNIX sockets or even:

postgres_connstring sslcert=... sslrootcert=... ...

which is our use case, since we're authenticating all applications using the database with SSL certificates.

I'm wondering what would be the best way to implement it (and whether you'd be willing to accept such a patch). A separate directive (like postgres_connstring) that overrides postgres_server? Making postgres_server understand that if the directive is not "host:ip params", it's a literal libpq connstring? Something else entirely?

[PiotrSikora]

Add new parameters (with PostgreSQL names) to the "postgres_server" directive (i.e. ngx_postgres_conf_server function).

I won't accept changes that will simply pass whole connection string "as-is" from the nginx.conf to the database.

[wulczer]

Are you sure that's the best way to do it? There are around 26 connection parameters and new are being added in each release. Both the ngx_postgres_upstream_server_t and ngx_postgres_upstream_peer_t structs would have to include fields for those parameters and it still wouldn't solve problems with using UNIX sockets or wanting to use a different sslmode (ngx_postgres currently forces it to "disable").

I think libpq applications are encouraged to use a connection string for configuration.

[PiotrSikora]

Are you sure that's the best way to do it?

Nope, but it's the one we're taking.

There are around 26 connection parameters and new are being added in each release.

But of all those, we need less than a dozen (client_encoding, application_name, sslmode + friends).

I think libpq applications are encouraged to use a connection string for configuration.

Based on what exactly?

What problem are you trying to solve? Why do you need connection string?

[wulczer]

I'd like to:

• use SSL certificates to authenticate Nginx to Postgres
• verify that the Postgres instance I'm connecting to presents itself with a correct SSL certificate
• use a hostname instead of a host address to connect (this can block while libpq resolves the hostname, but I'm OK with that)
• be able to use UNIX sockets in case we decide to use them
• be able to use a service file instead of spelling out all connection variables in the Nginx config file

Our current approach is to use libpq environment variables, which allows us to work around some of these. We can use SSL certs, but still not use a hostname, UNIX sockets or have everything specified in a service file.

Ideally, we'd configure ngx_postgres with

postgres_connstring service=nginx

and keep all the configuration it our pg_service.conf. But like I said, with a couple of env directives we were able to work around the issues that were most important for us.

[PiotrSikora]

• use SSL certificates to authenticate Nginx to Postgres
• verify that the Postgres instance I'm connecting to presents itself with a correct SSL certificate

You can do that by adding params to postgres_server.

• use a hostname instead of a host address to connect (this can block while libpq resolves the hostname, but I'm OK with that)

But I'm most definitely. To be honest, blocking gethostbyname is one of the reasons why I don't like the idea of passing connection string "as-is".

• be able to use UNIX sockets in case we decide to use them

Uhm, keep in mind that SSL doesn't work with UNIX sockets.

• be able to use a service file instead of spelling out all connection variables in the Nginx config file

This is indeed appealing idea, but I won't support this because of blocking gethostbyname.

[wulczer]

OK, I see your point.

Since I have a valid workaround now (using environment variables) I'll just stick with that.

Thanks for your input!