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
> 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.comIf 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. Aki
> 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