Re: password_rollover_time like Oracle

> On Jun 20, 2024, at 2:43 AM, Kashif Zeeshan <kashi(dot)zeeshan(at)gmail(dot)com> wrote:
>
> Hi James
>
> There is no such functionality in PG.
>
> Thanks
> Kashif Zeeshan
>
>
> On Thu, Jun 20, 2024 at 11:10 AM James Pang <jamespang886(at)gmail(dot)com <mailto:jamespang886(at)gmail(dot)com>> wrote:
> Hi,
> We used to use "password_rollover_time" = 7days with Oracle database, so when we change password of a user, that allow 7days for both old and new passwords to login database. application can rolling change their database user passwords in 7days. That did help application to change their database user passwords.
> we use Postgresql v14, is there similar feature for Postgresql ?
>
> Thanks,
>
> James

It can be achieved by using roles and rolling accounts. Then the application would need to update username/password before it expires to the new account/password. The only difference is rather than changing just the password the account information also changes; however, no permissions are ever given directly to the user account. I’ve been in an environments that have use this approach — Just remember to create the new user and update the username/password before they expire.

i.e.

approle (A role with no login and all the application permissions)

create user appuser202406 with inherit in role approle valid until '07/01/2024' encrypted password 'xxxx’;
create user appuser202407 with inherit in role approle valid until '08/01/2024' encrypted password ‘yyyy';