Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?

On 22 Nov 2022, at 10:05, Bryn Llewellyn wrote:

> Because PG allows a cluster to have as many superusers as you please, and because any one of these can create or drop
another,any convention in this space needs some extra mechanisms to enforce it.. 
>
> …  effectively tamper-proof implementation of the scheme …
>
Somewhat interesting thread so far but seems to be asking more than one question —
Q1. Is there ever a use case to have two or more superusers? Answer: Yes, but entirely depending on the use case.
Q2. [IMPLIED] How to make the database tamper-proof since at least one superuser is unavoidable? Answer: Not possible,
ever— see below. 

It is best to consider a database security system’s design objectives to be tamper proof from the outside (ie., general
clientaccess perspective), and tamper evident from within the database. As far as the server is concerned one person’s
superusertampering is another person’s maintenance. There is no way to configure login credentials to prevent malicious
ormistaken changes when you need to have the occasional superuser role that can repair a serious fault or process a
systemupgrade. If an upgrade or repair can be anticipated it should already be done, the superuser is needed for the
thingsthat were not expected or too complex to pre-automate. 

AFAICT minimal tamper evident criteria will include logs being kept of changes made and these on a system the database
superusercannot change. At worst the logs will still have recorded when they were turned off. And the logs should have
recordedthe credentials used to assume the superuser role. After that it is basic business management — was the person
actingbadly, were the credentials stolen, what damage was done, and are the backups useful? 

The full security analysis is complex and searches around “threat landscape” will help widen the perspective. The
questionbecomes one of identifying what is possible to prevent, what can only be mitigated, and what can only be
repairedafter the fact. Database security is a lot more complicated than just trying to restrict the superuser role. 

Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a well-known solution to every human problem —
neat,plausible, and wrong. 
— H. L. Mencken, 1920