ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / как организовать хранение кода пользователя
17 сообщений из 17, страница 1 из 1
как организовать хранение кода пользователя
    #39820148
Swa111
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Swa111
Гость
Здравствуйте!
Версия СУБД 9,5.
в базе очень многое зависит от кода пользователя которое хранится в БД в таблице t_users(id, sUserNameBD...).
для определения кода используется функция с запросом
Код: plsql
1.
select id from t_users where sUserNameBD = session_user;



Функция эта используется во многих местах (аудит, хранение настроек, доступность тех или иных элементов) и не смотря на то что запрос выполняется очень быстро в среднем < 0.01 мс, (при больших нагрузках правда подвисает на несколько мс из-за ожидания доступа к диску), в статистике выполнения запросов по общему времени занимает место в топе из за того что вызывается миллионы раз.

Суть вопроса в следующем как можно организовать хранение кода пользователя, по возможности без использования таблиц и без снижения безопасности.

Сначала хотел сделать так: так как код не меняется в рамках сессии то при первом обращении находить по таблице, а затем создавать временную функцию в pg_temp, но в этом случае возможно компрометация и/или подмена.
...
Рейтинг: 0 / 0
30.05.2019, 07:12
| Ответить | Цитировать | Написать  
как организовать хранение кода пользователя
    #39820287
Фотография Scott Tiger
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Scott Tiger
Участник
У Вас вся логика в хранимых процедурах в БД?
...
Рейтинг: 0 / 0
30.05.2019, 12:35
| Ответить | Цитировать | Написать  
как организовать хранение кода пользователя
    #39820328
Swa111
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Swa111
Гость
Scott Tiger,

Именно так.
...
Рейтинг: 0 / 0
30.05.2019, 14:23
| Ответить | Цитировать | Написать  
как организовать хранение кода пользователя
    #39820338
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Swa111,

Везде где сталкивался, код хранился в репозитории (cvs, git) и были утилиты, чтобы его дампнуть из базы (разработческой к пимеру) и чтобы его загрузить в базу.
...
Рейтинг: 0 / 0
30.05.2019, 14:43
| Ответить | Цитировать | Написать  
как организовать хранение кода пользователя
    #39820375
Павел Лузанов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Лузанов
Участник
Swa111,

Какая категория изменчивости у функции (volatile, stable, immutable)?
Если функция только читает данные, то должно быть stable.

В принципе, с учетом "так как код не меняется в рамках сессии" можно попробовать и immutable поставить. Но есть риски, если предположение не оправдается.
...
Рейтинг: 0 / 0
30.05.2019, 15:53
| Ответить | Цитировать | Написать  
как организовать хранение кода пользователя
    #39820379
Swa111
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Swa111
Гость
Павел Лузанов,

Функция помечена как stable. Насчет Immutable у меня есть некоторые опасения (может напрасные) ведь для разных пользователей результат будет разный, т.е. нарушается принцип что Immutable должна гарантировано возвращать один и тот же результат.
...
Рейтинг: 0 / 0
30.05.2019, 16:03
| Ответить | Цитировать | Написать  
как организовать хранение кода пользователя
    #39820398
Павел Лузанов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Лузанов
Участник
Swa111,

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

Другое дело, что постгрес имеет право закэшировать результат вызова функции и больше функцию с этим параметром не вызывать (имеет право, но не обязан). Собственно это вам и нужно. Но если вы всё-таки поменяете справочник, то функция до конца сессии может выдавать старое, закэшированное, значение.
...
Рейтинг: 0 / 0
30.05.2019, 16:26
| Ответить | Цитировать | Написать  
как организовать хранение кода пользователя
    #39820402
Павел Лузанов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Лузанов
Участник
Swa111,

У вас похоже нет параметров у функции. Увидел, что в запросе session_user стоит. Это меняет дело.
...
Рейтинг: 0 / 0
30.05.2019, 16:30
| Ответить | Цитировать | Написать  
как организовать хранение кода пользователя
    #39820405
Павел Лузанов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Лузанов
Участник
Swa111,

А покажите текст функции целиком, вместе с create function.
На каком она языке?

Если функция состоит из одного запроса, то возможно её следует оформить на sql. Тогда планировщик сможет раскрывать текст запроса из функции и вставлять его в основной запрос.
Поможет/не поможет - сложно сказать, но количество вызовов функций точно уменьшится.
...
Рейтинг: 0 / 0
30.05.2019, 16:35
| Ответить | Цитировать | Написать  
как организовать хранение кода пользователя
    #39820407
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Swa111 код не меняется в рамках сессии то при первом обращении находить по таблице, а затем создавать временную функцию в pg_temp, но в этом случае возможно компрометация и/или подмена.
каким образом ?
черканите код кейса на пальцах


