GRANT UPDATE и SELECT

Тихонюк Владимир
Дата: 10.04.2004 16:55:42
Hi all!
Столкнулся с такой проблемой:

Есть таблица test(id int, name text).
Есть пользователь vst.
Есть view test_view AS SELECT * FROM test WHERE id > 10;

На таблицу test у пользователя нет никаких прав.
На test_view у пользователя есть права на SELECT.

Т.о. пользователь vst может видеть только строки у которых id > 10 из таблицы test.

Едем дальше.
Я хочу, что бы пользователь мог изменять и добавлять строки таблице test но видеть он должен только то, что я ему разрешу через test_view. Т.е. у пользователя будут права на UPDATE и INSERT таблицы и не будет прав на SELECT.

Сказано - сделано:
GRANT INSERT, UPDATE ON test TO vst;

Кульминация (pgsql под юзером vst):

testdb=> INSERT INTO test VALUES (1, 'test');
INSERT 17213 1
testdb=> UPDATE test SET name = '321' WHERE id = 1;
ERROR: permission denied for relation test

Если я из под админа сделаю GRANT SELECT ON test TO vst; то всё будет впорядке.

Вот такие пироги.

С уважением,
Владимир Тихонюк
Shweik
Дата: 11.04.2004 00:24:01
Задачка простая, но тема с подвохом. Все нижесказанное - результат RTFM+expirience,
опробывалось на PG 7.3-7.4 ,так что гарантировать ничего не могу.
Тихонюк Владимир

Я хочу, что бы пользователь мог изменять и добавлять строки таблице test но видеть он должен только то, что я ему разрешу через test_view. Т.е. у пользователя будут права на UPDATE и INSERT таблицы и не будет прав на SELECT.

Это делается вот так:

create OR REPLACE rule r_test_ins as
on insert to test_view do instead
insert into test (id,name) values (new.id,new.name);

И все. НИКАКИХ прав на таблицу test пользователь vst не имеет-он работает только с просмотром.
Аналогично пишем правило ON UPDATE (ну может и ON DELETE) и вроде бы все.
Но тут есть одна неявная особенность:
Все действия с привилегями просмотра нужно реализовывать в правилах.
( то что по умолчанию работает GRANT SELECT on test_view to vts
значит :
a) CREATE VIEW как бы создает "неявное" правило на SELECT,
которое анализирует права доступа.
(кое-что об этом см в гл 34.2.1. How SELECT Rules Work)
б) Достаточно перекрыть это правило и все будет в Ваших руках -
не напишете рестиктов - GRANT/REVOKE перестанут влиять на
доступ к просмотру )
Думаю это нечто среднее между багом и фичей 8))
Например незная этого можно написать :
REVOKE INSERT on test_view from vst;
увидев что это не работает заорать "Ага, в Постгресе безопастность глюкавая!!!"
IMHO все совсем не так - багом является другое :
в доке гл. 34.4. Rules and Privileges я не обнаружил описания подобных ситуаций.
Вот это опасно.
Может тут дело только в моем "Forgive-my-dirty-engleze" ;)\
То что я нашел в доке меня только развеселило.
Пользователь vst создал _СВОЙ_ просмотр и сделал грант на него для кого-то кому
этих данных видеть не стоит.
Ну например представьте: секретарь(!!!) опубликовала список телефонов шефа
доступных ей. Какая жуть. "Я плакаль...." (c)
IMHO беда СБ Постгреса не в том что она "плохая/сложная",а в том что
у писатели доки до этого раздела понастоящему никогда не добирались.
Shweik
Дата: 11.04.2004 02:05:21
Да забыл -вот примитивнейший вариант использования функции проверки прав
в правиле.

create or replace function check_priv(name,text,text) RETURNS void AS '
declare
Username alias for $1;
Relation alias for $2;
Privelege alias for $3;
TR record;
BEGIN
select into TR has_table_privilege(UserName,Relation,Privelege) as priv;
if TR.priv='
'f'' then
RAISE EXCEPTION '
' НЕТУ ВАС ПРАВ!!! '';
end if;
return NULL;
END;
'
LANGUAGE 'plpgsql';
---------------------------------------------а теперь создаем правило

create OR REPLACE rule r_test_ins as
on insert to test_view do instead (
select check_priv(current_user,'test_view','insert');
insert into test (id,name) values (new.id,new.name);
);

Все конечно нужно в реальности делать чуть иначе,
например можно обойтись и баз "обвязки" на pl/pgsql
Т.е "простор открыт и ничего святого!"(с)
Тихонюк Владимир
Дата: 13.04.2004 09:47:51
Hi, Shweik !

