>>>>> "Aki" == Aki Tuomi <aki.tu...@open-xchange.com> writes:
> iterate_query only works with
> userdb {
> driver = sql
> args = /path/to/auth-sql.conf.ext
> }
Thanks, that was the key part I was missing! Can you maybe think to
update the code to give a more useful error message, or even a warning
on startup which says something like: 'static driver does not support
iterating users'? I tried looking at the source code, but it's going
to take me quite a while to wrap my brain around how it's structured
and how error messages propogate.
Even just listing which userdb block failed would be a help, since you
can have multiple ones defined.
In any case, I've got it working now once I updated both areas in my
configuration which referred to the 'static' driver.
Thanks, really appreciate your help and all the work you guys do on
this software!
John
>> On 02/12/2023 00:02 EET John Stoffel <j...@stoffel.org> wrote:
>>
>>
>> >>>>> "John" == John Stoffel <j...@stoffel.org> writes:
>>
>> Do I think I'm on the right track here, since I removed the following
>> from /etc/dovecot/conf.d/auth-sql.conf.ext
>>
>> #userdb {
>> # driver = static
>> # args = uid=mail gid=mail home=/var/mail/%d/%n
>> #}
>>
>> So now my error is as follows:
>>
>> # doveadm user -u '*'
>> Error: auth-master: userdb list: User listing returned failure
>> Fatal: user listing failed
>>
>> Because now when I restart dovecot, I see the following in the log:
>>
>> Dec 01 16:55:14 master: Info: Dovecot v2.3.21 (47349e2482) starting up
>> for imap, lmtp, sieve (core dumps disabled)
>> Dec 01 16:55:14 auth: Warning: sql: Ignoring changed iterate_query in
>> /etc/dovecot/dovecot-sql.conf.ext, because userdb sql not used. (If
>> this is intentional, set userdb_warning_disable=yes)
>> Dec 01 16:55:14 auth: Error: auth-master client: Trying to iterate
>> users, but userdbs don't support it (created 0 msecs ago, handshake 0
>> msecs ago)
>>
>> So I commented out my 'iterate_query = ...' (see below) from
>> /etc/postfix/dovecot-sql.conf.ext and now I get the error on startup
>> which says:
>>
>> Dec 01 16:57:42 master: Info: Dovecot v2.3.21 (47349e2482) starting up
>> for imap, lmtp, sieve (core dumps disabled)
>> Dec 01 16:57:42 auth: Error: auth-master client: Trying to iterate
>> users, but userdbs don't support it (created 0 msecs ago, handshake 0
>> msecs ago)
>> Dec 01 16:57:42 replicator: Error: auth-master: userdb list: User
>> listing returned failure
>> Dec 01 16:57:42 replicator: Error: listing users failed, can't
>> replicate existing data
>>
>> Which tells me I need the iteracte_users setting, but I've got a bogus
>> query in there. So I think I should be using something like this:
>>
>> iterate_query = SELECT email AS user from virtual_users;
>>
>> where 'virtual_users' is the one and only table in my sqlite db file.
>> And I'm just returning the 'email' column as 'user', since that's what
>> it seems to expect.
>>
>> Hmmm...
>>
>>
>> > I've been pounding my head against the sand for a while here trying to
>> > figure out why I can't get:
>>
>> > doveadm user '*'
>>
>> > working properly. I've got a Debian 11 VPS runnig dovecot version
>> > 2.3.21-1+debian10 and it works great. But not I'm trying to add in
>> > simple replication to a home dovecot instance over a wireguard tunnel
>> > so I can do backups and have a little better resiliency. Maybe.
>>
>> > In any case, my sqlite schema looks like this:
>>
sqlite> .schema virtual_users
>> > CREATE TABLE `virtual_users` (
>> > `id` integer NOT NULL PRIMARY KEY AUTOINCREMENT
>> > , `domain_id` integer NOT NULL
>> > , `password` varchar(106) NOT NULL
>> > , `email` varchar(100) NOT NULL
>> > , UNIQUE (`email`)
>> > , CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`)
>> > REFERENCES `virtual_domains` (`id`) E
>> > );
>> > CREATE INDEX "idx_virtual_users_domain_id" ON "virtual_users"
>> > (`domain_id`);
>>
>>
>> > and I don't have any other tables. The 'domain_id' was/is a leftover
>> > from my thinking I needed it for extra testing of other domains and
>> > such.
>>
>> > I can do 'doveadm user j...@stoffel.org' and it works just fine. When
>> > I do "doveadm user '*'" it fails and I get:
>>
>> > doveadm user '*'
>> > Error: auth-master: userdb list: User listing returned failure
>> > Fatal: user listing failed
>>
>>
>> > So my config looks like this:
>>
>> > root@mail:/etc/dovecot/conf.d# cat auth-sql.conf.ext
>> > # Authentication for SQL users. Included from 10-auth.conf.
>> > #
>> > # <doc/wiki/AuthDatabase.SQL.txt>
>>
>> > passdb {
>> > driver = sql
>>
>> > # Path for SQL configuration file, see
>> > example-config/dovecot-sql.conf.ext
>> > args = /etc/dovecot/dovecot-sql.conf.ext
>> > }
>>
>> > userdb {
>> > driver = static
>> > args = uid=mail gid=mail home=/var/mail/%d/%n
>> > }
>>
>> > My /etc/dovecot/dovecot-sql.conf.ext has the following:
>>
>> > driver = sqlite
>> > connect = /etc/dovecot/private/virtual_users.sqlite3
>>
>> > default_pass_scheme = SHA512-CRYPT
>>
>> > password_query = SELECT '/var/mail/%d/%u' AS userdb_home, 'mail' AS
>> > userdb_uid, 'mail' AS userdb_gid, email as user, password FROM
>> > virtual_users WHERE email='%u';
>>
>> > iterate_query = SELECT email AS user from virtual_users;
>>
>> > And my general doveadm config output is this, slightly edited down to
>> > remove stuff I don't think I need to show is at the end. Any hints on
>> > what I've done wrong here? Do I need a more complete sqlite3 schema?
>> > I wish I could get more debugging info on what query it's trying to
>> > run and the error(s) it's getting.
>>
>> > Thanks,
>> > John
>>
>>
>>
>> > # 2.3.21 (47349e2482): /etc/dovecot/dovecot.conf
>> > # Pigeonhole version 0.5.21 (f6cd4b8e)
>> > # OS: Linux 5.10.0-26-amd64 x86_64 Debian 11.8 ext4
>> > # Hostname: localhost
>> > # NOTE: Send doveconf -n output instead when asking for help.
>> > auth_anonymous_username = anonymous
>> > auth_cache_negative_ttl = 1 hours
>> > auth_cache_size = 0
>> > auth_cache_ttl = 1 hours
>> > auth_cache_verify_password_with_worker = no
>> > auth_debug = no
>> > auth_debug_passwords = no
>> > auth_failure_delay = 2 secs
>> > auth_gssapi_hostname =
>> > auth_krb5_keytab =
>> > auth_master_user_separator =
>> > auth_mechanisms = plain login
>> > auth_policy_check_after_auth = yes
>> > auth_policy_check_before_auth = yes
>> > auth_policy_hash_mech = sha256
>> > auth_policy_hash_nonce =
>> > auth_policy_hash_truncate = 12
>> > auth_policy_log_only = no
>> > auth_policy_reject_on_fail = no
>> > auth_policy_report_after_auth = yes
>> > auth_policy_request_attributes = login=%{requested_username}
>> > pwhash=%{hashed_password} remote=%{rip} device_id=%{client_id} protocol=%s
>> > session_id=%{session}
>> > auth_policy_server_api_header =
>> > auth_policy_server_timeout_msecs = 2000
>> > auth_policy_server_url =
>> > auth_proxy_self =
>> > auth_realms =
>> > auth_socket_path = auth-userdb
>> > auth_ssl_require_client_cert = no
>> > auth_ssl_username_from_cert = no
>> > auth_stats = no
>> > auth_use_winbind = no
>> > auth_username_chars =
>> > abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890.-_@
>> > auth_username_format = %Lu
>> > auth_username_translation =
>> > auth_verbose = no
>> > auth_verbose_passwords = no
>> > auth_winbind_helper_path = /usr/bin/ntlm_auth
>> > auth_worker_max_count = 30
>> > base_dir = /run/dovecot
>> > config_cache_size = 1 M
>> > debug_log_path =
>> > default_client_limit = 1000
>> > default_idle_kill = 1 mins
>> > default_internal_group = dovecot
>> > default_internal_user = dovecot
>> > default_login_user = dovenull
>> > default_process_limit = 100
>> > default_vsz_limit = 256 M
>> > deliver_log_format = msgid=%m: %$
>> > dict_db_config =
>> > disable_plaintext_auth = yes
>> > dotlock_use_excl = yes
>> > doveadm_allowed_commands =
>> > doveadm_api_key =
>> > doveadm_http_rawlog_dir =
>> > doveadm_password =
>> > doveadm_port = 0
>> > doveadm_socket_path = doveadm-server
>> > doveadm_ssl = no
>> > doveadm_username = doveadm
>> > doveadm_worker_count = 0
>> > first_valid_gid = 1
>> > first_valid_uid = 0
>> > import_environment = TZ CORE_OUTOFMEM CORE_ERROR LISTEN_PID LISTEN_FDS
>> > NOTIFY_SOCKET
>> > info_log_path =
>> > libexec_dir = /usr/lib/dovecot
>> > listen = *
>> > log_core_filter =
>> > log_debug =
>> > log_path = /var/log/dovecot.log
>> > log_timestamp = "%b %d %H:%M:%S "
>> > mail_access_groups =
>> > mail_always_cache_fields =
>> > mail_attachment_detection_options =
>> > mail_attachment_dir =
>> > mail_attachment_fs = sis posix
>> > mail_attachment_hash = %{sha1}
>> > mail_attachment_min_size = 128 k
>> > mail_attribute_dict =
>> > mail_cache_fields = flags
>> > mail_chroot =
>> > mail_debug = no
>> > mail_fsync = optimized
>> > mail_full_filesystem_access = no
>> > mail_gid =
>> > mail_home =
>> > mail_location = maildir:/var/mail/%d/%n/Maildir
>> > mail_log_prefix = "%s(%u)<%{pid}><%{session}>: "
>> > mail_max_keyword_length = 50
>> > mail_max_lock_timeout = 0
>> > mail_max_userip_connections = 10
>> > mail_never_cache_fields = imap.envelope
>> > mail_nfs_index = no
>> > mail_nfs_storage = no
>> > mail_plugin_dir = /usr/lib/dovecot/modules
>> > mail_plugins = " notify replication"
>> > mail_prefetch_count = 0
>> > mail_privileged_group = mail
>> > mail_save_crlf = no
>> > mail_server_admin =
>> > mail_server_comment =
>> > mail_shared_explicit_inbox = no
>> > mail_sort_max_read_count = 0
>> > mail_temp_dir = /tmp
>> > mail_temp_scan_interval = 1 weeks
>> > mail_uid =
>> > mail_vsize_bg_after_count = 0
>> > passdb {
>> > args = /etc/dovecot/dovecot-sql.conf.ext
>> > auth_verbose = default
>> > default_fields =
>> > deny = no
>> > driver = sql
>> > master = no
>> > mechanisms =
>> > name =
>> > override_fields =
>> > pass = no
>> > result_failure = continue
>> > result_internalfail = continue
>> > result_success = return-ok
>> > skip = never
>> > username_filter =
>> > }
>> > protocols = imap lmtp sieve
>> > recipient_delimiter = +_
>> > service auth-worker {
>> > chroot =
>> > client_limit = 1
>> > drop_priv_before_exec = no
>> > executable = auth -w
>> > extra_groups =
>> > group =
>> > idle_kill = 0
>> > privileged_group =
>> > process_limit = 0
>> > process_min_avail = 0
>> > protocol =
>> > service_count = 0
>> > type = worker
>> > unix_listener auth-worker {
>> > group =
>> > mode = 0600
>> > user = $default_internal_user
>> > }
>> > user = mail
>> > vsz_limit = 18446744073709551615 B
>> > }
>> > service auth {
>> > chroot =
>> > client_limit = 0
>> > drop_priv_before_exec = no
>> > executable = auth
>> > extra_groups =
>> > group =
>> > idle_kill = 0
>> > privileged_group =
>> > process_limit = 1
>> > process_min_avail = 0
>> > protocol =
>> > service_count = 0
>> > type =
>> > unix_listener /var/spool/postfix/private/auth {
>> > group = postfix
>> > mode = 0666
>> > user = postfix
>> > }
>> > unix_listener auth-client {
>> > group =
>> > mode = 0600
>> > user = $default_internal_user
>> > }
>> > unix_listener auth-login {
>> > group =
>> > mode = 0600
>> > user = $default_internal_user
>> > }
>> > unix_listener auth-master {
>> > group =
>> > mode = 0600
>> > user =
>> > }
>> > unix_listener auth-userdb {
>> > group =
>> > mode = 0666
>> > user = mail
>> > }
>> > unix_listener login/login {
>> > group =
>> > mode = 0666
>> > user =
>> > }
>> > unix_listener token-login/tokenlogin {
>> > group =
>> > mode = 0666
>> > user =
>> > }
>> > user = dovecot
>> > vsz_limit = 18446744073709551615 B
>> > }
>> > service doveadm {
>> > chroot =
>> > client_limit = 1
>> > drop_priv_before_exec = no
>> > executable = doveadm-server
>> > extra_groups = $default_internal_group
>> > group =
>> > idle_kill = 0
>> > privileged_group =
>> > process_limit = 0
>> > process_min_avail = 0
>> > protocol =
>> > service_count = 1
>> > type =
>> > unix_listener doveadm-server {
>> > group =
>> > mode = 0600
>> > user =
>> > }
>> > user =
>> > vsz_limit = 18446744073709551615 B
>> > }
>> > service log {
>> > chroot =
>> > client_limit = 0
>> > drop_priv_before_exec = no
>> > executable = log
>> > extra_groups =
>> > group =
>> > idle_kill = 4294967295 secs
>> > privileged_group =
>> > process_limit = 1
>> > process_min_avail = 0
>> > protocol =
>> > service_count = 0
>> > type = log
>> > unix_listener log-errors {
>> > group =
>> > mode = 0600
>> > user =
>> > }
>> > user =
>> > vsz_limit = 18446744073709551615 B
>> > }
>> > state_dir = /var/lib/dovecot
>> > stats_http_rawlog_dir =
>> > syslog_facility = mail
>> > userdb {
>> > args = uid=mail gid=mail home=/var/mail/%d/%n
>> > auth_verbose = default
>> > default_fields =
>> > driver = static
>> > name =
>> > override_fields =
>> > result_failure = continue
>> > result_internalfail = continue
>> > result_success = return-ok
>> > skip = never
>> > }
>> > _______________________________________________
>> > dovecot mailing list -- dovecot@dovecot.org
>> > To unsubscribe send an email to dovecot-le...@dovecot.org
>> _______________________________________________
>> dovecot mailing list -- dovecot@dovecot.org
>> To unsubscribe send an email to dovecot-le...@dovecot.org
_______________________________________________
dovecot mailing list -- dovecot@dovecot.org
To unsubscribe send an email to dovecot-le...@dovecot.org
