pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpassword_rollover_time like Oracle
5+ messages / 4 participants
[nested] [flat]
* password_rollover_time like Oracle
@ 2024-06-20 05:05 James Pang <jamespang886@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: James Pang @ 2024-06-20 05:05 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: password_rollover_time like Oracle
@ 2024-06-20 06:43 Kashif Zeeshan <kashi.zeeshan@gmail.com>
parent: James Pang <jamespang886@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kashif Zeeshan @ 2024-06-20 06:43 UTC (permalink / raw)
To: James Pang <jamespang886@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: password_rollover_time like Oracle
@ 2024-06-21 00:53 Rui DeSousa <rui.desousa@icloud.com>
parent: Kashif Zeeshan <kashi.zeeshan@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Rui DeSousa @ 2024-06-21 00:53 UTC (permalink / raw)
To: Kashif Zeeshan <kashi.zeeshan@gmail.com>; +Cc: James Pang <jamespang886@gmail.com>; pgsql-admin@lists.postgresql.org
> On Jun 20, 2024, at 2:43 AM, Kashif Zeeshan <kashi.zeeshan@gmail.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@gmail.com <mailto: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';=
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: password_rollover_time like Oracle
@ 2024-06-21 02:46 Bruce Momjian <bruce@momjian.us>
parent: Rui DeSousa <rui.desousa@icloud.com>
0 siblings, 1 reply; 5+ messages in thread
From: Bruce Momjian @ 2024-06-21 02:46 UTC (permalink / raw)
To: Rui DeSousa <rui.desousa@icloud.com>; +Cc: Kashif Zeeshan <kashi.zeeshan@gmail.com>; James Pang <jamespang886@gmail.com>; pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: password_rollover_time like Oracle
@ 2024-06-21 03:13 Rui DeSousa <rui.desousa@icloud.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 5+ messages in thread
From: Rui DeSousa @ 2024-06-21 03:13 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Kashif Zeeshan <kashi.zeeshan@gmail.com>; James Pang <jamespang886@gmail.com>; pgsql-admin@lists.postgresql.org
> 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 <mailto:bruce@momjian.us>> https://momjian.us <https://momjian.us/;
> EDB https://enterprisedb.com <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. =
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-06-21 03:13 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-20 05:05 password_rollover_time like Oracle James Pang <jamespang886@gmail.com>
2024-06-20 06:43 ` Kashif Zeeshan <kashi.zeeshan@gmail.com>
2024-06-21 00:53 ` Rui DeSousa <rui.desousa@icloud.com>
2024-06-21 02:46 ` Bruce Momjian <bruce@momjian.us>
2024-06-21 03:13 ` Rui DeSousa <rui.desousa@icloud.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox