On 7/1/22 05:14, Hannu Krosing wrote:
On Thu, Jun 30, 2022 at 7:25 PM Bruce Momjian <br...@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
