On 7/1/22 05:14, Hannu Krosing wrote:
> On Thu, Jun 30, 2022 at 7:25 PM Bruce Momjian <bruce@momjian.us> wrote:
>> On Thu, Jun 30, 2022 at 11:52:20AM -0400, Robert Haas wrote:
>> > I don't think this would be very convenient in most scenarios,
>
> This is the eternal problem with security - more security always
> includes more inconvenience.
yep
> This one would be for cases where you want best multi-layer
> protections also against unknown threats and are ready to trade some
> convenience for security. Also it would not be that bad once you use
> automated deployment pipelines which just need an extra line to unlock
> superuser for deployment.
+1
>>> and I think it would also be difficult to implement correctly. I
>>> don't think you can get by with just having superuser() return
>>> false sometimes despite pg_authid.rolsuper being true. There's a
>>> lot of subtle assumptions in the code to the effect that the
>>> properties of a session are basically stable unless some SQL is
>>> executed which changes things.
> A good barrier SQL for this could be SET ROLE=... .
> Maybe just have a mode where a superuser can not log in _or_ SET ROLE
> unless this is explicitly allowed in pg_superuser.conf
Agreed.
In fact in a recent discussion with Joshua Brindle (CC'd) he wished for
a way that we could designate the current session "tainted". For example
if role joe with membership in postgres should always be logging in from
192.168.42.0/24 when performing admin duties as postgres, but logs in
from elsewhere their session should be marked tainted and escalating to
postgres should be denied.
>> > I think if we start injecting hacks like this it may seem to work in
>> > light testing but we'll never get to the end of the bug reports.
>
> In this case it looks like each of these bug reports would mean an
> avoided security breach which for me looks preferable.
>
> Again, this would be all optional, opt-in, DBA-needs-to-set-it-up
> feature for the professionally paranoid and not something we suddenly
> force on people who would like to run all their databases using
> user=postgres database=postgres with trust specified in the
> pg_hba.conf "because the firewall takes care of security" :)
>
>> Yeah, seems it would have to be specified per-session, but how would you
>> specify a specific session before the session starts?
>
> One often recommended way to do superuser'y things in a secure
> production database is to have a non-privileged NOINHERIT user for
> logging in and then do
> SET ROLE=<superuserrole>;
> when needed, similar to using su/sudo in shell. This practice both
> reduces the attack surface and also provides auditability by knowing
> who logged in for superuser work.
+many
--
Joe Conway
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com