[Dovecot] sqlite queries for master user

[Alan Chandler]

On 13/02/14 22:43, Alan Chandler wrote:
> Hi
>
> I am trying to achieve the following functionality
>
> "usera" can't login because his account is set non active.  there is a 
> deny query that checks this.
>
> a master user ("masteruser") should be able login using 
> "usera*masteruser" syntax to this disabled account
>
> I just tried it with auth_debug set on, but the deny query is being 
> run for usera, presumably because my master definition is
>
> passdb {
>   driver = sql
>   master = yes
>   args = /etc/dovecot/sqlite-master.conf
>
>   # Unless you're using PAM, you probably still want the destination 
> user to
>   # be looked up from passdb that it really exists. pass=yes does that.
>   pass = yes
> }
>
> with pass=yes, and that causes usera to be validated through both the 
> deny and accept phase of authorisation.
>
>
> In the password_query defined in sqlite-master.conf I user '%u' to 
> locate my master user.  Is there any variable I could use to (in that 
> query) to also check that "usera" exists? I could then remove the pass 
> = yes, both to avoid two more queries and to avoid the trap that this 
> account is locked out.
>

To answer my own question - I found the variable in the wiki in the end 
%{login_user}

At first it didn't work even then, because it was still trying to find 
stuff from the userdb, but when I added a userdb_uid (which was all it 
was looking for) it worked a treat.

To be more specific - my normal query to look up users is

  SELECT m.name AS user, u.password AS password, u.uid+10000 AS userdb_uid \
             FROM mailaccount m INNER JOIN user u ON m.username = 
u.username \
             WHERE m.name = '%u'

(I have a common user who could have several mail accounts = they will 
be all in the virtual uid of the user, not his account.)

I took the master stanza above and removed the pass=yes

and changed the master query to be

SELECT u.password AS password,l.uid+10000 as userdb_uid FROM user u \
             INNER JOIN capability c ON u.username = c.username \
             ,mailaccount m JOIN user l ON m.username =l.username \
             WHERE c.role = 'masteruser' and u.username = '%u' AND 
m.name = '%{login_user}'

and this seemed to work nicely.

Alan Chandler