Password database extra fields and SQL
Hi everyone,
first post to the list, be gentle with me!
Perhaps I'm missing something here, but it appears to me that many password database extra fields currently aren't much use inside SQL queries?
All boolean fields like nologin/nodelay/nopassword are set if the column is present in the returned query, regardless of value (including NULL)
For example, say you have a query like:
password_query = SELECT hash AS password, NULLIF(active = 1, 'y') AS nologin FROM users WHERE name = '%u'
Then nologin will always be set for this account, there's no realistic way to toggle the flag.
Has anyone come across any workarounds for this problem that don't sacrifice efficiency by performing multiple lookups?
I was considering what would happen if dovecot treated NULL fields as not present, but that immediately conflicts with advice to set password to NULL to satisfy the field requirement on nopassword. Any thoughts?
-- Dave
Am 29.04.2016 um 15:49 schrieb Dave:
For example, say you have a query like:
password_query = SELECT hash AS password, NULLIF(active = 1, 'y') AS nologin FROM users WHERE name = '%u'
why not using a logic like this to block users that are not active:
password_query = SELECT username as user, password as password,
homedir AS userdb_home, maildir AS userdb_mail,
concat('*:bytes=', quota) as userdb_quota_rule, uid as
userdb_uid, gid as userdb_gid
FROM mailbox
WHERE username = '%Lu' AND active = '1'
AND ( access_restriction = 'ALL' OR LOCATE( '%Us',
access_restriction ) > 0 )
Gruß Matthias
--
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning." -- Rich Cook
On 29/04/2016 14:57, Matthias Fechner wrote:
Am 29.04.2016 um 15:49 schrieb Dave:
For example, say you have a query like:
password_query = SELECT hash AS password, NULLIF(active = 1, 'y') AS nologin FROM users WHERE name = '%u'
why not using a logic like this to block users that are not active: ... WHERE username = '%Lu' AND active = '1'
...
Hi Matthias,
that's the logic I'm currently using, but being unable to use nologin means having no simple way to communicate that the account is explicitly locked rather than auth failed / not present. It seems a bit of a hack!
It also doesn't help with other boolean fields, current or future. For example nodelay would be useful if it could be selectively set on query for, say, webmail access.
-- Dave
On 2016-04-29 6:49 AM, Dave wrote:
Hi everyone,
first post to the list, be gentle with me! Perhaps I'm missing something here, but it appears to me that manypassword database extra fields currently aren't much use inside SQL queries?
All boolean fields like nologin/nodelay/nopassword are set if the column is present in the returned query, regardless of value (including NULL)
For example, say you have a query like:
password_query = SELECT hash AS password, NULLIF(active = 1, 'y') AS nologin FROM users WHERE name = '%u'
Then nologin will always be set for this account, there's no realistic way to toggle the flag.
Has anyone come across any workarounds for this problem that don't sacrifice efficiency by performing multiple lookups?
I was considering what would happen if dovecot treated NULL fields as not present, but that immediately conflicts with advice to set password to NULL to satisfy the field requirement on nopassword. Any thoughts?
My password query works as follows with PostgreSQL and dovecot 20140806 (don't remember what version that is):
password_query = SELECT userid AS username, passwd AS password, case when send=true then null else true end as nologin FROM users WHERE userid='%n'
The "send" column is what I use to determine if "nologin" should be set.
Greg
participants (3)
-
Dave
-
Gregory Finch
-
Matthias Fechner