From: Joe Conway <mail@joeconway.com>
To: Bhasker Bathini <bbathini@gmail.com>
To: Holger Jakobs <holger@jakobs.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Why do i need to install set_user extension if i can directly grant all required privileges to user?
Date: Wed, 21 Jun 2023 11:13:17 -0400
Message-ID: <4fe50104-af62-95dc-abcb-75abd47025a7@joeconway.com> (raw)
In-Reply-To: <CAJ4vKBtLL4yU=PrEw_y+svC9bqADPhnqHW0Ma4ktZ2ehpeY_bQ@mail.gmail.com>
References: <CAF57e9Mg7yBnt6UdaLQDYbqouiD+e7gn7mdWrBMuB4n82V1yhQ@mail.gmail.com>
<310498031.1194425.1687354401566@office.mailbox.org>
<44912597-d81a-aece-2222-5a2e116dc0e1@jakobs.com>
<CAJ4vKBtLL4yU=PrEw_y+svC9bqADPhnqHW0Ma4ktZ2ehpeY_bQ@mail.gmail.com>
On 6/21/23 10:38, Bhasker Bathini wrote:
> Thank you for the quick response.
>
> What if i can grant all the required privileges or even making the user
> a superuser, why do i need set_user ?
>
> Does set_user is just to make sure users with direct privileges wont
> accidently modify critical information/parameters unless they set the
> session to elevated privileged role to perform the operation?
>
> I am not able to find a reason why i need set_user extension?
There are two main use cases for set_user:
1/ Allow an unprivileged admin role to escalate to superuser, but (to
the extent possible) ensure every command issued is logged for audit
purposes. It can also block things like COPY PROGRAM and ALTER SYSTEM
that might allow them to do things you do not wish to allow. Once
escalated, there are ways they could bypass the controls, but the act of
bypassing itself would get logged, so if you monitor and alert you would
catch it.
2/ Allow a single unprivileged role to be used to switch to other
unprivileged roles, without having to grant all of them, and with the
ability to set a random token so that switching back (and thus to
indirectly other roles) requires the token. Think in terms of a web app
that uses a single role to log in but then switches to a actual users
role. Or a proxy or load balancer of sorts.
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: mail@joeconway.com, bbathini@gmail.com, holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: Why do i need to install set_user extension if i can directly grant all required privileges to user?
In-Reply-To: <4fe50104-af62-95dc-abcb-75abd47025a7@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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