Re: pg_walinspect - a new extension to get raw WAL data and WAL stats

Greetings,

* Jeff Davis (pgsql(at)j-davis(dot)com) wrote:
> On Thu, 2022-03-10 at 15:54 -0500, Stephen Frost wrote:
> > The standard is basically that all of the functions it brings are
> > written to enforce the PG privilege system and you aren't able to use
> > the extension to bypass those privileges. In some cases that means
> > that
>
> Every extension should follow that standard, right? If it doesn't (e.g.
> creating dangerous functions and granting them to public), then even
> superuser should not install it.

Every extension that's intended to be installed on a multi-user PG
system where the admin cares about security in the database, sure. I
disagree that this applies universally to every system or every
extension. Those are standards that modules we distribute in contrib
should meet but I don't know that we necessarily have to have them for,
say, modules in test.

> > the C-language functions installed have if(!superuser) ereport()
> > calls
>
> I'm curious why not rely on the grant system where possible? I thought
> we were trying to get away from explicit superuser checks.

We don't yet have capabilities for everything. I agree that we should
be getting away from explicit superuser checks and explained below how
we might be able to in this case.

> > I've not looked back on this thread, but I'd expect pg_walinspect to
> > need those superuser checks and with those it *could* be marked as
> > trusted, but that again brings into question how useful it is to mark
> > it
> > thusly.
>
> As long as any functions are safely accessible to public or a
> predefined role, there is some utility for the 'trusted' marker.

I'm not sure that I agree, though I'm also not sure that it's a useful
thing to debate. Still, if all of the functions in a particular
extension have explicit if (!superuser) ereport() checks in them, then
while installing it is 'safe' and it could be marked as 'trusted'
there's very little point in doing so as the only person who can get
anything useful from those functions is a superuser, and a superuser can
install non-trusted extensions anyway. How is it useful to mark such an
extension as 'trusted'?

> As this patch is currently written, pg_monitor has access these
> functions, though I don't think that's the right privilege level at
> least for pg_get_raw_wal_record().

Yeah, I agree that pg_monitor isn't the right thing for such a function
to be checking.

> > I certainly don't think we should allow either database owners or
> > regular users on a system the ability to access the WAL traffic of
> > the
> > entire system.
>
> Agreed. That was not what I intended by asking if it should be marked
> 'trusted'. The marker only allows the non-superuser to run the CREATE
> EXTENSION command; it's up to the extension script to decide whether
> any non-superusers can do anything at all with the extension.

Sure.

> > More forcefully- we should *not* be throwing more access
> > rights towards $owners in general and should be thinking about how we
> > can allow admins, providers, whomever, the ability to control what
> > rights users are given. If they're all lumped under 'owner' then
> > there's no way for people to provide granular access to just those
> > things they wish and intend to.
>
> Not sure I understand, but that sounds like a larger discussion.

The point I was trying to make is that it's better to move in the
direction of things like pg_read_all_data rather than just declaring
that the owner of a database can read all of the tables in that
database, as an example. Maybe we want to implicitly have such
privilege for the owner of the database too, but we should first make it
something that's able to be GRANT'd out to non-owners so that it's not
required that all of those privileges be given out together at once.

Note that to be considered an 'owner' of an object you have to be a
member of the role that owns the object, which means that said role is
necessarily able to also become the owning role too. Lumping lots of
privileges together- all the rights that being an 'owner' of the object
conveys, plus the ability to also become that role directly and do
things as that role, works actively against the general idea of 'least
privilege'.

Thanks,

Stephen