pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy do i need to install set_user extension if i can directly grant all required privileges to user?
7+ messages / 6 participants
[nested] [flat]
* Why do i need to install set_user extension if i can directly grant all required privileges to user?
@ 2023-06-20 11:33 Bhasker Bathini <onelargepeg@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Bhasker Bathini @ 2023-06-20 11:33 UTC (permalink / raw)
To: pgsql-admin
Hi , i was wondering, why do i need to install set_user extension when i
can grant all the privileges to user directly?
Is there any advantage or any scenario where i can only perform having
set_user grants?
Appreciate your help!
Thanks
BB
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why do i need to install set_user extension if i can directly grant all required privileges to user?
@ 2023-06-21 13:33 Erik Wienhold <ewie@ewie.name>
parent: Bhasker Bathini <onelargepeg@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Erik Wienhold @ 2023-06-21 13:33 UTC (permalink / raw)
To: Bhasker Bathini <onelargepeg@gmail.com>; pgsql-admin
> On 20/06/2023 13:33 CEST Bhasker Bathini <onelargepeg@gmail.com> wrote:
>
> Hi , i was wondering, why do i need to install set_user extension when i can
> grant all the privileges to user directly?
Are you talking about https://github.com/pgaudit/set_user ?
I never used this extension but after skimming over the readme it looks like
a replacement for SET ROLE and SET SESSION AUTHORIZATION with logging for audit
purposes.
> Is there any advantage or any scenario where i can only perform having
> set_user grants?
Changing roles is already possible in Postgres. You must be a member of the
target role or be a superuser in order to change roles. I don't know if the
set_user extension behaves identical to SET ROLE etc. because it also supports
whitelisting of target roles and superusers.
The extension may be relevant to you if the whitelist feature or auditing is
a requirement for your project. But I'm not sure if the whitelist feature
bypasses the membership requirement imposed by SET ROLE or if you still have
to grant memberships to target roles.
--
Erik
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why do i need to install set_user extension if i can directly grant all required privileges to user?
@ 2023-06-21 14:06 Holger Jakobs <holger@jakobs.com>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 1 reply; 7+ messages in thread
From: Holger Jakobs @ 2023-06-21 14:06 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 21.06.23 um 15:33 schrieb Erik Wienhold:
>
> Changing roles is already possible in Postgres. You must be a member of the
> target role or be a superuser in order to change roles.
This is going to change in detail as of version 16 of PostgreSQL. You
may determine whether a role switch is allowed or not.
Excerpt from https://www.postgresql.org/docs/16/sql-grant.html:
"The |SET| option, if it is set to |TRUE|, allows the member to change
to the granted role using the |SET ROLE|
<https://www.postgresql.org/docs/16/sql-set-role.html; command. If a
role is an indirect member of another role, it can use |SET ROLE| to
change to that role only if there is a chain of grants each of which has
|SET TRUE|. This option defaults to |TRUE|."
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../44912597-d81a-aece-2222-5a2e116dc0e1@jakobs.com/3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why do i need to install set_user extension if i can directly grant all required privileges to user?
@ 2023-06-21 14:38 Bhasker Bathini <bbathini@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bhasker Bathini @ 2023-06-21 14:38 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
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?
Please advise
Thanks
On Wed, Jun 21, 2023 at 10:06 AM Holger Jakobs <holger@jakobs.com> wrote:
> Am 21.06.23 um 15:33 schrieb Erik Wienhold:
>
>
> Changing roles is already possible in Postgres. You must be a member of the
> target role or be a superuser in order to change roles.
>
> This is going to change in detail as of version 16 of PostgreSQL. You may
> determine whether a role switch is allowed or not.
>
> Excerpt from https://www.postgresql.org/docs/16/sql-grant.html:
>
> "The SET option, if it is set to TRUE, allows the member to change to the
> granted role using the SET ROLE
> <https://www.postgresql.org/docs/16/sql-set-role.html; command. If a role
> is an indirect member of another role, it can use SET ROLE to change to
> that role only if there is a chain of grants each of which has SET TRUE.
> This option defaults to TRUE."
>
>
>
> --
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
> --
Bhasker Bathini
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why do i need to install set_user extension if i can directly grant all required privileges to user?
@ 2023-06-21 14:55 Jeff Janes <jeff.janes@gmail.com>
parent: Bhasker Bathini <onelargepeg@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Jeff Janes @ 2023-06-21 14:55 UTC (permalink / raw)
To: Bhasker Bathini <onelargepeg@gmail.com>; +Cc: pgsql-admin
On Wed, Jun 21, 2023 at 7:50 AM Bhasker Bathini <onelargepeg@gmail.com>
wrote:
> Hi , i was wondering, why do i need to install set_user extension when i
> can grant all the privileges to user directly?
>
>
Surely you don't need to install set_user in general. Indeed, I've never
even heard of it before.
Maybe you need it for some specific use, but you didn't describe anything
about what you are doing.
Cheers,
Jeff
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why do i need to install set_user extension if i can directly grant all required privileges to user?
@ 2023-06-21 15:13 Joe Conway <mail@joeconway.com>
parent: Bhasker Bathini <bbathini@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Joe Conway @ 2023-06-21 15:13 UTC (permalink / raw)
To: Bhasker Bathini <bbathini@gmail.com>; Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why do i need to install set_user extension if i can directly grant all required privileges to user?
@ 2023-06-21 15:57 Bhasker Bathini <bbathini@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Bhasker Bathini @ 2023-06-21 15:57 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Bhasker Bathini <onelargepeg@gmail.com>; pgsql-admin
Jeff, set_user needs to be installed as per CIS benchmark standards, I see
this as more vulnerable giving to individual users.
When you know a user or application account need set of permission, you can
directly grant all the necessary privileges, why do you need to switch
account in performing any operation, I am trying to find if there is any
operation in particular that can only be done by postgres, or any super
user account to inherit its roles?
On Wed, Jun 21, 2023 at 10:55 AM Jeff Janes <jeff.janes@gmail.com> wrote:
> On Wed, Jun 21, 2023 at 7:50 AM Bhasker Bathini <onelargepeg@gmail.com>
> wrote:
>
>> Hi , i was wondering, why do i need to install set_user extension when i
>> can grant all the privileges to user directly?
>>
>>
> Surely you don't need to install set_user in general. Indeed, I've never
> even heard of it before.
>
> Maybe you need it for some specific use, but you didn't describe anything
> about what you are doing.
>
> Cheers,
>
> Jeff
>
>>
--
Bhasker Bathini
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-06-21 15:57 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-20 11:33 Why do i need to install set_user extension if i can directly grant all required privileges to user? Bhasker Bathini <onelargepeg@gmail.com>
2023-06-21 13:33 ` Erik Wienhold <ewie@ewie.name>
2023-06-21 14:06 ` Holger Jakobs <holger@jakobs.com>
2023-06-21 14:38 ` Bhasker Bathini <bbathini@gmail.com>
2023-06-21 15:13 ` Joe Conway <mail@joeconway.com>
2023-06-21 14:55 ` Jeff Janes <jeff.janes@gmail.com>
2023-06-21 15:57 ` Bhasker Bathini <bbathini@gmail.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