Re: Example records for SQL AUTH

2014-11-05 Thread James 

On 03/11/2014 22:29, Jorge Bastos wrote:



Password_query:

password_query = select username, domain,password from users where
username='%u' and domain='%d' and active='true'



Try ... username='%n' ... not %u


From doc/dovecot/example-config/dovecot-sql.conf

# Commonly used available substitutions (see 
http://wiki.dovecot.org/Variables

# for full list):
#   %u = entire user@domain
#   %n = user part of user@domain
#   %d = domain part of user@domain




James.

Re: Example records for SQL AUTH

2014-11-04 Thread deoren 

On 11/3/2014 4:29 PM, Jorge Bastos wrote:
 Hi,



 Where can I get examples for the records for the users table?

If I understand your question properly and you're looking for examples
of creating new virtual users, then this guide covers that:

https://www.linode.com/docs/email/email-with-postfix-dovecot-and-mysql

You would have to adjust to fit your chosen schema of course as theirs
is a much simpler setup that excludes the uid, gid, and home values.


 For SHA512-CRYPT, I tried:



 replace into users values ('a...@a.com','a.com',ENCRYPT('b', CONCAT('$6$',
 SUBSTRING(SHA(RAND()), -16))),'',0,0,'true');



 schema is:

 CREATE TABLE `users` (

   `username` varchar(255) NOT NULL,

   `domain` varchar(255) NOT NULL,

   `password` varchar(255) NOT NULL,

   `home` varchar(255) NOT NULL,

   `uid` int(11) NOT NULL,

   `gid` int(11) NOT NULL,

   `active` enum('true','false') NOT NULL DEFAULT 'true',

   PRIMARY KEY (`username`)

 ) ENGINE=InnoDB DEFAULT CHARSET=utf8



 Password_query:

 password_query = select username, domain,password from users where
 username='%u' and domain='%d' and active='true'

What does your auth-sql-conf.ext file look like? With as much
information as you already have in your database schema you may want to
look at using the Prefetch userdb.

http://wiki2.dovecot.org/UserDatabase/Prefetch

P.S.

Apologies for the duplication. I forgot to reply to the list with my 
last response.

Example records for SQL AUTH

2014-11-03 Thread Jorge Bastos 
Hi,

 

Where can I get examples for the records for the users table?

For SHA512-CRYPT, I tried:

 

replace into users values ('a...@a.com','a.com',ENCRYPT('b', CONCAT('$6$',
SUBSTRING(SHA(RAND()), -16))),'',0,0,'true');

 

schema is:

CREATE TABLE `users` (

  `username` varchar(255) NOT NULL,

  `domain` varchar(255) NOT NULL,

  `password` varchar(255) NOT NULL,

  `home` varchar(255) NOT NULL,

  `uid` int(11) NOT NULL,

  `gid` int(11) NOT NULL,

  `active` enum('true','false') NOT NULL DEFAULT 'true',

  PRIMARY KEY (`username`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

Password_query:

password_query = select username, domain,password from users where
username='%u' and domain='%d' and active='true'

 

What could I be missing?

 

 

but logs says user is not found in userdb:

 

Nov  3 22:17:24 fastmail dovecot: auth: Error:
sql(a...@a.com,127.0.0.1,JIsTtvsGZwB/AAAB): user not found from userdb

Nov  3 22:17:24 fastmail dovecot: pop3: Error: Authenticated user not found
from userdb, auth lookup id=3938844673 (client-pid=10240 client-id=1)

Nov  3 22:17:24 fastmail dovecot: pop3-login: Internal login failure
(pid=10240 id=1) (internal failure, 1 successful auths): user=a...@a.com,
method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=10247, secured,
session=JIsTtvsGZwB/AAAB