и да, замеры приведите, за какие времена боретесь

чтобы предметно пофантазировать в разные стороны. например :
 
Код: sql
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.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
deallocate all;

prepare foo_current_user(name ) as select 12345 where current_user=$1;

execute foo_current_user(current_user);

do $$
declare
	_in timestamp ;
begin
	_in := clock_timestamp();
	for i in 1..100000 loop
		EXECUTE 'execute foo_current_user(current_user);';
	end loop;
	raise notice '% ' ,  clock_timestamp() - _in;
end;
$$
;

prepare foo_current_session as select 1234567 ;

execute foo_current_session;
do $$
declare
	_in timestamp ;
begin
	_in := clock_timestamp();
	for i in 1..100000 loop
		EXECUTE 'execute foo_current_session;';
	end loop;
	raise notice '% ' ,  clock_timestamp() - _in;
end;
$$
;
----------------------------------------------------
NOTICE:  00:00:00.569828
результат запроса (1 строка) отброшен.

NOTICE:  00:00:00.38019
результат запроса (1 строка) отброшен.



кеш-таблицу , на которую завязана иммутабная функция можно принудительно обновить, вызвав create or replace тела ф-ии (не изменив его по факту) сразу же после добавления записи (например в триггере). но это чревато тем, что в ранее запущенных сессиях вылетят ошибки чтения кеша (сейчас уже не вспомню код ошибки)
...
Рейтинг: 0 / 0
30.05.2019, 16:38
| Ответить | Цитировать | Написать  
как организовать хранение кода пользователя
    #39820408
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Павел ЛузановSwa111,

А покажите текст функции целиком, вместе с create function.
На каком она языке?

Если функция состоит из одного запроса, то возможно её следует оформить на sql. Тогда планировщик сможет раскрывать текст запроса из функции и вставлять его в основной запрос.
Поможет/не поможет - сложно сказать, но количество вызовов функций точно уменьшится.
она, если я верно помню, д.б. секьюрити-инвокер. а табла кодов -- явно не публичная. прокладываться придётся
...
Рейтинг: 0 / 0
30.05.2019, 16:41
| Ответить | Цитировать | Написать  
как организовать хранение кода пользователя
    #39820442
Swa111
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Swa111
Гость
qwwq,

По производительности смотрел в pg_stat_statements в функции есть два запроса
calls: 946 000 000
total_time: 14 506 000 мс + 66 658 000 мс
min_time: 0.07 мс
max_time: 44 000 мс
mean_time 0.08 мс
stddev_time: 1.55 мс

И занимают 3 место

На функции в схеме pg_temp можно организовать атаку следующим образом. В приложении начинается сессия, после используя уязвимости (я не идеалист, да и в бинарник влезть не могу) делается условный create or replace function pg_temp.getCurentUser().
Либо каким либо образом сразу после начала сессии, но до первого вызова admin.getCurrentUser() создать функцию pg_temp.getCurentUser() из за чего определение легального пользователя будет отменено. Хотя возможно заморачиваюсь.


Павел Лузанов,

Функция на plpgsql,
да функция не имеет параметров поэтому у меня сомнения по поводу immutable и возникли.

Для понимания, можете объяснить как работает интерпретатор plPgSQL? Есть допустим функция foo которая использует другую bar с признаком immutable. Есть ли такое понятие как компиляция функций? т.е. при create or replace function foo создается ли какой либо промежуточный код, в который встроен результат функции bar? Общий ли этот код между сессиями? или же это промежуточное состояние вычисляется при первом вызове в сессии?

Может тогда действительно объявить ее как immutable и это снимет проблему.
...
Рейтинг: 0 / 0
30.05.2019, 17:36
| Ответить | Цитировать | Написать  
как организовать хранение кода пользователя
    #39820459
Павел Лузанов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Лузанов
Участник
Swa111,

Код plpgsql функций не компилируется, но для immutable функций постгрес может при первом вызове в сессии запомнить результат и дальше его использовать без повторного вызова функции.

А может и повторно вызывать функцию в запросе, но один раз, а не для каждой строки.
Вот похожий пример. После того как функция стала immutable она выполнилась только 1 раз в запросе, вместо 10000 пока была stable:
Код: sql
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.
postgres=# set track_functions = 'all';
SET
postgres=# create function get_userid() returns int as 'begin return (select oid::int from pg_authid where rolname = session_user); end;' language plpgsql stable;
CREATE FUNCTION
postgres=# select max(x) from (select get_userid() from generate_series(1,10000)) as f(x);
 max 
