At Tue, 19 Mar 2019 17:54:01 +0900 (JST), Tatsuo Ishii <ishii@sraoss.co.jp> wrote in
<20190319.175401.646838939186238443.t-ishii@sraoss.co.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