BUG #3928: INFORMATION_SCHEMA does not give results if a user is allowed only access via ROLE

Поиск
Список
Период
Сортировка
От Bart Heupers
Тема BUG #3928: INFORMATION_SCHEMA does not give results if a user is allowed only access via ROLE
Дата
Msg-id 200802050907.m1597jXG084578@wwwmaster.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #3928: INFORMATION_SCHEMA does not give results if a user is allowed only access via ROLE  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
The following bug has been logged online:

Bug reference:      3928
Logged by:          Bart Heupers
Email address:      bart@sara.nl
PostgreSQL version: 8.2.4
Operating system:   Linux & Windows
Description:        INFORMATION_SCHEMA does not give results if a user is
allowed only access via ROLE
Details:

If a user wants to get data from the INFORMATION_SCHEMA   about a table and
he has access to the table via a ROLE
then the INFORMATION_SCHEMA will not return results.

If access is given directly to the user then the INFORMATION_SCHEMA will
give results.

For example for USER BART, ROLE RADAR_READ

CREATE ROLE RADAR_READ;
GRANT RADAR_READ TO BART;
GRANT USAGE ON SCHEMA RADAR TO RADAR_READ;
GRANT SELECT,REFERENCES
ON TABLE RADAR.RADAR
TO RADAR_READ;

This will NOT give results from the query to retrieve the primary key for
the RADAR.RADAR table:

select k.column_name, c.data_type
from information_schema.key_column_usage k
join information_schema.table_constraints t
using(constraint_schema, constraint_name)
join information_schema.columns c
on c.table_schema = t.table_schema
and c.table_name = t.table_name
and k.column_name = c.column_name
where t.table_schema = 'radar'
and t.table_name = 'track_object'
and t.constraint_type = 'PRIMARY KEY'
order by k.ordinal_position

But if access is given directly to the user with :

grant usage on schema radar to bart;

GRANT SELECT,REFERENCES
ON TABLE RADAR.RADAR
TO bart;

then the above query will give the required results.

This seems to me undesired behaviour of ROLES with the INFORMATION_SCHEMA

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

Предыдущее
От: Christian Ullrich
Дата:
Сообщение: Re: BUG #3927: configure --with-gssapi fails
Следующее
От: "Arjan Tuinhout"
Дата:
Сообщение: BUG #3929: RULE causes unintended update of SEQUENCE