public inbox for [email protected]help / color / mirror / Atom feed
Valid until 5+ messages / 4 participants [nested] [flat]
* Valid until @ 2024-05-14 07:57 Rama Krishnan <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Rama Krishnan @ 2024-05-14 07:57 UTC (permalink / raw) To: pgsql-general 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? Thanks Ram ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Valid until @ 2024-05-14 15:27 Adrian Klaver <[email protected]> parent: Rama Krishnan <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: Adrian Klaver @ 2024-05-14 15:27 UTC (permalink / raw) To: Rama Krishnan <[email protected]>; pgsql-general 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 [email protected] ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Valid until @ 2024-05-14 15:45 Tom Lane <[email protected]> parent: Adrian Klaver <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: Tom Lane @ 2024-05-14 15:45 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: Rama Krishnan <[email protected]>; pgsql-general Adrian Klaver <[email protected]> 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Valid until @ 2024-05-14 15:52 Muhammad Imtiaz <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Muhammad Imtiaz @ 2024-05-14 15:52 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Adrian Klaver <[email protected]>; Rama Krishnan <[email protected]>; pgsql-general 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 <[email protected]> wrote: > Adrian Klaver <[email protected]> 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 > > > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Valid until @ 2024-05-18 14:48 Adrian Klaver <[email protected]> parent: Adrian Klaver <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Adrian Klaver @ 2024-05-18 14:48 UTC (permalink / raw) To: Rama Krishnan <[email protected]>; +Cc: pgsql-general 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 [email protected] ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-05-18 14:48 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-05-14 07:57 Valid until Rama Krishnan <[email protected]> 2024-05-14 15:27 ` Adrian Klaver <[email protected]> 2024-05-14 15:45 ` Tom Lane <[email protected]> 2024-05-14 15:52 ` Muhammad Imtiaz <[email protected]> 2024-05-18 14:48 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox