> 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
<!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>
> > Did you check your logs? >that?s just it. There?s nothing in syslog (which is logging at mail.debug). Not just nothing useful, absolutely _nothing_ logged when I run that. All I see is this (printed, I presume to STDERR): 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)<gCspG6m8Tl3bJQEAxHAMlw>: Error: read(remote) failed: EOF (version not received) The last line makes sense given the earlier failure. Should this log somewhere else besides syslog? And is there some way I can isolate and test _just_ the user iteration and enumeration code to prove out that the query is correct? Clearly, from output above, it should be. Thanks, -c