Re: Strange permission problem regarding pg_settings

I wrote
> "Florian Pflug" <fgp(at)phlo(dot)org> writes:
>> I tried setting the relacl for the pg_settings table to {=rw}, but I still
>> get permission denied.

> This does not surprise me; the original code was just plain buggy.
> I suspect it is applying some completely inappropriate check (like
> checking some other permission bit than UPDATE), so that the apparently
> correct failure is really coincidental, and it still fails when it
> should succeed.

I've finished tracing through this, and it turns out to be a combination
of factors. ExecCheckRTEPerms sees pg_settings referenced with
checkForWrite in a SELECT query, which it thinks means a SELECT FOR
UPDATE. It happens that this requires the same UPDATE permission as a
real UPDATE, so the aclcheck() call that gets issued is, more or less by
chance, the correct thing: a check for UPDATE privilege on pg_settings.
But that fails, and the reason it fails is the "usecatupd" defense
against allowing anyone to update a system catalog. This latter defense
obviously predates the existence of updatable views in the system
schema. What I've done about it is to disable the check in the case of
a view relation, which seems to be the minimum workable loosening of the
check ... but maybe it deserves a more complete rethink.

I have also reverted the broken change of 13-Feb-03 in rewriteHandler.c.
Upshot is that:
* loss of view permission checking is fixed in 7.3.*, 7.4.*, and HEAD;
* 7.4.* and HEAD will correctly allow UPDATE on pg_settings, although
this depends on a chance coincidence;
* the bug Tim Burgess complained of here is back:
http://archives.postgresql.org/pgsql-bugs/2003-02/msg00038.php

Per previous discussion, the best way to fix Tim's problem and make the
pg_settings behavior less coincidental seems to require a change in RTE
representation, so it will only be fixable in 7.5. I will work on that
next ...

regards, tom lane