Re: Recognizing superuser in pg_hba.conf

Greetings,

On Thu, Jan 2, 2020 at 15:50 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
> > "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> > Tom> Meh. If the things aren't actually roles, I think this'd just add
> > Tom> confusion. Or were you proposing to implement them as roles? I'm
> > Tom> not sure if that would be practical in every case.
>
> > In fact my original suggestion when this idea was discussed on IRC was
> > to remove the current superuser flag and turn it into a role; but the
> > issue then is that role membership is inherited and superuserness
> > currently isn't, so that's a more intrusive change.
>
> To cover the proposed functionality, you'd still need some way to
> select not-superuser. So I don't think this fully answers the need
> even if we wanted to do it.

Sorry- why do we need that..? The first match for a pg_hba line wins, so
you can define all the access methods that superuser accounts are allowed
to use first, then a “reject” line for superuser accounts, and then
whatever else you want after that.

More generally, allowing inheritance of superuser scares me a bit
> from a security standpoint. I wouldn't mind turning all the other
> legacy role properties into grantable roles, but I *like* the fact
> that that one is special.

Requiring an extra “set role whatever;” is good to make sure the user
really understands they’re running as superuser, but it doesn’t really
improve actual security at all since there’s no way to require a password
or anything. That superuser-ness isn’t inherited but membership in the
“postgres” or other role-that-owns-everything role is actually strikes me
as less than ideal... the whole allow system table mods thing kinda helps
with that since you need that extra step to actually change most things but
it’s still not great imv. I can’t get too excited about trying to improve
this though since I’d expect material changes to improve security to be
beat back with backwards incompatibility concerns.

Thanks,

Stephen

>