Спасибо за совет. Всё сделал как хотел.
Правда возник новый вопрос: если я переопределяю правило на INSERT, то при вставке, постгрес не хавает значения по-умолчанию. Например:

CREATE TABLE test ( id SERIAL, name text );

В этой таблице, значение по-умолчанию для поля id = nextval('test_id_seq');
Т.е. комманда INSERT INTO test (name) VALUES ('Vladimir'); проходит.

Если я сделаю правило на INSERT для этой таблицы:
CREATE RULE "test_insert_rule" AS ON INSERT TO test DO INSTEAD
INSERT INTO test (id, name) VALUES (NEW.id. NEW.name);

То комманда INSERT INTO test (name) VALUES ('Vladimir'); уже не пройдёт.

В принципе я могу вытащить значения по-умолчанию для колонок из системных таблиц (pg_class, pg_attribude, pg_attrdef) но тогда возникают проблемы, если в значении по-умолчанию записано не статическое значение, а функция.

Та же петрушка возникает и при правиле на UPDATE.
При UPDATE'e какого-нибудь одного поля таблицы через правило, будут UPDATиться все поля таблицы.

В принципе и этого можно избежать, написав функцию, которая будет просматривать запись NEW и поля со значением NULL не вставлять в INSERT/UPDATE, но это тоже не выход.

Может есть какой-нибудь более цивилизованный способ с этим бороться ?

С уважением,
Владимир Тихонюк
OAN
Дата: 28.04.2004 11:53:08
>>То комманда INSERT INTO test (name) VALUES ('Vladimir'); уже не пройдёт.
попробуй такой вариант
INSERT INTO test (id,name) SELECT nextval('test_id_seq'),'Vladimir';

не забуть про доступ к последовательности, например
GRANT ALL ON test_id_seq TO PUBLIC;

>>При UPDATE'e какого-нибудь одного поля таблицы через правило, будут UPDATиться все поля таблицы.

у меня подобное было для таблицы пользователей программы, я создал 2 отображения - одно отвечало за добавку/изменение/удаления id, имени, описания и т.п.,
а второе отображение только за изменение поля с паролем.

первая вьюшка:
CREATE VIEW users_v AS SELECT id,id_parent,depth,CASE WHEN visible_lvl>0 THEN inv2str(name) ELSE name END AS name,descript,inv2str(coalesce(pswd,descript)) as pswd,modify_date,visible_lvl,modify_lvl,modify_user FROM users;

GRANT SELECT,INSERT,UPDATE,DELETE ON users_v TO GROUP usr;

CREATE RULE users_v_ins AS ON INSERT TO users_v DO INSTEAD INSERT INTO users (id,id_parent,depth,name,descript,visible_lvl,modify_lvl) VALUES(new.id,new.id_parent,new.depth,new.name,new.descript,new.visible_lvl,new.modify_lvl);

CREATE RULE users_v_upd AS ON UPDATE TO users_v DO INSTEAD UPDATE users SET id=new.id,id_parent=new.id_parent,descript=new.descript,name=new.name,visible_lvl=new.visible_lvl,modify_lvl=new.modify_lvl WHERE id=old.id;

CREATE RULE users_v_del AS ON DELETE TO users_v DO INSTEAD DELETE FROM users WHERE id=old.id;

вторая вьюшка:
CREATE VIEW users_pswd_v AS SELECT id,inv2str(coalesce(pswd,'')) as pswd FROM users;

GRANT SELECT,UPDATE ON users_pswd_v TO GROUP usr;

CREATE RULE users_pswd_v_upd AS ON UPDATE TO users_pswd_v DO INSTEAD UPDATE users SET pswd=new.pswd WHERE id=old.id;

где inv2str - функция шифрования пароля.
Заглянул
Дата: 28.04.2004 15:13:17

test=> \c test postgres
You are now connected to database test as user postgres.
test=# create user vst;
CREATE USER
test=# create table test(id int, name text);
CREATE TABLE
test=# create view test_view AS SELECT * FROM test WHERE id > 10;
CREATE VIEW
test=# insert into test(id, name) values(1, 'first');
INSERT 2392670 1
test=# insert into test(id, name) values(2, 'second');
INSERT 2392671 1
test=# insert into test(id, name) values(12, 'for view');
INSERT 2392672 1
test=# grant select on test_view to vst;
GRANT
test=# grant update, delete on test to vst;
GRANT
test=# \c test vst
You are now connected to database test as user vst.
test=> select * from test_view;
id | name
----+----------

12 | for view
(1 row)

test=> select * from test;
ERROR: test: permission denied
test=> update test set name='newname';
UPDATE 3
test=> update test set name='newname' where id=12;
ERROR: test: permission denied

Таким образом, если обновление таблицы осуществляется по условию WHERE, требуется разрешение на выборку данных из таблицы.



