>>>>> "Aki" == Aki Tuomi via dovecot <dovecot@dovecot.org> writes:
That did the trick. Thanks! Maybe the docs can be updated to make
this crystal clear? Especially the section which talks about using
the static driver with userdb and how it doesn't allow the use of
interate_query.
> iterate_query only works with
> userdb {
> driver = sql
> args = /path/to/auth-sql.conf.ext
> }
> Aki
>> 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
_______________________________________________
dovecot mailing list -- dovecot@dovecot.org
To unsubscribe send an email to dovecot-le...@dovecot.org
