/etc/postfix/pgsql/ files discrepancy [SOLVED]

[jankom]

My PostgrSQL database was created by postfixadmin web-app after solving virtual web hosting and php activation issues, successful login to paostfixadmin web site. All I did there was to add my existing email address and pointing postmaster, etc. aliases to my email address..

jankom

[grknight]

Like pingtoo has said, postfixadmin keeps user data in the mailbox table. There is no user or users table from your output.

So,

Code: Select all

userdb sql {
  auth_username_format = %{user | username}
  iterate_query = SELECT userid AS username, domain FROM users
  query = SELECT home, uid, gid FROM users WHERE userid = '%{user | username}' AND domain = '%{user | domain}'
}
passdb sql {
  auth_username_format = %{user | username}
  query = SELECT userid AS username, domain, password FROM user WHERE userid = '%{user | username}' AND domain = '%{user | domain}'
}

Should likely be:

Code: Select all

userdb sql {
  auth_username_format = %{user | username}
  iterate_query = SELECT username, domain FROM mailbox
  query = local_part AS user, CONCAT('/var/vmail/',maildir) AS home FROM mailbox WHERE local_part = '%{user | username}' AND domain = '%{user | domain}'
}
passdb sql {
  auth_username_format = %{user | username}
  query = SELECT local_part AS username, domain, password FROM mailbox WHERE local_part = '%{user | username}' AND domain = '%{user | domain}'
}

[pingtoo]

The relevant part of my dovecot configuration now is:

Code: Select all

service auth {
  # Postfix (SMTP Auth)
  unix_listener /var/spool/postfix/private/auth {
    mode = 0660
    user = postfix
    group = postfix
  }

  # Doveadm & Internal Lookups (Crucial for Virtual Users)
  unix_listener auth-userdb {
    mode = 0600
    user = vmail
  }
}

and various outputs of commands as root:

Code: Select all

andraslinux /home/janos # doveconf -n service auth
service auth {
  unix_listener /var/spool/postfix/private/auth {
    group = postfix
    mode = 0660
    user = postfix
  }
  unix_listener auth-userdb {
    mode = 0600
    user = vmail
  }
}
andraslinux /home/janos # doveconf -n userdb passdb
userdb sql {
  auth_username_format = %{user | username}
  iterate_query = SELECT userid AS username, domain FROM users
  query = SELECT home, uid, gid FROM users WHERE userid = '%{user | username}' AND domain = '%{user | domain}'
}
userdb static {
  fields {
    gid = vmail
    home = /var/vmail/%{user | domain}/%{user | username}
    uid = vmail
  }
}
passdb sql {
  auth_username_format = %{user | username}
  query = SELECT userid AS username, domain, password FROM user WHERE userid = '%{user | username}' AND domain = '%{user | domain}'
}
passdb pam {
}
andraslinux /home/janos # dovecot auth login janos@andraslinux.jgklinux.jangkom.com
Password: 
Error: cmd auth login: user janos@andraslinux.jgklinux.jangkom.com: internal auth failure
extra fields:
  user=janos@andraslinux.jgklinux.jangkom.com
  code=temp_fail
andraslinux /home/janos # 

and mail.error log file snippet:

Code: Select all

Mar 28 07:37:32 localhost dovecot: auth: Error: sqlpool(pgsql): Query failed, aborting: SELECT userid AS username, domain, password FROM user WHERE userid = 'janos' AND domain = 'andraslinux.jgklinux.jangkom.com'
Mar 28 07:37:32 localhost dovecot: auth(janos@andraslinux.jgklinux.jangkom.com,sasl:plain): Error: sql: Password query failed: ERROR:  column "userid" does not exist
Mar 28 07:37:32 localhost dovecot: auth(janos@andraslinux.jgklinux.jangkom.com,sasl:plain): Error: sql: LINE 1: SELECT userid AS username, domain, password FROM user WHERE ...

I have been playing with this, rebooted, cleared cashe, etc. - still no sugar.
How come sqlpool had a query with "userid"? There is no such word in dovecot.conf or 50-misc.conf files. Not even in postfix main.cf or master.cf files.

jankom

Maybe it is me not have enough understanding of English or western couture

Why are you keep questioning this "userid" thingy again? it is very clear in your posted output the

Code: Select all

passdb sql {
  auth_username_format = %{user | username}
  query = SELECT userid AS username, domain, password FROM user WHERE userid = '%{user | username}' AND domain = '%{user | domain}'
}

Spcifically ask "...WHERE userid = '%{user | username}'. So must be somewhere in your dovecot configuration files. May be it is not one of shipped .conf file. It could be come from somewhere in the .conf file yout have "!include </path/to/file>" statement?

[jankom]

My debugging method is to save files of various configuration attempts by disfiguring them (prepending filename with x or suffixing filename with .date or something). I did not realize that the "!include..." statement included them all regardless.

Now I moved them to ~/tmp/ directory, and the situation is different:

Code: Select all

andraslinux /etc/dovecot/conf.d # doveconf -n passdb 
doveconf: Panic: SELECT local_part AS = username, domain, password FROM mailbox WHERE local_part = '%{user | username}' AND domain = '%{user | domain)': syntax error, unexpected CBRACE, expecting CCBRACE or PIPE
doveconf: Error: Raw backtrace: libdovecot.so.0(backtrace_append+0x3d) [0x7f1cb6...

jankom

[pingtoo]

My debugging method is to save files of various configuration attempts by disfiguring them (prepending filename with x or suffixing filename with .date or something). I did not realize that the "!include..." statement included them all regardless.

Now I moved them to ~/tmp/ directory, and the situation is different:

Code: Select all

andraslinux /etc/dovecot/conf.d # doveconf -n passdb 
doveconf: Panic: SELECT local_part AS = username, domain, password FROM mailbox WHERE local_part = '%{user | username}' AND domain = '%{user | domain)': syntax error, unexpected CBRACE, expecting CCBRACE or PIPE
doveconf: Error: Raw backtrace: libdovecot.so.0(backtrace_append+0x3d) [0x7f1cb6...

jankom

see the query statement, at the end domain = '%{user | domain)' You have unmatched ')' at the end, it should be '}'.

[jankom]

Thank you both @grknight and @pingtoo.

Big step, but still errors.
mail.err file

Code: Select all

 Mar 28 18:19:08 localhost dovecot: auth: Fatal: passdb sql: sql pgsql: pgsql { .. } named list filter is missing

and mail.log file

Code: Select all

Mar 28 18:19:08 localhost dovecot: auth: Fatal: passdb sql: sql pgsql: pgsql { .. } named list filter is missing
Mar 28 18:19:08 localhost dovecot: master: Error: service(auth): command startup failed, throttling for 60.000 secs

and

Code: Select all

andraslinux /home/janos # doveconf -n service auth
service auth {
  unix_listener /var/spool/postfix/private/auth {
    group = postfix
    mode = 0660
    user = postfix
  }
  unix_listener auth-userdb {
    mode = 0600
    user = vmail
  }
}
andraslinux /home/janos # doveconf -n userdb
userdb sql {
  auth_username_format = %{user | username}
  query = SELECT CONCAT = ('/var/vmail/', 'maildir') AS home, 5000 AS uid, 5000 AS gid FROM = mailbox WHERE = local_part = '%{user | username}' AND domain = '%{user | domain}'
}
andraslinux /home/janos # doveconf -n passdb
passdb sql {
  auth_username_format = %{user | username}
  query = SELECT local_part AS = username, domain, password FROM mailbox WHERE local_part = '%{user | username}' AND domain = '%{user | domain}'
}
passdb pam {
}
andraslinux /home/janos # 

jankom

[pingtoo]

please post

Code: Select all

doveconf -n

if it is too big use app-text/wgetpaste send it to pastebin sevice.

[jankom]

Code: Select all

andraslinux /home/janos # doveconf -n
# 2.4.2 (0962ed2104): /etc/dovecot/dovecot.conf
# OS: Linux 6.12.58-gentoo x86_64 Gentoo Base System release 2.18 
# Hostname: localhost
dovecot_config_version = 2.4.2
auth_mechanisms = plain login
dovecot_storage_version = 2.4.2
mail_driver = maildir
mail_gid = vmail
mail_home = /var/vmail/%{user | domain}/%{user | username}
mail_path = ~/mail
mail_uid = vmail
pgsql_parameters {
  dbname = postfix
  host = localhost
  password = xxx
  user = postfix
}
protocols {
  imap = yes
  lmtp = yes
}
sql_driver = pgsql
passdb sql {
  auth_username_format = %{user | username}
  query = SELECT local_part AS = username, domain, password FROM mailbox WHERE local_part = '%{user | username}' AND domain = '%{user | domain}'
}
userdb sql {
  auth_username_format = %{user | username}
  query = SELECT CONCAT = ('/var/vmail/', 'maildir') AS home, 5000 AS uid, 5000 AS gid FROM = mailbox WHERE = local_part = '%{user | username}' AND domain = '%{user | domain}'
}
namespace inbox {
  inbox = yes
  mailbox Drafts {
    auto = subscribe
    special_use = "\\Drafts"
  }
  mailbox Sent {
    auto = subscribe
    special_use = "\\Sent"
  }
  mailbox "Sent Messages" {
    auto = no
    special_use = "\\Sent"
  }
  mailbox Spam {
    auto = create
    special_use = "\\Junk"
  }
  mailbox Trash {
    auto = subscribe
    special_use = "\\Trash"
  }
}
passdb pam {
}
ssl_server {
  cert_file = /etc/dovecot/server.pem
  key_file = /etc/dovecot/server.key
}
service auth {
  unix_listener /var/spool/postfix/private/auth {
    group = postfix
    mode = 0660
    user = postfix
  }
  unix_listener auth-userdb {
    mode = 0600
    user = vmail
  }
}
andraslinux /home/janos #

jankom

[pingtoo]

There are few syntax errors in query and the one that I am not certain but please give it a try,

1. query syntax error, unnecessary '=' sign.
From

Code: Select all

passdb sql {
  auth_username_format = %{user | username}
  query = SELECT local_part AS = username, domain, password FROM mailbox WHERE local_part = '%{user | username}' AND domain = '%{user | domain}'
}
userdb sql {
  auth_username_format = %{user | username}
  query = SELECT CONCAT = ('/var/vmail/', 'maildir') AS home, 5000 AS uid, 5000 AS gid FROM = mailbox WHERE = local_part = '%{user | username}' AND domain = '%{user | domain}'
}

To

Code: Select all

passdb sql {
  auth_username_format = %{user | username}
  query = SELECT local_part AS username, domain, password FROM mailbox WHERE local_part = '%{user | username}' AND domain = '%{user | domain}'
}
userdb sql {
  auth_username_format = %{user | username}
  query = SELECT CONCAT ('/var/vmail/', 'maildir') AS home, 5000 AS uid, 5000 AS gid FROM mailbox WHERE local_part = '%{user | username}' AND domain = '%{user | domain}'
}

2. the uncertainty part.
From

Code: Select all

pgsql_parameters {
  dbname = postfix
  host = localhost
  password = xxx
  user = postfix
}

To

Code: Select all

pgsql localhost {
  dbname = postfix
  host = localhost
  password = xxx
  user = postfix
}

[jankom]

Made changes.

Code: Select all

andraslinux /home/janos # doveadm user janos@andraslinux.jgklinux.jangkom.com
doveadm(janos@andraslinux.jgklinux.jangkom.com)<7381><>: Error: auth-master: conn unix:/run/dovecot/auth-userdb (pid=7369,uid=0): Disconnected unexpectedly

userdb lookup: Unexpectedly disconnected from auth service
field   value
andraslinux /home/janos #

Same arrors in mail log files as B4: "Fatal: passdb sql: sql pgsql: pgsql { .. } named list filter is missing"

I'll try something later.

jankom

[pingtoo]

Instead the one I posted,

Code: Select all

pgsql localhost {
  dbname = postfix
  host = localhost
  password = xxx
  user = postfix
}

Try this new format

Code: Select all

pgsql localhost {
  parameters {
    user = postfix
    password = xxx
    dbname = postfix
  }
}

[jankom]

The last one did not even work. Studied dovecot documentation, tried various options, but still the same thing: "pgsql { .. } named list filter is missing".Here is my current (and shortened) dovecot configuration

Code: Select all

andraslinux /etc/dovecot # doveconf -n             
# 2.4.2 (0962ed2104): /etc/dovecot/dovecot.conf
# OS: Linux 6.12.58-gentoo x86_64 Gentoo Base System release 2.18 ext4
# Hostname: localhost
dovecot_config_version = 2.4.2
auth_mechanisms = plain login
dovecot_storage_version = 2.4.2
mail_driver = maildir
mail_gid = vmail
mail_home = /var/vmail/%{user | domain}/%{user | username}
mail_path = /var/vmail/%{user | domain}/%{user | username}/.maildir
mail_uid = vmail
pgsql_host = localhost
protocols {
  imap = yes
  lmtp = yes
}
sql_driver = pgsql
passdb sql {
  auth_username_format = %{user | username}
  query = SELECT local_part AS username, domain, password FROM mailbox WHERE local_part = '%{user | username}' AND domain = '%{user | domain}'
}
userdb sql {
  auth_username_format = %{user | username}
  query = SELECT CONCAT ('/var/vmail/', 'maildir') AS home, 5000 AS uid, 5000 AS gid FROM mailbox WHERE local_part = '%{user | username}' AND domain = '%{user | domain}'
}
passdb pam {
}
ssl_server {
  cert_file = /etc/ssl/dovecot/server.pem
  key_file = /etc/ssl/dovecot/server.key
}
service auth {
  unix_listener /var/spool/postfix/private/auth {
    group = postfix
    mode = 0660
    user = postfix
  }
   unix_listener auth-userdb {
    mode = 0600
    user = vmail
  }
}
andraslinux /etc/dovecot #

After starting dovecot daemon:

Code: Select all

andraslinux /etc/dovecot # doveadm user janos@andraslinux.jgklinux.jangkom.com
doveadm(janos@andraslinux.jgklinux.jangkom.com)<10948><>: Error: auth-master: conn unix:/run/dovecot/auth-userdb (pid=10932,uid=0): Disconnected unexpectedly

userdb lookup: Unexpectedly disconnected from auth service
field   value
andraslinux /etc/dovecot #

and again, the named list filer is missing.

jankom

[pingtoo]

Will I am not able to understand your latest configuration why only defined "pgsql_host = localhost", How did you arrived to this configuration and why do you believe it will work?

I would prefer debug with my last posted configuration. I want to see my suggested configuration actually in the output of doveconf -n, and with that configuration what exactly error messages and what command was used cause the error messages. please include messages prior to the command execution so we know the "auth" process is actually successfully started.

P.S. I found two commands try prior to start any test.

Code: Select all

doveadm service status

and

Code: Select all

doveadm process status

[jankom]

How did you arrive

from https://www.postgresql.org/docs/current ... AMKEYWORDS - but it did not help. So now I want back to

my last posted configuration.

with "=" corrections.
This time I've increased postfix debug level. Still the same errors, no more info.
Actually, I don't know if there is somewhere a dovecot log file.
As far as the two doveadm commands you suggested:
service status has a lengthy output without anu clue - everything looks normal, bunch of zeros, no errors.
process status - equally no clue:

Code: Select all

andraslinux /home/janos # doveadm process status
name   pid   available_count total_count idle_start last_status_update last_kill_sent
anvil  19087 1000            1           1774878527 1774878527         0        
config 19090 999             3           0          1774880285         0        
log    19088 979             21          0          1774878331         0        
stats  19130 999             2           0          1774880285         0        

I have been searching dovecot documentation for explanation of "named list filter is missing" but still, I did not get smarter. Copy from dovecot documentation:
Named List Filter

The settings inside the filter are used only in a specific situation. The filter has a unique name, which can be used to identify it within the list. See Named Filters for more details
End of copy.

That's it for now.
I'll save this configuration and try removing settings to find out what settings have no effect to this named list filer problem.

jankom

[pingtoo]

If the "process" output were everything return from the command, that mean the "auth" process is not running at that moment.

I am not able currently start my test dovecot vm because the host is heavenly building my new system or else I would done it my self to see what to expect.

I did some more research on your output that include "pgsql_host = localhost". I think you may have hit on something but I am not able to verify myself at this moment.

have "pgsql_host = localhost" is not the same as the link you provided. the "host" from the link you provided is refer to the "host = " inside the "parameters" block. The syntax pgsql_host = localhost mean it is

Code: Select all

pgsql localhost {
  host = localhost
  parameters {
    ...
  }
}

So it is at different level. However I suspect it may be a necessary even the document suggest it is not necessary when the pgsql <NAMED> { ... } this could be a bug. But I suspect there something more need to be defined, you can give it a try to see if it will work or produce some other kind of error.

[jankom]

Another step closer.
I've rearranged configuration hierarchy: nn more changes to the original 50-misc.conf file. Made settings only in dovecot.conf file.
Presently meil.err file no longer complains about list empty, no new entry there.
Also, success in

Code: Select all

andraslinux /var # doveadm user janos@andraslinux.jgklinux.jangkom.com
field   value
uid     5000
gid     5000
home    /var/vmail/maildir
mail_path       /var/vmail/maildir/mail
andraslinux /var #

However /var/vmail/ directory is still empty, and more importantly, mail client (evolution) is unable to send email. mai. log:has "SASL PLAIN authentication failed: (reason unavailable), sasl_username=janos@andraslinux.jgklinux.jangkom.com".
My dovecot configuration:

Code: Select all

janos@andraslinux ~ $ doveconf -n
# 2.4.2 (0962ed2104): /etc/dovecot/dovecot.conf
# OS: Linux 6.12.58-gentoo x86_64 Gentoo Base System release 2.18 
# Hostname: localhost
dovecot_config_version = 2.4.2
dovecot_storage_version = 2.4.2
mail_driver = maildir
mail_gid = vmail
mail_home = /var/vmail/%{user | domain}/%{user | username}
mail_path = ~/mail
mail_uid = vmail
protocols {
  imap = yes
  lmtp = yes
}
sql_driver = pgsql
namespace inbox {
  inbox = yes
  separator = /
}
passdb pam {
}
ssl_server {
  cert_file = /etc/ssl/dovecot/server.pem
  key_file = /etc/ssl/dovecot/server.key
}
service auth {
  unix_listener /var/spool/postfix/private/auth {
    group = postfix
    mode = 0660
    user = postfix
  }
  unix_listener auth-userdb {
    mode = 0600
    user = vmail
  }
}
pgsql pgsql {
  parameters {
    dbname = postfix
    host = localhost
    user = postfix
  }
}
passdb sql {
  auth_username_format = %{user | username}
  query = SELECT local_part AS username, domain, password FROM mailbox WHERE local_part = '%{user | username}' AND domain = '%{user | domain}'
}
userdb sql {
  auth_username_format = %{user | username}
  query = SELECT CONCAT ('/var/vmail/', 'maildir') AS home, 5000 AS uid, 5000 AS gid FROM mailbox WHERE local_part = '%{user | username}' AND domain = '%{user | domain}'
}
userdb static {
  fields {
    gid = vmail
    home = /var/vmail/%{user | domain}/%{user | username}
    uid = vmail
  }
}
janos@andraslinux ~ $ 

Maybe I need to make additional configuration settings for SASL.

jankom

[pingtoo]

jankom.

Congratulation, one step closer

It looks like I am chasing a running configuration situation. I just got my dovecot test VM up and was able to reproduce the error

Code: Select all

 Mar 28 18:19:08 localhost dovecot: auth: Fatal: passdb sql: sql pgsql: pgsql { .. } named list filter is missing

This happen when I intentionally mess around the pgsql block definition. Anyhow, you got it work, great!

I think possible when you emerge postfix you have USE flag "dovecot-sasl" turn on. so may be see Postfix and Dovecot SASL


Better open a new topic/thread since it is yet another kind of configuration and it is not related to how user/database configuration.


P.S. I am going back to my system build so it is unlikely I am able to test/debug. So I will leave it to you until you stuck and able to prove in a fixed state so I don't have to chase change configuration.

[jankom]

Yes - I've closed this topic because the postgres issue is now resolved with my latest dovecot.conf version. My thinking in solving it (lucky guess?) was reading and trying to interpret the mail log files. I had to add "pgsql pgsql{}" stanza which contains the "filter" that is the pgsql_parameters{} in it. I was confused with understanding the difference between "foo foo{}" and "foo_foo{}" constructs, statements in dovecot syntax.

The sasl issue was my fault. Yes, USE flags were OK, I messed up editing main.cf during the past few months, and did not discover it until all other questions were resolved.

Still not there, the /var/vmail/ directory is still empty, and my mail client cannot send/receive messages.

Working on that, and I may start a new topic related to this unfinished project.

jankom