Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / получить список функций через sql / 9 сообщений из 9, страница 1 из 1
08.05.2007, 16:56
    #34513426
tadmin
tadmin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
получить список функций через sql
Задача - отозвать все права на таблицы из схемы для роли, и для нее же дать права на все функции в этой схеме.

Поскольку grant execute к схеме не применимо, придется строить список функций

первое, на что наткнулся
select * from pg_proc;
это работает, но нужно еще фильтровать по pronamespace.

опытным путем выяснил, что
select * from pg_proc where prolang > 16;
возвращает лишь пользовательские функции, но
select * from pg_proc where pronamespace =???;

где найти pronamespace для моей схемы данных?

select * from pg_namespace ;
дает лишь названия схем, но не идентификаторы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.05.2007, 21:22
    #34513989
aov
aov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
получить список функций через sql
вот вроде как немного сырая заготовка - терпения не хватило дописать полностью.
ета супер-функция рубает все права на таблицы из указаной в параметре схемы для указаной параметром роли - и раздаёт для этой же схемы все права для таблицы. недоработка - с функциями это работает токо с теми, которые без параметров. todo: к названию функций добавить параметры их прикрутить - так же лупом.
вроде рабочее всё - что обещано :) - токо не оч проверено. но с правами при этом таки происходит что-то :). но нормально не проверял. однако думаю работает.
вот код:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
CREATE OR REPLACE FUNCTION test.test_grant_revoke(schema_name character varying, login_name character varying)
  RETURNS text AS
$BODY$
declare
	rw record; msg text;
begin
	msg='TABLES: ';
	for rw in select table_name from information_schema.tables where table_type='BASE TABLE' and table_schema=schema_name loop
		raise notice 'table name: %',rw.table_name;
		msg=msg || rw.table_name || ', ';
		execute 'revoke all on ' || schema_name || '.' || rw.table_name || ' from ' || login_name;
	end loop;
	msg=msg || ' ROUTINES: ';
	for rw in select routine_name from information_schema.routines where routine_schema=schema_name loop
		raise notice 'routine name: %',rw.routine_name;
		msg=msg || rw.routine_name || ', ';
		execute 'grant all on function ' || schema_name || '.' || rw.routine_name || '() to ' || login_name;
	end loop;

	return msg;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.05.2007, 22:39
    #34514057
.gc
.gc
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
получить список функций через sql
tadmin
возвращает лишь пользовательские функции, но
select * from pg_proc where pronamespace =???;

где найти pronamespace для моей схемы данных?

select * from pg_namespace ;
дает лишь названия схем, но не идентификаторы.

Код: plaintext
select oid, * from pg_namespace ;
hint: для системных таблиц и view (pg_*) атрибут oid не входит в "select *" (его надо явно указывать и для других таблиц с OID) но таки "он есть" (с) и используется в других системных таблицах :)

опытным путем выяснил, что
select * from pg_proc where prolang > 16;
возвращает лишь пользовательские функции
пользовательские функции бывают, в общем случае, и на C, и на SQL (prolang <16) - языки по умолчанию.

как, кстати, собираетесь давать права на функции? SECURITY DEFINER?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.05.2007, 11:40
    #34514382
aov
aov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
получить список функций через sql
а чем мой вариант плох - с information_schema который? там и язык и тип функции есть и точно не помню - но и ойд вроде тоже - не знаю токо зачем он вам. ну и как бы если исходить из тз - то всё именно так и работает как хотели.
мне вообще всёравно какой вы будете использовать вариант - но просто любопытно в плане может я чего пропустил? чем то мой вариант не рабочий?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.05.2007, 10:55
    #34515469
tadmin
tadmin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
получить список функций через sql
.gc
Код: plaintext
select oid, * from pg_namespace ;
hint: для системных таблиц и view (pg_*) атрибут oid не входит в "select *" (его надо явно указывать и для других таблиц с OID) но таки "он есть" (с) и используется в других системных таблицах :)

Вот спасибо, В жизни не догадался бы!
.gc
как, кстати, собираетесь давать права на функции? SECURITY DEFINER?
Да, SECURITY DEFINER
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.05.2007, 10:59
    #34515483
tadmin
tadmin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
получить список функций через sql
aovа чем мой вариант плох - с information_schema который? там и язык и тип функции есть и точно не помню - но и ойд вроде тоже - не знаю токо зачем он вам. ну и как бы если исходить из тз - то всё именно так и работает как хотели.

Пример отличный, спасибо!
aov
мне вообще всёравно какой вы будете использовать вариант - но просто любопытно в плане может я чего пропустил? чем то мой вариант не рабочий?
У меня много функций и все с параметрами, но, как вы справедливо заметили, с ними можно разобраться сходным образом.

to all: спасибо за примеры и советы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.05.2007, 13:48
    #34548400
tadmin
tadmin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
получить список функций через sql
Умные люди подсказали ключевой прием:
Код: plaintext
select oidvectortypes(proargtypes) from pg_proc;
возвращет готовый спсок аргументов через запятую.

В результате многочисленных советов получено готовое решение.
Функция получает имя схемы и имя группы пользователей, отзывает от всех функций в схеме (prolang >16 оставляет только PLPGSQL функции) права на исполнение для public, а затем дает право на исполнение для указанной группы.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
CREATE OR REPLACE FUNCTION func_grant_revoke(schema_name character varying, login_name character varying)
  RETURNS text AS
$BODY$
declare
	rw record; 
	msg text;
	s_oid int;
	st varchar;
begin

select oid::int into s_oid from pg_namespace where lower(nspname) = lower(schema_name);
	raise notice 'schema oid: %', s_oid;
	raise notice 'login_name: %', login_name;

	for rw in  
		select oid as ID, proname as Name, oidvectortypes(proargtypes) as Arguments 
		from pg_proc 
		where pronamespace = s_oid and  prolang > 16 
	loop
		st:= rw.Name || '(' || rw.Arguments ||')';
		raise notice 'routine name: % ', st;
		execute 'revoke all on function ' || st || ' from public';
		execute 'revoke all on function ' || st || ' from group "' || login_name ||'"';
		execute 'grant execute on function ' || st || ' to group "'|| login_name||'"';
	end loop;
	
	return s_oid::varchar ;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.05.2007, 14:25
    #34548584
4321
4321
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
получить список функций через sql
tadmin prolang >16 оставляет только PLPGSQL функции
гм. если вас интересуют пользовательские ф-ии, то ограничение PLPGSQL-ем непрактично. сплошь и рядом удобнее использовать SQL (т.е. у меня это 14 язык), но есть и системные SQL ф-ии, и нет гарантии, что в будущем не попадется PLPGSQL ф-я где-нито в information_schema

т.ч. могабыть посмотреть в сторону фильтрации по имени схемы:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select p.oid as ID
--,*
,oidvectortypes(proargtypes)
, proname as Name,prolang,nspname
 from pg_proc p 
INNER JOIN pg_namespace s
ON p.pronamespace=s.oid

--WHERE  prolang NOT IN(12,13)
WHERE s.nspname NOT IN('pg_catalog','pg_toast','information_schema')
ORDER BY s.nspname, prolang
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.05.2007, 14:37
    #34548632
tadmin
tadmin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
получить список функций через sql
4321[
WHERE s.nspname NOT IN('pg_catalog','pg_toast','information_schema')

ммм-да, а я не подумал.
Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / получить список функций через sql / 9 сообщений из 9, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]