Server error from ULI link when running Postgres [#2851352]

Problem/Motivation

When the password_policy module is enabled in a site using Postgres instead of mysql, the function _password_policy_was_user_blocked_due_to_expiration() causes a server error, as it attempts to use an alias in the value of a WHERE clause, which is disallowed in Postgres.

Admittedly this is a niche issue, as relatively few Drupal sites run on Postgres.

Proposed resolution

Replace the relevant implementation of selectquery::condition method with an implementation of the selectquery::having method.

Remaining tasks

Patch forthcoming.

Comment	File	Size	Author
#5	password_policy-alter_efq_for_postgres_compatibility-2851352-5.patch	561 bytes	COBadger
#5	7.x-1.x: PHP 5.3 & MySQL 5.5, D7 42 pass, 3 fail PHP 5.5 & PostgreSQL 9.1, D7 42 pass, 3 fail
#2	password_policy-alter_efq_for_postgres_compatibility-2851352-2.patch	561 bytes	COBadger
#2	7.x-1.x: PHP 5.3 & MySQL 5.5, D7 40 pass, 4 fail

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

10 February 2017 at 05:09

COBadger created an issue. See original summary.

Comment #2

COBadger CreditAttribution: COBadger as a volunteer commented 10 February 2017 at 05:12

File	Size
password_policy-alter_efq_for_postgres_compatibility-2851352-2.patch	561 bytes
7.x-1.x: PHP 5.3 & MySQL 5.5, D7 40 pass, 4 fail

Comment #3

COBadger CreditAttribution: COBadger as a volunteer commented 10 February 2017 at 05:14

Status:

Active

» Needs review

Comment #4

10 February 2017 at 05:18

Status:

Needs review

» Needs work

The last submitted patch, 2: password_policy-alter_efq_for_postgres_compatibility-2851352-2.patch, failed testing.

Comment #5

COBadger CreditAttribution: COBadger as a volunteer commented 10 February 2017 at 18:02

Version:	7.x-1.12	» 7.x-1.x-dev
Assigned:	COBadger	» Unassigned
Status:	Needs work	» Needs review

File	Size
password_policy-alter_efq_for_postgres_compatibility-2851352-5.patch	561 bytes
7.x-1.x: PHP 5.3 & MySQL 5.5, D7 42 pass, 3 fail PHP 5.5 & PostgreSQL 9.1, D7 42 pass, 3 fail

Comment #6

10 February 2017 at 20:26

Status:

Needs review

» Needs work

The last submitted patch, 5: password_policy-alter_efq_for_postgres_compatibility-2851352-5.patch, failed testing.

Comment #7

AohRveTPV CreditAttribution: AohRveTPV commented 8 March 2017 at 16:11

COBadger, could you please help me to better understand this bug? What do you mean by "it attempts to use an alias in the value of a WHERE clause"? Both 'blocked' and 'unblocked' are columns of {password_policy_expiration}. How is an alias involved?

Comment #8

AohRveTPV CreditAttribution: AohRveTPV commented 8 March 2017 at 16:24

Status:

Needs work

» Postponed (maintainer needs more info)

I executed _password_policy_was_user_blocked_due_to_expiration() on a site using PostgreSQL 9.4 and Password Policy 7.x-1.x-dev without error. I did so by:
1. As administrator (uid 1), created an expiration policy that applies to 'authenticated user'.
2. Created a new user 'foo'.
3. Blocked user ' foo'.

This caused _password_policy_was_user_blocked_due_to_expiration() to be executed, but I see no error.

So, I cannot reproduce this bug. Can you please give steps to reproduce it?