Re: Proposal to suppress errors thrown by to_reg*()

On Wed, 20 Mar 2019 09:48:59 +0900 (Tokyo Standard Time)
Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:

> At Wed, 20 Mar 2019 07:13:28 +0900 (JST), Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp> wrote in <20190320(dot)071328(dot)485760446856666486(dot)t-ishii(at)sraoss(dot)co(dot)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.

What we want to say below is 'foo' has no privilege. not important to execute REVOKE.
> # revoke usage on schema s1 from foo;
> REVOKE
> :
> [connect as foo]
> test=> select to_regclass('s1.t1')::oid;
> ERROR: permission denied for schema s1

> 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.

Yes, I think so too.
But their functions may be used for confirming a obejct visibility, so this time
I want to supress errors only.
And if want to raise an error about "permission denied for schema xx",
would use regclass() function.

best regards,

--
Takuma Hoshiai <hoshiai(at)sraoss(dot)co(dot)jp>

> If we protect system catalogs later, the commands naturally will
> follow the change.
>
> regards.
>
> --
> Kyotaro Horiguchi
> NTT Open Source Software Center
>
>