Valid until

On 5/14/24 00:57, Rama Krishnan wrote:

Hi team,

I have a question about the "valid until" parameter in the "create role"
command.

I recently set a user's password validity to "2024-05-13", but despite
this, the user is still able to connect to the database. Is this a bug,
or is the "valid until" parameter just for identification purposes,
indicating when the password will expire?

Could you please clarify this for me?

https://www.postgresql.org/docs/current/sql-createrole.html

"
VALID UNTIL 'timestamp'

The VALID UNTIL clause sets a date and time after which the role's
password is no longer valid. If this clause is omitted the password will
be valid for all time.
"

When did the user log in?

What is the TimeZone setting in the database?

Thanks

Ram

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 5/14/24 00:57, Rama Krishnan wrote:

I recently set a user's password validity to "2024-05-13", but despite
this, the user is still able to connect to the database. Is this a bug,
or is the "valid until" parameter just for identification purposes,
indicating when the password will expire?

The VALID UNTIL clause sets a date and time after which the role's
password is no longer valid. If this clause is omitted the password will
be valid for all time.

The important point there is that the *password* is not usable after
the specified date. If the user logs in via some non-password-based
authentication method, that's fine (and it's on the infrastructure
of that auth method to enforce whatever restrictions it thinks are
appropriate).

regards, tom lane

Hi,
What is the outcome of this query for the specified user? Does the database
contain a value in the rolvaliduntil column?
SELECT rolname, rolvaliduntil FROM pg_authid;
Regards,
M.Imtiaz

On Tue, 14 May 2024 at 20:45, Tom Lane <tgl@sss.pgh.pa.us> wrote:

On 5/18/24 02:37, Rama Krishnan wrote:

Reply to list also.
Ccing list.

Please find the details below

```
postgres=# \du
                              List of roles
 Role name  |                         Attributes
------------+------------------------------------------------------------
 pgbackrest | Replication
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS
 test       | Password valid until 2023-05-13 00:00:00+00
 user_name  | Password valid until 2024-05-13 00:00:00+00

postgres=# show timezone;
 TimeZone
----------
 UTC
(1 row)

postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0
20210514 (Red Hat 8.5.0-20), 64-bit
(1 row)

postgres=# \q
[postgres@postgres16 log]$ psql -U username -d postgres
psql: error: connection to server on socket
"/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "username" does not
exist
[postgres@postgres16 log]$ psql -U user_name -d postgres
psql (16.2)
Type "help" for help.
```

Regards

A.Rama Krishnan

On Tue, May 14, 2024 at 8:57 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 5/14/24 00:57, Rama Krishnan wrote:

Hi team,

I have a question about the "valid until" parameter in the

"create role"

command.

I recently set a user's password validity to "2024-05-13", but

despite

this, the user is still able to connect to the database. Is this

a bug,

or is the "valid until" parameter just for identification purposes,
indicating when the password will expire?

Could you please clarify this for me?

https://www.postgresql.org/docs/current/sql-createrole.html
<https://www.postgresql.org/docs/current/sql-createrole.html&gt;

"
VALID UNTIL 'timestamp'

     The VALID UNTIL clause sets a date and time after which the
role's
password is no longer valid. If this clause is omitted the password
will
be valid for all time.
"

When did the user log in?

What is the TimeZone setting in the database?

Thanks

Ram

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

On 5/18/24 03:09, Rama Krishnan wrote:
Reply to list also.
Ccing list

Hi Adrian,

I have modified the pg_hba entry from trust to md5 like below

```
local   all             all                                     md5

That would be the issue. trust ignores the password check.

```

When i have tired with postgres user I am able to connect

Which is expected as postgres does not have a 'valid until' restriction.

[postgres@postgres16 data]$ psql -U postgres -d postgres
Password for user postgres:
psql (16.2)
Type "help" for help.

postgres=# \du
                              List of roles
 Role name  |                         Attributes
------------+------------------------------------------------------------
 pgbackrest | Replication
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS
 test       | Password valid until 2023-05-13 00:00:00+00
 user_name  | Password valid until 2024-05-13 00:00:00+00

But when i tried with test or user_name user  even though I am passing
the correct value I am getting this error

Again as expected as the 'valid until' timestamp is in the past.

```
[postgres@postgres16 data]$ psql -U test -d postgres
Password for user test:
psql: error: connection to server on socket
"/run/postgresql/.s.PGSQL.5432" failed: FATAL:  password authentication
failed for user "test"

postgres=# \c  postgres user_name
Password for user user_name:
connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed:
FATAL:  password authentication failed for user "user_name"

```

Once i done the changes the valid until expiration date

```

postgres=# alter user test VALID UNTIL '2024-05-19';
ALTER ROLE

postgres=> \du
                              List of roles
 Role name  |                         Attributes
------------+------------------------------------------------------------
 pgbackrest | Replication
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS
 test       | Password valid until 2024-05-19 00:00:00+00
 user_name  | Password valid until 2024-05-13 00:00:00+00
```

Finally it allows to connect test

Which is correct as the 'valid until' timestamp is in the future.

```

[postgres@postgres16 data]$ psql -d postgres -U test
Password for user test:
psql (16.2)

```

I believe this is a expected output of validunitl , Please correct me if
i m wrong

The behavior is as referenced in the documentation:

https://www.postgresql.org/docs/current/sql-createrole.html

VALID UNTIL 'timestamp'

The VALID UNTIL clause sets a date and time after which the role's
password is no longer valid. If this clause is omitted the password will
be valid for all time.

Regards

A.Rama Krishnan

--
Adrian Klaver
adrian.klaver@aklaver.com