Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sKSWe-00AKod-9y for pgsql-admin@arkaria.postgresql.org; Fri, 21 Jun 2024 00:53:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sKSWc-0050S1-Mt for pgsql-admin@arkaria.postgresql.org; Fri, 21 Jun 2024 00:53:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sKSWc-0050Rs-As for pgsql-admin@lists.postgresql.org; Fri, 21 Jun 2024 00:53:10 +0000 Received: from qs51p00im-qukt01071501.me.com ([17.57.155.4]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sKSWa-002jyk-CG for pgsql-admin@lists.postgresql.org; Fri, 21 Jun 2024 00:53:10 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=icloud.com; s=1a1hai; t=1718931186; bh=I5EwPhX23bEJYav5J0y6Xf9E2/o5ZNh60nJcrTvtUhQ=; h=From:Message-Id:Content-Type:Mime-Version:Subject:Date:To; b=skZg2Odcdr6+tUsCC8+V/UWIWc26E8ITsIK1RSywqTK365ut5oGTXnLgqMZzgHm8f 8qNN39qf08dOngaEaCpjQiCQsg8a1IqS1Ub913t7zGFz9fDslu/66K7ApVbjtZ7SUu nr+9DiyREZxBMHgFcVCRPjBWGwupj2tGG1JyZGRlUknKFa9fW/pVa/rgHtnz9oAYf7 03aa906CvDPUWVUMMKfv03UbhJXKp8uYpbVzvfXoBapO0M/AJjmiEr2B7pnU95tjHl uLN6MMPpiH9Z25clnOur+jJfuAIX+4QmHoIXj/TIoGj1uYiWOdQBi2baAXCjFQap1a qeMCb0r3w8aAQ== Received: from smtpclient.apple (qs51p00im-dlb-asmtp-mailmevip.me.com [17.57.155.28]) by qs51p00im-qukt01071501.me.com (Postfix) with ESMTPSA id 2B4631C40191; Fri, 21 Jun 2024 00:53:05 +0000 (UTC) From: Rui DeSousa Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_F6EF69F5-0453-4F4E-9E32-0B43728DDC5A" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.8\)) Subject: Re: password_rollover_time like Oracle Date: Thu, 20 Jun 2024 20:53:02 -0400 In-Reply-To: Cc: James Pang , pgsql-admin@lists.postgresql.org To: Kashif Zeeshan References: X-Mailer: Apple Mail (2.3696.120.41.1.8) X-Proofpoint-GUID: MIHlaGjQBkGM7yu1ZHZcUFA3dx93SWDR X-Proofpoint-ORIG-GUID: MIHlaGjQBkGM7yu1ZHZcUFA3dx93SWDR X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.272,Aquarius:18.0.1039,Hydra:6.0.680,FMLib:17.12.28.16 definitions=2024-06-20_12,2024-06-20_04,2024-05-17_01 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 spamscore=0 mlxlogscore=999 clxscore=1011 suspectscore=0 malwarescore=0 mlxscore=0 bulkscore=0 phishscore=0 adultscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.19.0-2308100000 definitions=main-2406210005 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_F6EF69F5-0453-4F4E-9E32-0B43728DDC5A Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Jun 20, 2024, at 2:43 AM, Kashif Zeeshan = wrote: >=20 > Hi James >=20 > There is no such functionality in PG. >=20 > Thanks > Kashif Zeeshan >=20 >=20 > On Thu, Jun 20, 2024 at 11:10=E2=80=AFAM James Pang = > wrote: > Hi, > We used to use "password_rollover_time" =3D 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 ?=20 >=20 > Thanks, >=20 > 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=E2=80=99ve = been in an environments that have use this approach =E2=80=94 Just = remember to create the new user and update the username/password before = they expire. i.e.=20 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=E2=80=99; create user appuser202407 with inherit in role approle valid until = '08/01/2024' encrypted password =E2=80=98yyyy';= --Apple-Mail=_F6EF69F5-0453-4F4E-9E32-0B43728DDC5A Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

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=E2=80=AFAM = James Pang <jamespang886@gmail.com> wrote:
Hi,
   We used to use "password_rollover_time" =3D = 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=E2=80=99v= e been in an environments that have use this approach =E2=80=94 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=E2=80=99;
create user appuser202407 with inherit in = role approle valid until '08/01/2024' encrypted password = =E2=80=98yyyy';
= --Apple-Mail=_F6EF69F5-0453-4F4E-9E32-0B43728DDC5A--