sqlite + IMAP Authentication issues - user unknown

[Aki Tuomi]

> On 25/05/2021 00:09 Scott Edwards <supadupa at gmail.com> wrote:
> 
>  
> Hello,
> I'm not on the maillist, please reply all.
> 
> It's been a few years since I've setup a mail server (I feel like a
> noob!). The following syslog message is isolating the auth issues. I
> have sqlite, and want to keep it this way. I don't see more than a few
> mailboxes to setup until sometime later, but if it does grow, I'll
> stand up PostgreSQL. FQDN is 'localcar.repair'.
> 
> May 24 21:01:45 localcar dovecot: auth: Debug: client in:
> AUTH#0116#011PLAIN#011service=imap#011secured=tls#011session=Rb8dshnD/Gwv2Ixo#011lip=45.33.2.221#011rip=47.216.140.104#011lport=993#011rport=27900#011local_name=localcar.repair#011ssl_cipher=TLS_AES_128_GCM_SHA256#011ssl_cipher_bits=128#011ssl_pfs=KxANY#011ssl_protocol=TLSv1.3#011resp=<hidden>
> May 24 21:01:49 localcar dovecot: auth-worker(20614): Debug:
> sql(webmaster-foo at localcar.repair,47.216.140.104,<Rb8dshnD/Gwv2Ixo>):
> query: SELECT password, userid as user FROM users WHERE userid =
> 'webmaster-foo' AND domain = 'localcar.repair'
> May 24 21:01:49 localcar dovecot: auth-worker(20614):
> sql(webmaster-foo at localcar.repair,47.216.140.104,<Rb8dshnD/Gwv2Ixo>):
> unknown user (given password: topsecret)
> May 24 21:01:51 localcar dovecot: auth: Debug: client passdb out:
> FAIL#0116#011user=webmaster-l5buyjuxao8 at localcar.repair#011original_user=webmaster-foo at localcar.repair
> May 24 21:01:51 localcar dovecot: imap-login: Debug: Ignoring unknown
> passdb extra field: original_user
> 
> 
> root at localcar:/etc/dovecot# sqlite3 users
> SQLite version 3.27.2 2019-02-25 16:06:06
> Enter ".help" for usage hints.
> sqlite> .schema
> CREATE TABLE users (
>   userid VARCHAR(128) NOT NULL,
>   domain VARCHAR(128) NOT NULL,
>   password VARCHAR(64) NOT NULL,
>   home VARCHAR(255) NOT NULL,
>   uid INTEGER NOT NULL,
>   gid INTEGER NOT NULL
> );
> sqlite> select * from users;
> webmaster-foo|localcar.repair|topsecret|/tmp|1000|1000
> 
> Thunderbird naturally says "Login to server localcar.repair with
> username webmaster-foo at localcar.repair failed.
> 

Can you show dovecot-sql.conf.ext?

Aki