On 09/08/2019 23:01 Coy Hile via dovecot < dovecot@dovecot.org> wrote:

On Aug 9, 2019, at 3:45 PM, Aki Tuomi < aki.tuomi@open-xchange.com> wrote:

On 09/08/2019 22:16 Coy Hile via dovecot < dovecot@dovecot.org> wrote:

Hi all,

In an earlier thread, https://dovecot.org/pipermail/dovecot/2019-August/116694.html I got a lot of useful help about migration. On my older host, everything was static; on the newer host, I’m storing user information in Postgres. usernames are of the form <login@REALM>, say ‘ hile@coyhile.com’ as basically a Kerberos principal, and authentication and individual lookups work.

My `users` table looks thus:

mail=> \d users

Table "public.users"

Column | Type | Modifiers

----------+------+-----------

username | text | not null

domain | text | not null

password | text | not null

mail=>

and contains, as an example:

username | domain | password

------------------+-------------+-------------------------------------------------------------------------------------------------------

hile@coyhile.com | coyhile.com | [REDACTED]

(1 row)

Naively, I’d expect something this to work for the iteration query:

iterate_query = SELECT username, domain FROM users

But, when I do that, I end up

doveadm backup -D -A -R -f ssh -i id_rsa.dsync imap01.coyhile.com /opt/local/bin/doveadm dsync-server -A

doveadm( hile@coyhile.com@coyhile.com): Info: User no longer exists, skipping

[root@81716ec5-bca4-6d53-ed81-bd1a55d46b4f /tmp]#

Note the extra “@coyhile.com” in there.

Thanks,

—

Coy Hile

coy.hile@coyhile.com

If your username field already contains domain, you do not need to return domain field separately. It is only needed if your username field only contains local part.

That’s what I thought, and a simpler query returns the data I expect:

mail=> select username from users;

username

------------------

hile@coyhile.com

(1 row)

mail=>

Or SELECT username AS user FROM users; (if the iterate query is the column to be named `user`?) When I configure the iterate_query to be SELET username AS user FROM users; I get this:

doveadm backup -D -A -R -f ssh -i id_rsa.dsync imap01.coyhile.com /opt/local/bin/doveadm dsync-server -A

Error: User listing returned failure

doveadm: Error: Failed to iterate through some users

dsync-local( hile@coyhile.com)<xktwERnPTV3dfwEAxHAMlw>: Error: read(remote) failed: EOF (version not received)

Which brings up two questions:

(1) Is there a way to get more useful debugging information than “failed to iterate through some users”? (FWIW there’s nothing relevant in syslog.)

(2) Is there a way to isolate and exercise just that particular bit so that I know I’m giving it what it expects?

The SQL documentation https://wiki.dovecot.org/AuthDatabase/SQL indicates that

iterate_query = SELECT username AS user FROM users

should return what it wants.

—

Coy Hile

coy.hile@coyhile.com

---
Aki Tuomi