Re: Seeking practice recommendation: is there ever a use case to have two or more superusers? - Mailing list pgsql-general

On 2022-11-18 13:11:24 -0800, Bryn Llewellyn wrote:
>     hjp-pgsql@hjp.at wrote:
>     bryn@yugabyte.com wrote:
>         Given that the bootstrap superuser must exist, is there ever a reason
>         to create
>         another role with "superuser"?
>
>         My intuition tells me that the answer is a resounding "No!".
>
>
>     Is there ever a reason? Yes. Does that reason apply to you? I don't
>     know. Maybe, maybe not.
[...]
>     You could create additional superusers and restrict those to
>     certain databases and/or IP ranges. That probably won't stop an attacker (I
>     can think of at least one way to get around that and it's probably
>     even easier than I think) but it might prevent accidental damage.
[...]
>     One important task that can AFAIK only be performed by superusers is
>     the creation of functions in untrusted languages like plpython3u
>     and plperlu. If your application uses functions in those languages you need
>     a superuser to install or upgrade it.
>
>
> Thanks, Peter. I experimented with the notion of restricting a superuser to
> certain databases. My set-up has this in the “hba_file”:
[...]
> now failed with this error:
>
> ...no pg_hba.conf entry for host "[local]", user "postgres", database "d0"...
>
> as promised by the doc. But this seems to achieve very little. Having
> authorized using the first command, I then did "drop database d0". I had no
> reason to think that it might fail. And it didn't. I see that it's careless to
> say that a superuser is *unstoppable*. But it seems that, as long as a
> superuser can start as session at all, then it can certainly do untold harm.

Yes, you can drop the database. But that will be noticed soon enough.
You can even recreate it, but if you don't know the contents, this will
also be quite conspicuous. I am much more worried about subtle changes:
Altering a few records, installing a different version of a trigger.
That might not be noticed for a long time and by the time you notice it
you can't restore from backup because you have a lot of new changes.

So preventing a user from connecting to to a database has some value
even if it doesn't prevent that user from dropping the database as a
whole.

(And as I said, it only helps against *accidental* damage. An attacker
who already has some superuser role can almost certainly escalate to any
other superuser role.)

> I don't see that the point about maintenance changes for functions written in
> untrusted languages changes anything. You already have the inevitable bootstrap
> superuser and, if the organization chooses, robust "four eyes" practices to
> govern how people use sessions that authorize as this role. You don't need a
> second superuser just because this kind of task is to be done a long time after
> the initial cluster creation and configuration.

You almost certainly want to automate software upgrades. So the actual
session will be "zero eyes". One would hope that configuring the job is
done with proper care, but editing errors happen and are not always
caught by a reviewer. Having to get the database, the role and the
password consistently wrong is a much greater hurdle that getting just
the database wrong.

So you can give these credentials to you developers or devops folks
(whom you trust not attack the system - they could do that by adding
backdoors to the code - but don't trust to never make a mistake).

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"