Password database extra fields and SQL

Gregory Finch gfinch at
Fri Apr 29 14:32:37 UTC 2016

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

The "send" column is what I use to determine if "nologin" should be set.


-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 819 bytes
Desc: OpenPGP digital signature
URL: <>

More information about the dovecot mailing list