On Thu, Oct 24, 2024 at 11:31:13AM +0200, Thomas Landauer via Postfix-users wrote:
> 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: You're mistaken. The behaviour is exactly 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' These are three separate queries with the full address, the user part and the domain part, as documented for various address rewriting tables. See virtual(5), aliases(5), canonical(5), ... > So it looks like if `%s` doesn't yield a result, then another try with `%u`, > and a third with `%d` is made. Nothing of the sort, rather higher-level facilities issue multiple queries with separate lookup keys. > But when using `%d` in the query template, the behavior differs in two > aspects: > Firstly, the same query is executed *twice*: The bare user query is suppressed, as documented, it lacks a domain. > > 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. Well, you asked for '%d' so that's what you got. > 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. The "sslmode" parameter is documented: https://www.postgresql.org/docs/9.2/app-psql.html What problem were you having with that connection string? If you're trying to say that "tablename" should be "databasename", then you're right. > 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 ... What version of Postfix are you using? The switch from "LATIN1" to "UTF8" is recent. postfix-3.8-20220509 diff --git a/postfix/HISTORY b/postfix/HISTORY index 3da460d7d..14ed05def 100644 --- a/postfix/HISTORY +++ b/postfix/HISTORY @@ -26495,3 +26495,10 @@ Apologies for any names omitted. Documentation: added POSTLOG_SERVICE and POSTLOG_HOSTNAME to the import_environment description. File: proto/postconf.proto. + +20220509 + + Cleanup: the pgsql: client encoding is now configurable + with the "encoding" configuration file attribute. The default + is "UTF8". Previously the encoding was hard-coded as "LATIN1". + Files: global/dict_pgsql,c, proto/pgsql_table. For documentation about your version of Postfix, look at the manpage for pgsql-table(5) that shipped with your system. > 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 Who said this is a supported way to change the encoding in Postfix? In older versions of Postfix the client encoding was hardcoded to "LATIN1" as documented. > ... 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"? The "encoding = ..." setting goes into the configuration file as a Postfix table parameter, not as a component of the connect string URI. > 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 Are you using a version of Postfix that is 3.8 or newer? > 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 = The "dbname" is currently not optional. > 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) The Postgresql library may have various default behaviours, that you need to override. Postfix just passes your connection string to the Postgres library, which decides what it means. > * Postfix version: 3.7.11 > * postfix-pgsql/stable,stable,now 3.7.11-0+deb12u1 amd64 [installed] > * Entry in `main.cf`: This looks to be older than 3.8, so the encoding issues are exactly as expected. > > local_recipient_maps = regexp:/etc/postfix/local_recipients > > pgsql:/etc/postfix/postgres_db.cf See the documentation of "local_recipient_maps" for the lookup keys used. -- Viktor. _______________________________________________ Postfix-users mailing list -- postfix-users@postfix.org To unsubscribe send an email to postfix-users-le...@postfix.org
