Can't use UNIX socket connection

[caseyandgina]

'user=app_sql_exporter dbname=postgres host=/run/postgresql port=5432'
...results in:
{"caller":"job.go:189","err":"sql: unknown driver \"\" (forgotten import?)","job":"example","level":"warn","msg":"Failed to connect","ts":"2024-07-26T17:50:10.540688846Z"}

'postgres://app_sql_exporter:testpw@%2Frun%2Fpostgresql:5432/postgres'
...results in:
{"caller":"job.go:104","err":"parse \"postgres://app_sql_exporter@%2Frun%2Fpostgresql:5432/postgres\": invalid URL escape \"%2F\"","job":"example","level":"error","msg":"Failed to parse URL","ts":"2024-07-26T17:49:26.091765976Z","url":"postgres://app_sql_exporter@%2Frun%2Fpostgresql:5432/postgres"}

'postgres://app_sql_exporter:testpw@/run/postgresql:5432/postgres'
...results in:
{"caller":"job.go:189","err":"pq: no pg_hba.conf entry for host \"127.0.0.1\", user \"app_sql_exporter\", database \"run/postgresql:5432/postgres\", SSL encryption","job":"example","level":"warn","msg":"Failed to connect","ts":"2024-07-26T17:48:58.382065441Z"}

[marevers]

@caseyandgina your database hostname/IP is misconfigured:
For instance here:
'postgres://app_sql_exporter:testpw@/run/postgresql:5432/postgres'

/run/postgresql should be replaced with the hostname or IP of the database.

[caseyandgina]

@caseyandgina your database hostname/IP is misconfigured: For instance here: 'postgres://app_sql_exporter:testpw@/run/postgresql:5432/postgres'

/run/postgresql should be replaced with the hostname or IP of the database.

No, that would open a TCP connection, rather than UNIX socket. There's no point in using a TCP for a local connection. Note the following URI works with psql (as with generally anything else that accepts a URI database connection string) and there isn't a client_id/client_addr for a UNIX socket connection:

$ psql "postgres://app_sql_exporter@%2Frun%2Fpostgresql:5432/postgres"
psql (16.3 (Ubuntu 16.3-1.pgdg24.04+1))
Type "help" for help.

postgres=> \x
Expanded display is on.
postgres=> select * from pg_stat_activity where pid = pg_backend_pid ();
-[ RECORD 1 ]----+--------------------------------------------------------------
datid            | 5
datname          | postgres
pid              | 1749759
leader_pid       | 
usesysid         | 11154078
usename          | app_sql_exporter
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2024-07-29 13:58:51.12089+00
xact_start       | 2024-07-29 13:59:08.337709+00
query_start      | 2024-07-29 13:59:08.337709+00
state_change     | 2024-07-29 13:59:08.337711+00
wait_event_type  | 
wait_event       | 
state            | active
backend_xid      | 
backend_xmin     | 7554800
query_id         | -1520606479713249872
query            | select * from pg_stat_activity where pid = pg_backend_pid ();
backend_type     | client backend

[caseyandgina]

UNIX socket connections have less overhead, and they can be more secure since access can be further regulated through permissions on the socket file (/run/postgresql/.s.PGSQL.5432 in this case), to ensure that e.g. only system users in the postgres group are able to connect.

[dewey]

This is expected and not something that's currently supported. The current implementation is only using URLs and uses url.Parse for most of the supported databases (

sql_exporter/job.go

Line 246 in 0001728

if strings.HasPrefix(conn, "postgres://") || strings.HasPrefix(conn, "pg://") {

).

It could probably be implemented, so if you are interested in that you could open a PR.

[mwaniki-wairungu]

FYI:

sql_exporter/vendor/github.com/lib/pq/README.md

Line 19 in 3f78c6b

* Unix socket support

sql_exporter/vendor/github.com/lib/pq/doc.go

Lines 42 to 49 in 3f78c6b

	For compatibility with libpq, the following special connection parameters are
	supported:
	* dbname - The name of the database to connect to
	* user - The user to sign in as
	* password - The user's password
	* host - The host to connect to. Values that start with / are for unix
	domain sockets. (default is localhost)

For PostgreSQL, this is already supported by using the host keyword as such:

postgres://app_sql_exporter:testpw@/postgres?host=/run/postgresql

/ $ netstat -a
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       
tcp        0      0 :::9237                 :::*                    LISTEN      
Active UNIX domain sockets (servers and established)
Proto RefCnt Flags       Type       State         I-Node Path
unix  3      [ ]         STREAM     CONNECTED     2200197 /run/postgresql/.s.PGSQL.5432
unix  3      [ ]         STREAM     CONNECTED     2198333 

[dewey]

Nice, so I think we can close this issue right?