>> 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)
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
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp