dovecot - Feb 2014 - sqlite queries for master user

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.

Alternatively some other parameter in the passdb segment above to tell 
the other login to miss the deny stage.

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