Hi,

I have a problem with a postfix 2.3.8 mail server that use a postegresql
8.1.9 database for resolving email aliases

The query performed by postfix looks like this :
SELECT DISTINCT primary_mail FROM v_aliases WHERE alias_mail='%s'

and it works with postmap (a postfix utility to check the config)

holy:~# postmap -q rico (AT) ricospirit (DOT) net pgsql:/etc/postfix/pgsql-alias.sql
ebelhomme (AT) ricospirit (DOT) net

But it fails when it is invoked by postfix daemon :

Nov 5 11:42:08 holy postfix/trivial-rewrite[18258]: fatal:
pgsql:/etc/postfix/pgsql-alias.cf(0,lock|fold_fix): table lookup problem
Nov 5 11:42:09 holy postfix/smtpd[18192]: warning: premature end-of-
input on private/rewrite socket while reading input attribute name
Nov 5 11:42:09 holy postfix/smtpd[18192]: warning: problem talking to
service rewrite: Success
Nov 5 11:42:09 holy postfix/master[15131]: warning: process
/usr/lib/postfix/trivial-rewrite pid 18258 exit status 1
Nov 5 11:42:09 holy postfix/master[15131]: warning:
/usr/lib/postfix/trivial-rewrite: bad command startup -- throttling

As far as I can understand, there is a lock table problem when it is
executed by postfix, but I don't know why ?

So this is the database structure, hope someone will be able to explain
where is the problem

CREATE TABLE "public"."t_domain" (
"colid" SERIAL,
"name" VARCHAR(128) NOT NULL,
"active" CHAR(1) DEFAULT 1 NOT NULL,
"sysowner_oid" INTEGER NOT NULL,
"syspath" VARCHAR(255) NOT NULL,
"max_users" INTEGER DEFAULT -1 NOT NULL,
CONSTRAINT "t_domain_ct_domain_primary_key" PRIMARY KEY("colid",
"name"),
CONSTRAINT "t_domain_ct_domain_unique_name" UNIQUE("name")
) WITHOUT OIDS;

CREATE UNIQUE INDEX "t_domain_colid_key" ON "public"."t_domain"
USING btree ("colid");


CREATE TABLE "public"."t_mailbox" (
"colid" SERIAL,
"username" VARCHAR(64) NOT NULL,
"passwd" VARCHAR(40) NOT NULL,
"quota" INTEGER DEFAULT -1 NOT NULL,
"active" CHAR(1) DEFAULT 1 NOT NULL,
"vacation" CHAR(1) DEFAULT 0 NOT NULL,
"forward" CHAR(1) DEFAULT 0 NOT NULL,
"forward_to" VARCHAR(128),
"vacation_msg" VARCHAR(1024),
"admin_access" CHAR(1) DEFAULT 0 NOT NULL,
"firstname" VARCHAR(64),
"surname" VARCHAR(64),
CONSTRAINT "t_mailbox_ct_mailbox_primary_key" PRIMARY KEY("colid",
"username"),
CONSTRAINT "t_mailbox_ct_mailbox_unique_user" UNIQUE("username")
) WITHOUT OIDS;

CREATE UNIQUE INDEX "t_mailbox_colid_key" ON "public"."t_mailbox"
USING btree ("col


CREATE TABLE "public"."t_alias" (
"colid" SERIAL,
"id_domain" INTEGER NOT NULL,
"id_mailbox" INTEGER NOT NULL,
"alias" VARCHAR(128) NOT NULL,
CONSTRAINT "t_alias_ct_alias_primary_key" PRIMARY KEY("colid",
"id_domain", "id_mailbox"),
CONSTRAINT "t_alias_ct_alias_unique_domain_alias" UNIQUE("id_domain",
"alias"),
CONSTRAINT "t_alias_fk_domain" FOREIGN KEY ("id_domain")
REFERENCES "public"."t_domain"("colid")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE,
CONSTRAINT "t_alias_fk_mailbox" FOREIGN KEY ("id_mailbox")
REFERENCES "public"."t_mailbox"("colid")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) WITHOUT OIDS;


CREATE VIEW "public"."v_aliases" (
username,
userpass,
domainname,
primary_mail,
alias_mail,
sys_oid,
syspath,
vacation,
forward)
AS
SELECT mb.username, mb.passwd AS userpass, dm.name AS domainname,
(((mb.username)::text || '@'::text) || (dm.name)::text) AS
primary_mail,
(((al.alias)::text || '@'::text) || (dm.name)::text) AS alias_mail,
dm.sysowner_oid AS sys_oid, dm.syspath, mb.vacation, mb."forward"
FROM ((t_alias al JOIN t_domain dm ON ((al.id_domain = dm.colid))) JOIN
t_mailbox mb ON ((al.id_mailbox = mb.colid)))
WHERE ((dm.active = '1'::bpchar) AND (mb.active = '1'::bpchar));


I have to precise the same database running on postgresql 7.4.17 does not
produce this problem !

Thanks for your help

--
Rico

Eric Belhomme <{rico}+no/spam (AT) ricospirit (DOT) net> wrote:
On the PostgreSQL server, edit the postgresql.conf file and change
the log_statement parameter to

log_statement = 'all'

Then reload the new configuration with

pg_ctl -D /postgres/server/directory reload

(where /postgres/server/directory should be replaced with the actual value).

Then try both cases, the 'postmap' where it works and the 'postfix' where
it doesn't.
In both cases the statements should be written to the database server log.
Check if they are the same and if both processes connect as the same
database user!

Try to find out the exact PostgreSQL error message that leads to the
postfix error!

Don't forget to reset log_statement to 'none' after you are done.

This information should help us to help you.

Yours,
Laurenz Albe

Laurenz Albe <invite (AT) spam (DOT) to.invalid> wrote in
news:1194357174.139542 (AT) proxy (DOT) dienste.wien.at:


thanks for your suggest ! It helped me to understand postgresql refused
postfix-pgsql to connect because it reclaimed ssl connect !

Finally the sql statement works well

--
Rico