Hi,
I think I found some bugs in `postfix-pgsql` lookup, or at least the
docs don't match the actual behavior.
1:
The expansion parameters `%s`, `%u` and `%d` are not working as documented:
If I'm just using `%s` in my query template like this:
query = SELECT foo FROM mytable WHERE foo = '%s'
... then I'm getting *three* queries in my PostgreSQL log:
2024-10-21 12:38:37.556 UTC [32997] postfix@db LOG: statement: SELECT foo FROM
mytable WHERE foo = 'u...@example.com'
2024-10-21 12:38:37.559 UTC [32997] postfix@db LOG: statement: SELECT foo FROM
mytable WHERE foo = 'user'
2024-10-21 12:38:37.561 UTC [32997] postfix@db LOG: statement: SELECT foo FROM
mytable WHERE foo = '@example.com'
So it looks like if `%s` doesn't yield a result, then another try with
`%u`, and a third with `%d` is made.
But when using `%d` in the query template, the behavior differs in two
aspects:
Firstly, the same query is executed *twice*:
2024-10-21 13:00:13.914 UTC [33773] postfix@db LOG: statement: SELECT foo FROM
mytable WHERE foo = 'example.com'
2024-10-21 13:00:13.916 UTC [33773] postfix@db LOG: statement: SELECT foo FROM
mytable WHERE foo = 'example.com'
And secondly, note the different expansions for `%d`:
`@example.com` in the above queries, vs.
`example.com` here.
2:
This line at https://www.postfix.org/pgsql_table.5.html is wrong:
hosts = postgresql://usern...@example.com/tablename?sslmode=require
I guess it should be:
hosts = postgresql://username:password@127.0.0.1/databasename
`sslmode` isn't documented at all.
3:
The encoding doesn't work as documented:
The encoding used by the database client. The default setting is:
encoding = UTF8
However, the default setting seems to be LATIN1 in fact, cause I'm
getting this in my PostgreSQL log:
2024-10-21 12:15:07.424 UTC [31953] postfix@db LOG: statement: set
client_encoding to 'LATIN1'
2024-10-21 12:15:07.428 UTC [31953] postfix@db LOG: statement: SELECT ...
Furthermore, changing the encoding doesn't work - this seems to be a bug
in the code (not just in the docs):
When trying to append it to the connection string like that:
hosts =
postgresql://usern...@example.com/tablename?sslmode=require?encoding=UTF8
... I'm getting this in my Postfix log:
Oct 21 14:17:27 backup postfix/smtpd[32279]: warning: connect to pgsql server
postgresql://postfix:...@127.0.0.1/db?encoding=UTF8: invalid URI query parameter:
"encoding"?
And setting it the "old school" way has no effect (i.e. same `LATIN1`
entry in PostgreSQL log as above):
hosts = postgresql://postfix:...@127.0.0.1/db
encoding = UTF8
4:
The connection string does replace the "old school" config parameters
`user =` and `password =`, but not `dbname =`.
If I omit the (redundant) `dbname =`, I'm getting this in my Postfix log:
Oct 21 13:54:25 backup postfix/smtpd[30180]: fatal: /etc/postfix/postgres_db.cf: bad string length 0 < 1: dbname =
5:
During testing, I stumbled across this entry in my Postfix log:
Oct 21 14:56:10 backup postfix/smtpd[33373]: warning: pgsql query failed: fatal
error from host postgresql://postfix:...@127.0.0.1/db: SSL connection has been
closed unexpectedly?
Why is an SSL connection tried to localhost? (I didn't set the `sslmode`
parameter, see 2)
* Postfix version: 3.7.11
* postfix-pgsql/stable,stable,now 3.7.11-0+deb12u1 amd64 [installed]
* Entry in `main.cf`:
local_recipient_maps = regexp:/etc/postfix/local_recipients
pgsql:/etc/postfix/postgres_db.cf
--
Cheers,
Thomas
_______________________________________________
Postfix-users mailing list -- postfix-users@postfix.org
To unsubscribe send an email to postfix-users-le...@postfix.org
