Date: Wed, 21 Jun 2023 15:33:21 +0200 (CEST)
From: Erik Wienhold <ewie@ewie.name>
To: Bhasker Bathini <onelargepeg@gmail.com>, pgsql-admin@postgresql.org
Message-ID: <310498031.1194425.1687354401566@office.mailbox.org>
In-Reply-To: 
 <CAF57e9Mg7yBnt6UdaLQDYbqouiD+e7gn7mdWrBMuB4n82V1yhQ@mail.gmail.com>
References: 
 <CAF57e9Mg7yBnt6UdaLQDYbqouiD+e7gn7mdWrBMuB4n82V1yhQ@mail.gmail.com>
Subject: Re: Why do i need to install set_user extension if i can directly
 grant all required privileges to user?
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Importance: Normal
Archived-At: 
 <https://www.postgresql.org/message-id/310498031.1194425.1687354401566%40office.mailbox.org>
Precedence: bulk

> 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