<!doctype html>
<html>
<head>
<meta charset="UTF-8">
</head>
<body>
<div>
<br>
</div>
<blockquote type="cite">
<div>
On 09/08/2019 23:01 Coy Hile via dovecot <
<a href="mailto:dovecot@dovecot.org">dovecot@dovecot.org</a>> wrote:
</div>
<div>
<br>
</div>
<div>
<br>
</div>
<blockquote type="cite">
<div>
On Aug 9, 2019, at 3:45 PM, Aki Tuomi <
<a href="mailto:aki.tuomi@open-xchange.com">aki.tuomi@open-xchange.com</a>> wrote:
</div>
<div>
<br>
</div>
<div>
<br>
</div>
<blockquote type="cite">
<div>
On 09/08/2019 22:16 Coy Hile via dovecot <
<a href="mailto:dovecot@dovecot.org">dovecot@dovecot.org</a>> wrote:
</div>
<div>
<br>
</div>
<div>
<br>
</div>
<div>
Hi all,
</div>
<div>
<br>
</div>
<div>
In an earlier thread,
<a href="https://dovecot.org/pipermail/dovecot/2019-August/116694.html" rel="noopener" target="_blank">https://dovecot.org/pipermail/dovecot/2019-August/116694.html</a> 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 ‘
<a href="mailto:hile@coyhile.com">hile@coyhile.com</a>’ as basically a Kerberos principal, and authentication and individual lookups work.
</div>
<div>
<br>
</div>
<div>
My `users` table looks thus:
</div>
<div>
<br>
</div>
<div>
mail=> \d users
</div>
<div>
Table "public.users"
</div>
<div>
Column | Type | Modifiers
</div>
<div>
----------+------+-----------
</div>
<div>
username | text | not null
</div>
<div>
domain | text | not null
</div>
<div>
password | text | not null
</div>
<div>
<br>
</div>
<div>
mail=>
</div>
<div>
<br>
</div>
<div>
and contains, as an example:
</div>
<div>
<br>
</div>
<div>
username | domain | password
</div>
<div>
------------------+-------------+-------------------------------------------------------------------------------------------------------
</div>
<div>
<a href="mailto:hile@coyhile.com">hile@coyhile.com</a> | coyhile.com | [REDACTED]
</div>
<div>
(1 row)
</div>
<div>
<br>
</div>
<div>
Naively, I’d expect something this to work for the iteration query:
</div>
<div>
<br>
</div>
<div>
iterate_query = SELECT username, domain FROM users
</div>
<div>
<br>
</div>
<div>
<br>
</div>
<div>
But, when I do that, I end up
</div>
<div>
<br>
</div>
<div>
doveadm backup -D -A -R -f ssh -i id_rsa.dsync imap01.coyhile.com /opt/local/bin/doveadm dsync-server -A
</div>
<div>
doveadm(
<a href="mailto:hile@coyhile.com">hile@coyhile.com</a>@coyhile.com): Info: User no longer exists, skipping
</div>
<div>
[root@81716ec5-bca4-6d53-ed81-bd1a55d46b4f /tmp]#
</div>
<div>
<br>
</div>
<div>
Note the extra “@coyhile.com” in there.
</div>
<div>
<br>
</div>
<div>
Thanks,
</div>
<div>
<br>
</div>
<div>
—
</div>
<div>
Coy Hile
</div>
<div>
<a href="mailto:coy.hile@coyhile.com">coy.hile@coyhile.com</a>
</div>
</blockquote>
<div>
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.
</div>
</blockquote>
<div>
That’s what I thought, and a simpler query returns the data I expect:
</div>
<div>
<br>
</div>
<div>
mail=> select username from users;
</div>
<div>
username
</div>
<div>
------------------
</div>
<div>
<a href="mailto:hile@coyhile.com">hile@coyhile.com</a>
</div>
<div>
(1 row)
</div>
<div>
<br>
</div>
<div>
mail=>
</div>
<div>
<br>
</div>
<div>
<br>
</div>
<div>
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:
</div>
<div>
<br>
</div>
<div>
doveadm backup -D -A -R -f ssh -i id_rsa.dsync imap01.coyhile.com /opt/local/bin/doveadm dsync-server -A
</div>
<div>
Error: User listing returned failure
</div>
<div>
doveadm: Error: Failed to iterate through some users
</div>
<div>
dsync-local(
<a href="mailto:hile@coyhile.com">hile@coyhile.com</a>)<xktwERnPTV3dfwEAxHAMlw>: Error: read(remote) failed: EOF (version not received)
</div>
<div>
<br>
</div>
<div>
<br>
</div>
<div>
Which brings up two questions:
</div>
<div>
(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.)
</div>
<div>
(2) Is there a way to isolate and exercise just that particular bit so that I know I’m giving it what it expects?
</div>
<div>
<br>
</div>
<div>
The SQL documentation
<a href="https://wiki.dovecot.org/AuthDatabase/SQL" rel="noopener" target="_blank">https://wiki.dovecot.org/AuthDatabase/SQL</a> indicates that
</div>
<div>
<br>
</div>
<div>
iterate_query = SELECT username AS user FROM users
</div>
<div>
<br>
</div>
<div>
should return what it wants.
</div>
<div>
<br>
</div>
<div>
—
</div>
<div>
Coy Hile
</div>
<div>
<a href="mailto:coy.hile@coyhile.com">coy.hile@coyhile.com</a>
</div>
</blockquote>
<div>
<br>
</div>
<div>
Did you check your logs?
</div>
<div class="io-ox-signature">
<pre>---
Aki Tuomi</pre>
</div>
</body>
</html>