test=> \c test postgres
You are now connected to database test as user postgres.
test=# revoke update, delete on test from vst;
REVOKE
test=# create OR REPLACE rule r_test_upd as on update to test_view do instead
test-# update test set id=new.id, name=new.name where id=old.id;
CREATE RULE
test=# alter table test alter column id set not null;
ALTER TABLE
test=# alter table test add constraint pk_test_id primary key(id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index 'pk_test_id' for table 'test'
ALTER TABLE
test=# create OR REPLACE rule r_test_del as on delete to test_view do instead
test-# delete from test where id=old.id;
CREATE RULE
test=# \c test vst
You are now connected to database test as user vst.
test=> insert into test_view(id, name) values(15, '15');
INSERT 2392678 1

То есть, никакие проверки прав при выполнении операций над таблицей через правила не производятся.



test=> create sequence seq_test_id;
CREATE SEQUENCE
test=> \c test postgres
You are now connected to database test as user postgres.
test=# alter table test alter column id set default nextval('seq_test_id');
ALTER TABLE
test=# select setval('seq_test_id', 20);
setval
--------

20
(1 row)
test=# alter table test alter column name set default 'default name';
ALTER TABLE
test=# insert into test_view(id) select nextval('seq_test_id');;
INSERT 2392686 1
test=# insert into test_view(name) values('21');
ERROR: ExecInsert: Fail to add null value in not null attribute id
test=# select * from test;
id | name
----+---------

1 | newname
2 | newname
12 | newname
15 | 15
21 |
(5 rows)


Таки да, вместо значений по умолчанию значения null.
А вот если исключить из представления столбцы, где требуется вводить значения по умолчанию (например, столбец первичного ключа), то все будет работать, только вот ввести в них через представление ничего нельзя будет. ;-)
Действительно, можно создать несколько представлений и обойти эту проблему.
Заглянул
Дата: 28.04.2004 16:42:33
Продолжение.


test=# alter table test_view alter name set default 'default name from view';
ALTER TABLE
test=# insert into test_view(id) select nextval('seq_test_id');
INSERT 2392707 1
test=# select * from test_view;
id | name
----+------------------------

12 | newname
15 | 15
16 | 16
21 |
22 |
23 | default name from view
(6 rows)

test=# alter table test_view alter id set default nextval('seq_test_id');
ALTER TABLE
test=# insert into test_view(name) select nextval('ins name');
ERROR: nextval: invalid name syntax


Как видим, можно для представлений назначить значения по умолчанию, но почему-то этот трюк не проходит для значений, генерируемых последовательностями. Хм.
OAN
Дата: 29.04.2004 09:53:34
>> test=# alter table test_view alter id set default nextval('seq_test_id');
>> test=# insert into test_view(name) select nextval('ins name');
>>ERROR: nextval: invalid name syntax

названия последовательностей разные ('seq_test_id' и 'ins name'), но это скорее опечатка.

>> Как видим, можно для представлений назначить значения по умолчанию, но почему-то этот трюк не проходит для значений, генерируемых последовательностями.

можно использовать отображения, у которых правила добавления будут задовать значения для полей по-умолчанию c использованеим конструкции CASE WHEN ... IS NULL THEN ... END и/или которых нет в отображении.
Заглянул
Дата: 29.04.2004 13:36:58
Действительно, поторопился я с выводами.

test=# \d test_view
View "public.test_view"
Column | Type | Modifiers
--------+---------+--------------------------------------

id | integer | default nextval('seq_test_id'::text)
name | text | default 'default name from view'
View definition: SELECT test.id, test.name FROM test WHERE (test.id > 10);
Rules: r_test_ins,
r_test_upd,
r_test_del

test=# insert into test_view(name) values('new ins name');
INSERT 2393020 1

2 OAN: Имеется в виду что-то наподобие такого?

test=# create or replace rule r_test_ins AS on insert to test_view do instead
test-# insert into test (id,name) select case when new.id is null then
test-# nextval('seq_test_id') else new.id end, case when new.name is null
test-# then 'default view name' else new.name end;
CREATE RULE
test=# insert into test_view(name) values('test');
INSERT 2397669 1
OAN
Дата: 29.04.2004 20:31:32
да, именно так.

а еще есть второй вариант - повесить триггер на изменение/обновление на таблицу и там добавлять чего не хватает в значениях. именно этот способ нашел как наиболее удобный. так можно заодно сделать "хитрые" проверки возможности записи и сложные вычисления.

я долго пытался разобраться с правилами, и мне думается, что параметр "DEFAULT ..." в описании структуры таблиц просто добавляет правило с "CASE WHEN ... IS NULL THEN ... END" на значение поля перед записью данных.