Обсуждение: password_rollover_time like Oracle
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
Hi James
There is no such functionality in PG.
Thanks
Kashif Zeeshan
On Thu, Jun 20, 2024 at 11:10 AM James Pang <jamespang886@gmail.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
On Jun 20, 2024, at 2:43 AM, Kashif Zeeshan <kashi.zeeshan@gmail.com> wrote:Hi JamesThere is no such functionality in PG.ThanksKashif ZeeshanOn Thu, Jun 20, 2024 at 11:10 AM James Pang <jamespang886@gmail.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';
On Thu, Jun 20, 2024 at 08:53:02PM -0400, Rui DeSousa wrote: > 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'; I can see that causing problems if you want to store CURRENT_USER in the database, perhaps for auditing. I guess you could call it user4_login12 and keep incrementing the login number, but that seems cumbersome. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
On Jun 20, 2024, at 10:46 PM, Bruce Momjian <bruce@momjian.us> wrote:
I can see that causing problems if you want to store CURRENT_USER in the
database, perhaps for auditing. I guess you could call it user4_login12
and keep incrementing the login number, but that seems cumbersome.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
I don’t think it’s too much of an issue for auditing as it’s same name with an embedded date code; however, I do think that changing a password every other month is cumbersome busy work and there are better ways to secure the account. The problem I’ve seen with this type of solution is the application owners would commonly forget to update password information and then the application would stop working causing a scramble to update the credentials. Then there is the account management itself, dropping expired users and creating new users for the upcoming month.