Добавление нового пользователя в PostgreSQL с передачей ему всех прав от старого

[de_gis]

Достаточно часто программисты на «лабниках» и тестовых средах без затей создают первые версии базы данных от дефолтного пользователя «postgres». Через некоторое время приходит понимание, что  так стыдно и надо бы завести отдельного пользователя под проект. Казалось бы - «не бином Ньютона» - заходим в psql консоли и поехали:



Для примера наша база данных называется "our-datalake" и в ней есть 2 схемы - public и bigdata

CREATE USER "new-boss" WITH PASSWORD '322223';
GRANT ALL PRIVILEGES ON DATABASE "our-datalake" to "new-boss";
\c our-datalake
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "new-boss";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA bigdata TO "new-boss";

Запускаем свои скрипты/программы и ОПС!

Query failed: ERROR: permission denied for schema - Could not truncate

Оказывается, мы не можем «транкать» таблицы и т.п., потому что нет прав на использование (usage) :) Ладно, колдуем дальше

GRANT USAGE ON SCHEMA bigdata TO "new-boss";
GRANT USAGE ON SCHEMA public TO "new-boss";
GRANT USAGE ON ALL SEQUENCES IN SCHEMA bigdata TO "new-boss";
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO "new-boss";
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA bigdata TO "new-boss";
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO "new-boss";

Запускаем свои скрипты/программы и ОПС!

Query failed: ERROR: must be owner of sequence

А владелец-то таблиц, последовательностей, «вьюх» и т.п. - всё ещё «старый» пользователь. А команды «со звёздочкой», чтобы сменить владельца махом нет.

Колдуем дальше. Нам надо выполнить «пачку» SQL-команд в консоли.

(1) Получить список SQL-команд для передачи прав владельца для таблиц ВО ВСЕХ СХЕМАХ(!)

SELECT 'ALTER TABLE '|| schemaname || '.' || tablename ||' OWNER TO "new-boss";' FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema') ORDER BY schemaname, tablename;

На выходе будет список команд ALTER TABLE, которые копируем и запускаем на исполнение.

(2) Получить список SQL-команд для передачи прав владельца для последовательностей ВО ВСЕХ СХЕМАХ(!)

SELECT 'ALTER SEQUENCE '|| sequence_schema || '.' || sequence_name ||' OWNER TO "new-boss";' FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog','information_schema') ORDER BY sequence_schema, sequence_name;

На выходе будет список команд ALTER SEQUENCE , которые копируем и запускаем на исполнение.

(3) Получить список SQL-команд для передачи прав владельца для «вьюх» ВО ВСЕХ СХЕМАХ(!)

SELECT 'ALTER VIEW '|| table_schema || '.' || table_name ||' OWNER TO "new-boss";' FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema') ORDER BY table_schema, table_name;

На выходе будет список команд ALTER VIEW, которые копируем и запускаем на исполнение.

(4) Получить список SQL-команд для передачи прав владельца для материализованных представлений ВО ВСЕХ СХЕМАХ(!)

SELECT 'ALTER TABLE '|| oid::regclass::text ||' OWNER TO "new-boss";' FROM pg_class WHERE relkind = 'm' ORDER BY oid;

На выходе будет список команд ALTER TABLE, которые копируем и запускаем на исполнение.