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

At Tue, 19 Mar 2019 17:54:01 +0900 (JST), Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp> wrote in <20190319(dot)175401(dot)646838939186238443(dot)t-ishii(at)sraoss(dot)co(dot)jp>
> > It seems to be a different thing. The oid 1647239 would be a
> > table in public schema or any schema that the user has access
> > to. If search_path contained only unprivileged schemas, the
> > function silently ignores such schemas.
> >
> > => set search_path to s1; -- the user doesn't have access to this schema.
> > => select to_regclass('t1')::oid; -- the table is really exists.
> >> to_regclass
> >> -------------
> >>
> >> (1 row)
>
> 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

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center