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

At Tue, 19 Mar 2019 16:35:32 +0900 (JST), Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp> wrote in <20190319(dot)163532(dot)529526338176696856(dot)t-ishii(at)sraoss(dot)co(dot)jp>
> >> According to the document, "to_reg* functions return null rather than
> >> throwing an error if the name is not found", but this is not the case
> >> if the arguments to those functions are schema qualified and the
> >> caller does not have access permission of the schema even if the table
> >> (or other object) does exist -- we get an error.
> >
> > You explicitly specified the namespace and I think that it is not
> > the case of not-found. It is right that the error happens since
> > you explicitly tried to access a unprivileged schema.
> >
> >> For example, to_regclass() throws an error if its argument is
> >> 'schema_name.table_name'' (i.e. contains schema name) and caller's
> >> role doesn't have access permission of the schema. Same thing can be
> >> said to Other functions,
> >>
> >> I get complain from Pgpool-II users because it uses to_regclass()
> >> internally to confirm table's existence but in the case above it's
> >> not useful because the error aborts user's transaction.
> >
> > I'm not sure how such unaccessible table names are given to the
> > function there, but it is also natural that any user trying to
> > access unprivileged objects gets an error.
>
> You misunderstand the functionality of to_regclass(). Even if a user
> does not have an access privilege of certain table, to_regclass() does
> not raise an error.
>
> test=> select * from t1;
> ERROR: permission denied for table t1
>
> test=> select to_regclass('t1')::oid;
> to_regclass
> -------------
> 1647238
> (1 row)
>
> So why can't we do the same thing for schema? For me, that way seems
> to be more consistent.

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)

Superuser gets the exepcted result.

=# set search_path to s1;
=# select to_regclass('t1')::oid; -- superuser has access to s1.
> to_regclass
> -------------
> 87612
> (1 row)

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center