On Fri, Jul 8, 2022 at 10:07 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Uh ... if such caching behavior is at all competently implemented,
> it will be transparent because the cache will notice and respond to
> events that should change its outputs.
Well, that assumes that we emit appropriate invalidations in every
place where permissions are updated, and take appropriate locks every
place where they are checked. I think that the first one might be too
optimistic, and the second one is definitely too optimistic. For
instance, consider pg_proc_ownercheck. There's no lock of any kind
taken on the function here, and at least in typical cases, I don't
think the caller takes one either. Compare the extensive tap-dancing
around locking and permissions checking in RangeVarGetRelidExtended
against the blithe unconcern in FuncnameGetCandidates.
I believe that of all the types of SQL objects in the system, only
relations have anything like proper interlocking against concurrent
DDL. Other examples of not caring at all include LookupCollation() and
LookupTypeNameExtended(). There's just no heavyweight locking here at
all, and so no invalidation based on sinval messages can ever be
reliable.
GRANT and REVOKE don't take proper locks, either, even on tables:
rhaas=# begin;
BEGIN
rhaas=*# lock table pgbench_accounts;
LOCK TABLE
rhaas=*#
Then, in another session:
rhaas=# create role foo;
CREATE ROLE
rhaas=# grant select on pgbench_accounts to foo;
GRANT
rhaas=#
Executing "SELECT * FROM pgbench_accounts" in the other session would
have blocked, but the GRANT has no problem at all.
I don't see that any of this is this patch's job to fix. If nobody's
cared enough to fix it any time in the past 20 years, or just didn't
want to pay the locking cost, well then we probably don't need to do
it now either. But I think it means that even the slightest change in
the timing or frequency of permissions checks is in theory a
user-visible change, because there are no grounds for assuming that
the permissions on any of the objects involved aren't changing while
the query is executing.
--
Robert Haas
EDB: http://www.enterprisedb.com