At Wed, 20 Mar 2019 07:13:28 +0900 (JST), Tatsuo Ishii <ishii@sraoss.co.jp> wrote in
<20190320.071328.485760446856666486.t-ishii@sraoss.co.jp>
> >> I (and Hoshiai-san) concern about following case:
> >>
> >> # revoke usage on schema s1 from foo;
> >> REVOKE
> >> :
> >> [connect as foo]
> >> test=> select to_regclass('s1.t1')::oid;
> >> ERROR: permission denied for schema s1
> >
> > That works in a transaction. It looks right that the actually
> > revoked schema cannot be accessed.
> >
> > S1:foo: begin;
> > S2:su : revoke usage on schema s1 from foo;
> > S1:foo: select to_regclass('s1.t1')::oid;
> >> to_regclass
> >> -------------
> >> 16418
> > S2:foo: commit;
> > S2:foo: select to_regclass('s1.t1')::oid;
> >> ERROR: permission denied for schema s1
>
> I'm confused. How is an explicit transaction related to the topic?
Since your example revokes the privilege just before (or
simultaneously with) "using" the unprivileged object. If the
given object name is obtained before the revokation, it can be
protected by beginning a transaction before obtaining the
name. If not, it is right to emit an error.
As another discussion, as I wrote just before, can be raised that
the behavior really doesn't protect nothing. We can lookup the
oid of an unprivileged objects through the system catalogs.
So I think it is reasonable that we just ignore privileges in the
commands. Maybe regclassin and friends also should be changed in
the same way.
If we protect system catalogs later, the commands naturally will
follow the change.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center