Password database extra fields and SQL

[Gregory Finch]

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 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?
>
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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 819 bytes
Desc: OpenPGP digital signature
URL: <http://dovecot.org/pipermail/dovecot/attachments/20160429/a2e67efd/attachment.sig>