Why password_auditor table not showing the password expired even after password_life_time ends?

Hi,

What client tool is being used? vsql, ODBC, JDBC, ADO.net?

Works okay in 9.0.1 (At least when the client is VSQL):

dbadmin=> select version();
              version
------------------------------------
 Vertica Analytic Database v9.0.1-3
(1 row)

dbadmin=> select user_name,password_create_time,is_current_password,profile_name from passwords where user_name ilike 'jim';
 user_name |     password_create_time      | is_current_password | profile_name
-----------+-------------------------------+---------------------+--------------
 jim       | 2018-02-21 14:50:50.825673-05 | t                   | jim_profile
(1 row)

dbadmin=> select profile_name,password_life_time from profiles where profile_name = 'jim_profile';
 profile_name | password_life_time
--------------+--------------------
 jim_profile  | 1
(1 row)

dbadmin=> select user_name,acctexpired from password_auditor where user_name ilike 'jim';
 user_name | acctexpired
-----------+-------------
 jim       | f
(1 row)

dbadmin=> select sysdate;
          sysdate
----------------------------
 2018-03-31 10:52:21.949443
(1 row)

dbadmin=> \q

[dbadmin@s18384357 ~]$ vsql -U jim -w jim1
The password has expired.

Changing password for jim
New password:

Note that the ACCTEXPIRED field in the PASSWORD_AUDITOR indicates if the current password has been forced to expire by superuser.

Example:

[dbadmin@s18384357 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbadmin=> select user_name,acctexpired from password_auditor where user_name ilike 'jim';
 user_name | acctexpired
-----------+-------------
 jim       | f
(1 row)

dbadmin=> alter user jim password expire;
ALTER USER

dbadmin=> select user_name,acctexpired from password_auditor where user_name ilike 'jim';
 user_name | acctexpired
-----------+-------------
 jim       | t
(1 row)

Hi Jim,

Thanks for the revert.
Currently we are using vertica version 7.2.3-15.
Users in my organization uses dbvisulizer and SAS and etc for database access. Some users getting password expire details in dbvizulizer connection message panel but some users are not getting, may be due to password_auditor table not showing as expire.
As of now we are checking password create time manually.
Hope it won't repeat in future versions.

Hi,

Glad to hear that you have a update plan!!!

Fyi... I was able to test with 7.2.3-26.

dbadmin=> select version();
               version
-------------------------------------
 Vertica Analytic Database v7.2.3-26
(1 row)

dbadmin=> select user_name,password_create_time,is_current_password,profile_name from passwords where user_name ilike 'jim';
 user_name |     password_create_time      | is_current_password | profile_name
-----------+-------------------------------+---------------------+--------------
 jim       | 2018-03-31 11:15:46.447622-04 | t                   | jim_profile
(1 row)

dbadmin=> select profile_name,password_life_time from profiles where profile_name = 'jim_profile';
 profile_name | password_life_time
--------------+--------------------
 jim_profile  | 1
(1 row)

dbadmin=> select sysdate;
          sysdate
----------------------------
 2018-04-01 11:25:37.518812
(1 row)

dbadmin=> select sysdate;
          sysdate
----------------------------
 2018-04-01 11:25:37.518812
(1 row)

[dbadmin@vertica7 ~]$ vsql -U jim
The password has expired.

Changing password for jim
New password:

Also,I get this message from DBVisualizer:

Long Message:
[Vertica]VJDBC Authentication failed because the password has expired.

Details:
   Type: java.sql.SQLException
   Error Code: 100067
   SQL State: 28000

Seems okay.