PostgreSQL address default value "local" instead of "localhost"

[exhuma]

Relevant telegraf.conf:

[[inputs.postgresql]]
address = ""

System info:

• Telegraf at commit 36193aea
• postgresql version 10.5-0ubuntu0.18.04

Steps to reproduce:

• set an empty address in telegraf.conf for that DB

Expected behavior:

• DB connection should use the PostgreSQL default, which is the use of a local unix domain socket

Actual behavior:

• The DB connection uses a TCP connection via localhost and disables SSL

Additional info:

This issue is a fairly low priority, but it got me stumped when playing around with telegraf. On my development box I don't need TCP connections to PostgreSQL and never enabled it. All my apps run locally using a unix-domain socket. This is the default config of PostgreSQL. All other PG clients I know of (including psql) will connect using the Unix domain socket when no DSN is specified.

I would expect telegraf to behave like any other PG client in it's default config.

Right now the DSN is hardcoded to localhost if an empty address is specified.

[danielnelson]

We should probably do what psql does, but the default would only change in Telegraf 2.0. In the meantime I think we should improve the comment for this option to show how to set it up for unix socket connections.

We also need to check how psql determines where to connect, it may be reading a config file on your host.

[ReinsBrain]

@danielnelson suggested

show how to set it up for unix socket connections

I would be very keen to see how you manage configuration to allow unix socket connection - i've been trying over and over again with no luck :)

Following up: @danielnelson - sorry to be a pain in rear - the way i'm reading your comment above is that it is possible to set telegraf to allow it to query postgres using sockets - if that is possible I am very interested because i'd like to avoid hitting the tcpip stack with frequency (im deploying to 200+ servers). If I have read that wrong I'm even willing to fork and fix (temporarily until 2.0 drops) - please advise :)

A little bit more followup: it looks like you're importing a postgres go library from jackc - a bit of digging and i see that host may also be a path to a socket: https://github.com/jackc/pgx/blob/4618730e71353189c1ae1632cb7d166613a882eb/conn.go#L66

Perhaps if i send the default postgres socket path via the host variable i will be able to get it rolling - I'll follow again with my results...

[urusha]

