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

Поиск
Список
Период
Сортировка
От Kyotaro HORIGUCHI
Тема Re: Proposal to suppress errors thrown by to_reg*()
Дата
Msg-id 20190319.172342.163394605.horiguchi.kyotaro@lab.ntt.co.jp
обсуждение исходный текст
Ответ на Re: Proposal to suppress errors thrown by to_reg*()  (Tatsuo Ishii <ishii@sraoss.co.jp>)
Ответы Re: Proposal to suppress errors thrown by to_reg*()  (Tatsuo Ishii <ishii@sraoss.co.jp>)
Список pgsql-hackers
At Tue, 19 Mar 2019 16:35:32 +0900 (JST), Tatsuo Ishii <ishii@sraoss.co.jp> wrote in
<20190319.163532.529526338176696856.t-ishii@sraoss.co.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



В списке pgsql-hackers по дате отправления:

Предыдущее
От: "Wu, Fei"
Дата:
Сообщение: Willing to fix a PQexec() in libpq module
Следующее
От: Masahiko Sawada
Дата:
Сообщение: Re: [HACKERS] Block level parallel vacuum