0

I have seperate User and Domains tables. When a domain is marked as not active I want all the users for that domain to fail auth.

This query works fine.

password_query = \
         SELECT CONCAT("5500") AS gid,  CONCAT("5500") AS uid, \
         CONCAT("/mail/dir/",'%d','/','%n') AS home,Email as User, Password as password \
         FROM users_table  WHERE Email='%u' AND active=1;

Now I'm trying to add a check to see if the domain is marked at active. Adding SELECT DomainName FROM domains_table UNION. (Which I will extend later) But when I add this, the query fails. The table names and headers are correct. Why would this line cause the query to fail?

In addition, are conditional statements supported here? I would like to add IF (domain is active) THEN (pass query)

password_query = \
         SELECT DomainName FROM domains_table \
         UNION SELECT CONCAT("5500") AS gid,  CONCAT("5500") AS uid, \
         CONCAT("/mail/dir/",'%d','/','%n') AS home,Email as User, Password as password \
         FROM users_table  WHERE Email='%u' AND active=1;

Each query seems to only work if they return gid, uid, home, user & password variables.

Thanks

Grandmaster Og
  • 1
  • 1

1 Answers1

0

Resolved the issue by doing the following.

  1. Enabled auth-deny.conf.ext by uncommenting it in dovecot.conf

  2. Change auth-deny.conf.ext to use sql driver and added a new file as arg.

  3. in the new file added another password_query with the following code that returns a deny variable

    password_query =
    SELECT ('%u') AS deny FROM domain_table WHERE DomainName ='%d' AND active=0 ;

This seems like a bit of a hack, in the logs it says Password query must return a field named 'password' was the reason the auth fails. But it does work. Anyone have a better way?

Grandmaster Og
  • 1
  • 1