@ReinsBrain @danielnelson As I can see using unix sockets is not possible at the moment (url.Parse is used https://github.com/influxdata/telegraf/blob/master/plugins/inputs/postgresql/service.go#L42). We might:

• use magic host like local to translate it to path (static path).
• get the path from the url path excluding last part (db) user@anyhost:port/path/to/socket/dir/database
• override host using host=/path/to/socket/dir DSN parameter (like sslmode). Add possibility to override any parameter?
• support native pg connection strings in address or another parameter which overrides address (what is the purpose of url formatting at all?)

I thinks the last option is the best, since it would not break existing configurations, but adds full pg flexibility.

The same for pgbouncer plugin. What do you think?

[urusha]

After writing comment, I've realized that I didn't try to use native pg connection string in address, which is working, actually ( https://github.com/influxdata/telegraf/blob/master/plugins/inputs/postgresql/service.go#L19-L21 ). So disregard my previous comment, but we should really mention this in the docs!

[powersj]

Hi,

Sven and I have been going through issues with breaking changes. As of now we have no plan to do a 2.0 release with lots of breaking changes. As such we are trying to evaluate each breaking change issue case-by-case.

I realize this issue is old issue, but it does not seem to have gotten a lot of votes or others chiming. Making this change is clearly a breaking change for current users and not ideal.

We can discuss adding a setting to override the default and go directly to socket as a possible option. Otherwise, I am inclined to close this.

Thanks

[exhuma]

Since I've opened up this issue, I drifted from DevOps to more Dev without the Ops so I am no longer impacted by this.

Having said that, from what I recall when opening this, then the biggest issue was that there was no possibility to connect to the domain socket using telegraf (please correct me if I'm wrong here). This is an annoyance on hosts where you don't need or want to open the TCP socket on PostgreSQL because it forces you to open the TCP socket.

I understand that changing the default behaviour is not backwards compatible and I support the decision of pushing that to a later release.

As a workaround for 1.x it could also be solved by allowing a special value in the config. For example:

[[inputs.postgresql]]
address = "__socket__"

Or something along those lines. That way it would still be backwards compatible and remove the necessity of opening the TCP socket.

Also note, that the "host-based access control" in PostgreSQL has different rules depending on the Unix domain-socket being in use, or the TCP socket. And that this could be an argument why someone would want to prefer the domain-socket over TCP.

In any case, it would be good if telegraf allowed PG connections via the domain-socket. One way or another.

[powersj]

Since I've opened up this issue, I drifted from DevOps to more Dev without the Ops so I am no longer impacted by this.

I appreciate you taking the time to respond!

the biggest issue was that there was no possibility to connect to the domain socket using telegraf

I agree that would be an issue. This is something I need to confirm and ensure the library in use also works.

In any case, it would be good if telegraf allowed PG connections via the domain-socket. One way or another.

Agreed, let's keep this open.

next steps: test current postgresql input plugin against postgresql a local socket and check the result. If it does not work, verify library support and add option or some method to enable the local socket support.

[powersj]

So it looks like, as urusha mentions above, it is possible to connect to a socket. I used the following config and was able to get metrics:

[[outputs.file]]
[[inputs.postgresql]]
  address = "host=/var/run/postgresql user=postgres password=password"

postgresql,db=postgres_global,host=f1,server=host\=/var/run/postgresql\ user\=postgres\  tup_fetched=307i,xact_rollback=0i,blk_read_time=0,blk_write_time=0,temp_bytes=0i,tup_updated=1i,temp_files=0i,tup_returned=519i,xact_commit=0i,blks_hit=1521i,numbackends=0i,conflicts=0i,datid=0i,tup_deleted=0i,blks_read=57i,tup_inserted=0i,deadlocks=0i 1675199322000000000
postgresql,db=postgres,host=f1,server=host\=/var/run/postgresql\ user\=postgres\  tup_returned=30343i,conflicts=0i,temp_bytes=0i,xact_commit=70i,tup_inserted=0i,blks_hit=6480i,tup_deleted=0i,blk_read_time=0,xact_rollback=3i,tup_updated=0i,blks_read=394i,blk_write_time=0,numbackends=1i,tup_fetched=2992i,datid=13427i,deadlocks=0i,datname="postgres",temp_files=0i 1675199322000000000
postgresql,db=template1,host=f1,server=host\=/var/run/postgresql\ user\=postgres\  blks_hit=8616i,xact_rollback=0i,tup_fetched=3563i,numbackends=0i,temp_bytes=0i,tup_inserted=0i,conflicts=0i,datname="template1",blks_read=335i,tup_deleted=0i,tup_returned=50627i,deadlocks=0i,datid=1i,xact_commit=106i,temp_files=0i,blk_write_time=0,tup_updated=0i,blk_read_time=0 1675199322000000000
postgresql,db=template0,host=f1,server=host\=/var/run/postgresql\ user\=postgres\  tup_updated=0i,numbackends=0i,xact_commit=0i,tup_fetched=0i,deadlocks=0i,tup_inserted=0i,xact_rollback=0i,datid=13426i,datname="template0",temp_bytes=0i,tup_deleted=0i,blks_hit=0i,blk_write_time=0,conflicts=0i,blks_read=0i,blk_read_time=0,tup_returned=0i,temp_files=0i 1675199322000000000
postgresql,db=postgres,host=f1,server=host\=/var/run/postgresql\ user\=postgres\  checkpoint_write_time=2,checkpoints_req=0i,buffers_alloc=791i,checkpoints_timed=5i,checkpoint_sync_time=2,buffers_checkpoint=0i,buffers_clean=0i,maxwritten_clean=0i,buffers_backend=0i,buffers_backend_fsync=0i 1675199322000000000

I have put up #12584 with a docs change as it appears the functionality is there. While we won't be changing the default behavior, we can certainly try to make this more clear for future users.