Введение
В предыдущей статье мы рассматривали вопросы подключения и глобальных привилегий пользователей сервера PostgreSQL. Будем считать что головную боль админов в виде pg_hba.conf мы прошли, переходим к следующей.
DBA против Разработчиков, DML против DDL. Сразу хочу сказать, что мое мнение: привилегии DDL разработчику не нужны, несмотря на то, что я сам разработчик.
Каждый уважающий себя разработчик хоть раз да накидывал фекалии на вентилятор выполняя команду:
ALTER TABLE big_data
ADD COLUMN have_fun BOOLEAN
NOT NULL DEFAULT TRUE;А потом не спеша перемещаясь к админам с фразой: “Что-то бекенд в бою тупить начал”.
Правда начиная с 11 версии этот фокус уже не пройдет, да и привилегии DML не мешают выполнить команду:
UPDATE big_data
SET have_fun =
CASE WHEN have_fun IS TRUE
THEN FALSE
ELSE TRUE
END;Но тем не менее, прод или боевая среда она на балансе эксплуатации, и ответственность таки за нее несут админы, а не разработчики.
Общие правила
Для начала определим базовое правило: все объекты всех баз данных принадлежат пользователю postgres. Исключением могут быть объекты которые мы будем рассматривать в каскадных привилегиях.
Опять же, если схема данных разворачивается или обновляется приложением, что потребуется временно выставить владельцем пользователя приложения на время разворачивания или обновления. После чего вернуть все на место.
Для удобства можно создать функцию обновления владельца:
CREATE OR REPLACE FUNCTION public.dba_flush_owner (
set_owner_in text = 'postgres'::text
)
RETURNS void AS
$body$
DECLARE
schema_name TEXT;
schema_owner TEXT;
set_owner TEXT;
obj_type TEXT;
obj_name TEXT;
obj_owner TEXT;
BEGIN
IF ( SELECT current_user ) <> 'postgres' THEN
RAISE EXCEPTION 'Only for postgres user';
END IF;
FOR schema_name, schema_owner IN
SELECT "iss"."schema_name", "iss"."schema_owner"
FROM "information_schema"."schemata" AS iss
-- Это условие я сначала поставил, но закомментировал по причине того,
-- что мало ли умник найдется и на системные схемы поставит левого owner
-- WHERE "iss"."schema_name" NOT LIKE 'pg_%' AND
-- "iss"."schema_name" <> 'information_schema'
LOOP
RAISE NOTICE 'Schema % owner is %', schema_name, schema_owner;
IF schema_name LIKE 'pg_%' OR schema_name = 'information_schema' THEN
set_owner := 'postgres';
ELSE
set_owner := set_owner_in;
END IF;
-- Выравниваем владельца если он не такой как надо:
IF set_owner <> schema_owner THEN
RAISE NOTICE '>> Schema % set owner to %', schema_name, set_owner;
EXECUTE 'ALTER SCHEMA "' || schema_name || '"' ||
' OWNER TO ' || set_owner;
END IF;
-- Владельцы таблиц, представлений и последовательностей
FOREACH obj_type IN
ARRAY '{table, view, sequence}'::text[]
LOOP
FOR obj_name, obj_owner IN
EXECUTE
'SELECT "obj"."' || obj_type ||'name", "obj"."' || obj_type || 'owner"
FROM "pg_' || obj_type || 's" AS "obj"
WHERE "obj"."schemaname" = ''' || schema_name || ''''
LOOP
RAISE NOTICE '>>>> % %.% owner is %', obj_type, schema_name, obj_name, obj_owner;
IF set_owner <> obj_owner THEN
RAISE NOTICE '>>>>>> % %.% set owner to %', obj_type, schema_name, obj_name, set_owner;
EXECUTE 'ALTER ' || obj_type || ' "' || schema_name || '"."' || obj_name || '"' ||
' OWNER TO ' || set_owner;
END IF;
END LOOP;
END LOOP;
-- Владельцы функций
FOR obj_name, obj_owner IN
SELECT
'"' || n."nspname" || '"."' || p."proname" ||
'"(' || "pg_catalog"."pg_get_function_identity_arguments"(p."oid") || ')',
( SELECT u."usename"
FROM "pg_user" AS u
WHERE u."usesysid" = p."proowner" )
FROM "pg_catalog"."pg_proc" AS p
JOIN "pg_catalog"."pg_namespace" n
ON n."oid" = p."pronamespace"
AND n."nspname" = schema_name
LOOP
RAISE NOTICE '>>>> function % owner is %', obj_name, obj_owner;
IF set_owner <> obj_owner THEN
RAISE NOTICE '>>>>>> function % set owner to %', obj_name, set_owner;
EXECUTE 'ALTER FUNCTION ' || obj_name ||
' OWNER TO ' || set_owner;
END IF;
END lOOP;
END LOOP;
-- Далее: домены, типы, операторы. Если они используются
-- ...
-- Смешная третья опция
REVOKE ALL ON SCHEMA public FROM PUBLIC;
EXECUTE 'REVOKE ALL ON DATABASE ' || ( SELECT current_database() ) || ' FROM PUBLIC';
FOR obj_name IN
SELECT lanname FROM pg_language WHERE lanpltrusted IS TRUE
LOOP
EXECUTE 'REVOKE ALL ON LANGUAGE ' || obj_name ||' FROM PUBLIC';
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql';И вызывать её:
SELECT * FROM public.dba_flush_owner( [ user name ] );Сразу возникает вопрос, что же за такая “Смешная третья опция”, а вот что:
Смешная третья опция
Для сначала проведем эксперимент. Заходим под администратором и создаем какую-нибудь роль:
# psql -U postgres -h 127.0.0.1 -p 5432 postgres
postgres=# CREATE ROLE foo LOGIN PASSWORD '12345' IN ROLE projects;
CREATE ROLE
postgres=# \qУ нас получился пользователь foo который может регистрироваться в данном экземпляре PostgreSQL (не базы!) с паролем 12345.
А теперь заходим новым пользователем в базу postgres и создаем какую-нибудь таблицу:
# psql -U foo -h 127.0.0.1 -p 5432 -W postgres
postgres=> CREATE TABLE test (id integer);
CREATE TABLE
postgres=> INSERT INTO test VALUES (1);
INSERT 0 1
postgres=> SELECT * FROM test;
id
----
1
(1 row)
postgres=> DROP TABLE test;
DROP TABLE
postgres=>\qКазалось бы, привилегий никаких у пользователя foo нет, но этот пользователь может спокойно логиниться в любую базу, создавать объекты в схеме public и делать с ними все что угодно. Что за дыра в безопасности?
Весь вопрос в ключевом слове PUBLIC, точнее это системная группа которая применяется ко всем без исключения пользователям и которая в свою очередь имеет GRAND CONNECT и TEMPORARY на все базы данных и GRAND USAGE и CREATE на схему public в них.
Поэтому лучше отключить эти привилегии для всех баз данных. Причем отменить правила по-умолчанию для PUBLIC не получится, поэтому при создании новой базы данных требуется сбрасывать привилегии:
mybase=> REVOKE ALL ON SCHEMA public FROM PUBLIC;
mybase=> REVOKE ALL ON DATABASE mybase FROM PUBLIC;Причем так как мы указали в pg_hba.conf, что пользователи приложений могут соединяться со любыми базами данных (any), отключение CONNECT по-умолчанию — более чем обязательно.
Да, там еще есть привилегии по-умолчанию типа USAGE на все процедурные языки, что значит, что пользователь может создавать функции, но только в том случае если у него есть привилегия CREATE на какую-либо схему.
Простые привилегии пользователей
Рассмотрим простую, но достаточно эффективную схему расстановки привилегий пользователям приложений которую я использую повсеместно, за исключением особенных случаев.
Для начала определимся с типами операций:
- DDL (Data Definition Language) — язык описания данных. К этим операциям относятся:
- CREATE;
- ALTER;
- DROP;
- TRUNCATE — я отношу очистку ка DDL операциям потому как по сути — это DROP + CREATE;
- DML (Data Manipulation Language) — язык манипулирования данными. К этим операциям относятся:
- SELECT;
- INSERT;
- UPDATE;
- DELETE;
- USAGE — ну не совсем, но пусть побудет здесь;
По факту, для пользователя приложения DDL операции в принципе не нужны, поэтому оставляем только DML, но с некоторыми оговорками, итак привилегии:
Для базы данных
- CREATE — возможность создавать схемы данных — DDL, отключаем;
- CONNECT — возможность подключения к данной базе. Да, если мы не отключили “смешную третью опцию” то эта привилегия бессмысленна, так как предоставляется по-умолчанию всем и для всех. В противном случае предоставляем эту привилегию на соответствующую базу соответствующему пользователю;
- TEMPORARY — возможность создавать временные таблицы — да, в большинстве случаев это нужно, несмотря на то, что это таки DDL операция;
GRANT CONNECT, TEMPORARY
ON DATABASE my_db
TO my_role;
REVOKE CREATE
ON DATABASE my_db
FROM my_role;Для схем данных
- CREATE — возможность создавать объекты в схеме — DDL, отключаем;
- USAGE — возможность доступа к объектам схемы — да, выдаем;
Для всех схем:
GRANT USAGE
ON SCHEMA my_schema
TO my_role;
REVOKE CREATE
ON SCHEMA my_schema
FROM my_role;Для таблиц и представлений
- SELECT — Возможность выборки записей из таблицы, включаем;
- INSERT — Возможность вставки записи в таблицу, включаем;
- UPDATE — Возможность обновления записи в таблице, включаем;
- DELETE — Возможность удаления записей из таблицы, включаем;
- TRUNCATE — Полная очистка таблицы. По сути это DROP + CREATE TABLE и работает очень быстро и безальтернативно. Я на самом деле я отключаю эту привилегию для пользователя приложений, так как функциональность полного сброса данных в приложении крайне редка. Устанавливать стоит только для тех таблиц, где это действительно требуется и где используется именно команда TRUNCATE;
- REFERENCES — Возможность создания ограничения внешнего ключа. Это DDL операция — отключаем;
- TRIGGER — Возможность создавать триггеры для данной таблицы, так же DDL операция, отключаем;
Для всех схем данных:
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES
IN SCHEMA [ name ]
TO my_role;
REVOKE TRUNCATE, REFERENCES, TRIGGER
ON ALL TABLES
IN SCHEMA [ name ]
FROM my_role;Для последовательностей
- SELECT — Возможность получения текущего значения последовательности в рамках сессии(!) currval. DML, включаем;
- UPDATE — Возможность изменять значение последовательности, в частности: setval. DML операция, можно включать, только быть аккуратным с setval, что бы не было конфликтов;
- USAGE — Возможность использовать последовательность, собственно currval и nextval. Для подавляющего большинства задач достаточно этой привилегии, да включаем;
Для всех схем данных:
GRANT ALL PRIVILEGES
ON ALL SEQUENCES
IN SCHEMA [ name ]
TO my_role;Отменять никаких привилегий для последовательностей не требуется, как как больше для них привилегий и нет;
Для функций
Так как мы не отключали привилегию EXECUTE на PUBLIC, то, в принципе, устанавливать или убирать ничего дополнительно не требуется.
Прочее
Остальные объекты расписывать не буду, что бы не погружаться сильно в детали, но все можно почерпнуть из документации, при этом четко себе представляя, какие именно привилегии требуются приложению, а какие нет.
Базовая документация находится по адресам:
Так же можно написать хранимую процедуру для сброса и установки правильных привилегий (при условии, что роль не является владельцем):
CREATE OR REPLACE FUNCTION public.dba_flush_privileges (
set_privileses_in text = 'postgres'::text
)
RETURNS void AS
$body$
DECLARE
schema_name TEXT;
BEGIN
IF ( SELECT current_user ) <> 'postgres' THEN
RAISE EXCEPTION 'Only for postgres user';
END IF;
IF set_privileses_in = 'postgres' THEN RETURN; END IF;
-- DATABASE GRANT
EXECUTE 'GRANT CONNECT, TEMPORARY ON DATABASE ' || current_database() || ' TO ' || set_privileses_in;
EXECUTE 'REVOKE CREATE ON DATABASE ' || current_database() || ' FROM ' || set_privileses_in;
FOR schema_name IN
SELECT "iss"."schema_name"
FROM "information_schema"."schemata" AS iss
-- только для не системных схем
WHERE "iss"."schema_name" NOT LIKE 'pg_%' AND
"iss"."schema_name" <> 'information_schema'
LOOP
EXECUTE 'GRANT USAGE ON SCHEMA ' || schema_name || ' TO ' || set_privileses_in;
EXECUTE 'REVOKE CREATE ON SCHEMA ' || schema_name || ' FROM ' || set_privileses_in;
EXECUTE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ' || schema_name || ' TO ' || set_privileses_in;
EXECUTE 'REVOKE TRUNCATE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA ' || schema_name || ' FROM ' || set_privileses_in;
EXECUTE 'GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA ' || schema_name || ' TO ' || set_privileses_in;
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql';И вызываем её:
SELECT * FROM public.dba_flush_privileges( [ user name ] );Каскадные привилегии пользователей
Для определенных решений требуется более сложные схемы привилегий и доступов. Например мы вынесли часть или всю бизнес-логику на уровень базы данных, и взаимодействие с определенными объектами баз данных жестко регламентировано. Для этого можно воспользоваться параметром функций: SECURITY DEFINER. Этот параметр говорит о том, что данная функция будет вызываться с привилегиями её владельца.
Так, создается дополнительная роль без возможности регистрации (LOGIN) и остального. Этой роли предоставляем доступ к DML операциям над требуемыми объектами. После чего, создаем функцию с этой ролью в качестве владельца и указываем что вызываться она будет с привилегиями создателя.
При этом, важно понимать, что по-умолчанию вызвать любую функцию могут все, поэтому требуется отключить данную привилегию у группы PUBLIC для каждой схемы данных или только для определенных:
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA [ schema name ] FROM PUBLIC;Что особенно важно, если у нас несколько уровней логики, например:
- на первом уровне производятся некоторые базовые (атомарные) операции со стоками таблиц:
- создаем роль project_base;
- предоставляем ей привилегии на DML операции с таблицами;
- создаем функции для базовых операций с таблицами добавляя дополнительную логику, при этом указываем в качестве владельца роль project_base и добавляем параметр SECURITY DEFINER;
- на втором уровне производятся бизнес операции приложения:
- создаем роль project_business;
- предоставляем ей привилегии на выполнение функций базовых операций;
- создаем функции для бизнес операций которые работают с данными через прослойку базовых функций, при этом указываем в качестве владельца роль project_base и добавляем параметр SECURITY DEFINER;
- на третьем уровне приложение вызывает бизнес операции с определенными параметрами:
- для обычной роли приложения (пользователя) указываем права на выполнение функций бизнес-операций;
На практике же я первый уровень базовых операций выношу на уровень триггеров, что бы не мудрить со сложными схемами привилегий.