What does `iterate_query` for SQL want as output?

[Coy Hile]

> On Aug 9, 2019, at 3:45 PM, Aki Tuomi <aki.tuomi at open-xchange.com> wrote:
> 
> 
>> On 09/08/2019 22:16 Coy Hile via dovecot <dovecot at 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 at REALM>, say ‘hile at 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 at 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 at coyhile.com@coyhile.com): Info: User no longer exists, skipping
>> [root at 81716ec5-bca4-6d53-ed81-bd1a55d46b4f /tmp]#
>> 
>> Note the extra “@coyhile.com” in there.
>> 
>> Thanks,
>> 
>> — 
>> Coy Hile
>> coy.hile at 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 at 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 at 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 at coyhile.com