-----
  10
(1 row)

postgres=# select * from pg_stat_user_functions where funcname = 'get_userid';
 funcid | schemaname |  funcname  | calls | total_time | self_time 
--------+------------+------------+-------+------------+-----------
 236672 | public     | get_userid | 10000 |     80.441 |    80.441
(1 row)

postgres=# alter function get_userid() immutable;
ALTER FUNCTION
postgres=# select max(x) from (select get_userid() from generate_series(1,10000)) as f(x);
 max 
-----
  10
(1 row)

postgres=# select * from pg_stat_user_functions where funcname = 'get_userid';
 funcid | schemaname |  funcname  | calls | total_time | self_time 
--------+------------+------------+-------+------------+-----------
 236672 | public     | get_userid | 10001 |      80.68 |     80.68
(1 row)


Но нужно признать, c immutable мы здесь обманываем постгрес.
...
Рейтинг: 0 / 0
30.05.2019, 18:50
| Ответить | Цитировать | Написать  
как организовать хранение кода пользователя
    #39820487
Swa111
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Swa111
Гость
Павел Лузанов,

то есть если объявлю функции как immutable конфликтов между сессиями не будет, точнее не будет такого варианта что Пользователь А вызвал функцию, ее результат запомнился и когда пользователь Б таже вызывает он получит результат из кеша для пользователя А?

Основную часть вызовов получаю из запросов типа

Код: plsql
1.
select value from vParam where sname = 'Parameter1' order by nOrder limit 1



где vparam это вьюха вида
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select 
  case when iduser is not null then 0
       when idmodule is not null then 1
       else 2 
  end nOrder,
  id, value, sname
from tParam 
where 
  (idUser is null or idUser = getCurentUser())
  and (idModule is null or idModule = getCurentModule())
...
Рейтинг: 0 / 0
30.05.2019, 21:38
| Ответить | Цитировать | Написать  
как организовать хранение кода пользователя
    #39820561
GeniyZ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
GeniyZ
Гость
Так-же, тоже как вариант, в данном случае можно попробовать set_config / current_setting.

Ну или как-то так, если очень надо обернуть своей функцией:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
create function get_userid(
  ) returns bigint
  language plpgsql
  IMMUTABLE 
as $$
  declare
    rez bigint;
  begin
    rez := current_setting('UserId');
    if rez is null then
        select id into rez from t_users where sUserNameBD= session_user;
        set_config ('UserId', rez);
    end if;
    return rez;
  end;
$$;
...
Рейтинг: 0 / 0
31.05.2019, 08:59
| Ответить | Цитировать | Написать  
как организовать хранение кода пользователя
    #39820582
GeniyZ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
GeniyZ
Гость
Swa111,

immutable - значит, что результат зависит только от её параметров.
Она выполняется вообще один раз как только вычислен параметр а если он не зависит от данных БД (или, как в данном случае - его вообще нет), то вообще посчитается на этапе планирования запроса.

Не знаю. На моих тестах - значения между сессиями не пересекаются.
Но полагаю, что для надёжности, лучше, в качестве параметра передавать session_user.
...
Рейтинг: 0 / 0
31.05.2019, 09:40
| Ответить | Цитировать | Написать  
как организовать хранение кода пользователя
    #39820760
Павел Лузанов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Лузанов
Участник
Swa111то есть если объявлю функции как immutable конфликтов между сессиями не будет, точнее не будет такого варианта что Пользователь А вызвал функцию, ее результат запомнился и когда пользователь Б таже вызывает он получит результат из кеша для пользователя А?

Такого не будет.

Но не заьывайте, что:
https://postgrespro.ru/docs/postgresql/9.5/xfunc-volatility Вообще в функциях IMMUTABLE обычно неразумно выбирать данные из таблиц, так как «постоянство» функции будет нарушено, если содержимое таблиц изменится. Однако PostgreSQL не принуждает вас явно отказаться от этого.
...
Рейтинг: 0 / 0
31.05.2019, 13:49
| Ответить | Цитировать | Написать  
17 сообщений из 17, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / как организовать хранение кода пользователя
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&gotonew=1&tid=1995180]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 53ms
get topic data:
 14ms
get first new msg:
 9ms
get forum data:
 3ms
get page messages:
 60ms
get tp. blocked users:
 2ms
others: 12ms
total:  186ms
созд. / загр.: 186ms
x
x
Закрыть


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