Блог

У меня есть база данных PG и я создаю две роли:

1. Роль полного доступа к базе данных и всем схемам
2. Доступ только для чтения ко всем схемам

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

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

Затем я пытаюсь получить доступ к таблице (выбрать) как пользователь только для чтения и получаю отказ в разрешении.

Я уже пробовал следующие фрагменты кода:

 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO READONLY_ROLE;
ALTER DEFAULT PRIVILEGES FOR ROLE FULL_ROLE IN SCHEMA public GRANT SELECT ON TABLES TO READONLY_ROLE;
  

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

Разве невозможно работать с таким подходом, основанным на ролях? Возможно ли изменить владельца таблиц по умолчанию, а также таблиц функций, на полную роль, а не на пользователя (без «УСТАНОВИТЬ РОЛЬ» перед «СОЗДАТЬ ТАБЛИЦУ»)?

Обновление 25.04.2019

Вот инструкции sql для создания ролей и пользователей:

Создать роль записи:

 CREATE ROLE write_role;
GRANT ALL PRIVILEGES ON DATABASE test_db TO write_role;
GRANT ALL ON SCHEMA public TO write_role;
  

Создать роль чтения:

 CREATE ROLE read_role;
GRANT CONNECT ON DATABASE test_db TO read_role;
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO read_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_role;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_role;
ALTER DEFAULT PRIVILEGES FOR ROLE write_role IN SCHEMA public GRANT SELECT ON TABLES TO read_role;
  

Создать пользователя для записи:

 CREATE USER write_user WITH ENCRYPTED PASSWORD '';
GRANT write_role TO write_user;
  

Создать пользователя для чтения:

 CREATE USER read_user WITH ENCRYPTED PASSWORD '';
GRANT read_role TO read_user;
  

Создайте таблицу как write_user и вставьте данные:

 CREATE TABLE link (
 ID serial PRIMARY KEY,
 url VARCHAR (255) NOT NULL,
 name VARCHAR (255) NOT NULL,
 description VARCHAR (255),
 rel VARCHAR (50)
);
INSERT INTO link (url, name)
VALUES
 ('http://www.postgresqltutorial.com','PostgreSQL Tutorial');
  

Информация о таблице:

 dt
                 List of relations
 Schema | Name | Type  |           Owner
-------- ------ ------- ---------------------------
 public | link | table | write_user
  

Выберите как read_user:

 $ select * from link;
ERROR:  permission denied for table link
  

Обновление 01.05.2019

Благодаря коллеге на работе мы нашли решение не по плану A, а по плану Z, которое работает. Эта статья послужила источником вдохновения: Изменить владельца с помощью триггера

Причина, по которой эта статья также очень важна, заключается в том, что другие пользователи с write_role также не могут удалять таблицы, созданные другим пользователем.

Вот триггер события, который я немного адаптировал:

 CREATE OR REPLACE FUNCTION trg_create_set_owner()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $$
DECLARE
  obj record;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag='CREATE TABLE' LOOP
    EXECUTE format('ALTER TABLE %s OWNER TO write_role', obj.object_identity);
    EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %s to read_role', obj.schema_name);
  END LOOP;
END;
$$;

CREATE EVENT TRIGGER trg_create_set_owner
 ON ddl_command_end
 WHEN tag IN ('CREATE TABLE')
 EXECUTE PROCEDURE trg_create_set_owner();
  

Несколько вещей, которые нужно проверить:

• Вы создали таблицу в общедоступной схеме? Если нет, имеет ли READONLY_ROLE права на использование в схеме, в которой находится таблица?
• ИЗМЕНЕНИЕ ПРИВИЛЕГИЙ ПО УМОЛЧАНИЮ изменяет привилегии только для объектов, созданных после запуска. Вы создали свою таблицу перед запуском этого?
• Вы пытаетесь выбрать из таблицы с readonly_role или пользователя, которому назначена readonly_role? Если последнее, наследует ли пользователь привилегии (по умолчанию)?

РЕДАКТИРОВАТЬ: Спасибо за всю дополнительную информацию. ИЗМЕНЕНИЕ ПРИВИЛЕГИЙ ПО УМОЛЧАНИЮ работает только для одной роли и не наследуется (если пользователь был членом нескольких ролей с разными привилегиями по умолчанию, я думаю, было бы нетривиально выяснить, какие привилегии применять к новым объектам).

Перед созданием таблицы от имени пользователя для записи просто запустите:

 SET ROLE write_role;