SQL-Ex blog

Прежде чем начать, давайте утвердим несколько терминов:

• Роли: Имеется только один тип доверителя аутентификации в PostgreSQL, ROLE, которая существует на уровне кластера. По соглашению РОЛЬ, которая разрешает вход, считается пользователем (user), а роль, которой не разрешен вход, — группой (group). Заметьте, что хотя все еще существуют команды CREATE USER и CREATE GROUP, они просто являются псевдонимами команды CREATE ROLE.


• Объекты базы данных: Все, что может быть создано и доступно в кластере PostgreSQL, является объектом. Базы данных, схемы, таблицы, представления, процедуры, функции и т.д., к каждому из них могут применяться различные привилегии для любой роли.


• Привилегии: Типы доступа, которые могут быть предоставлены роли на объект базы данных. Часто они применяются на уровне базы данных или схемы, но всегда имеется возможность применить конкретный доступ на отдельные объекты. Привилегии, присвоенные одной роли, могут быть предоставлены другим ролям. Это обычно делается предоставлением групповых ролей (которые не могут быть входом) пользовательским ролям (которые могут быть входом), облегчая управление привилегиями.

В зависимости от вашего прошлого опыта в сфере информационной безопасности (с базами данных, операционными системами или приложениями), вам может показаться неожиданным способ, которым эти компоненты взаимодействуют друг с другом в PostgreSQL.

Повсюду с этой статье вы увидите упоминание кластера PostgreSQL. Если вы новичок в PostgreSQL, этот термин может действительно смутить вас. Это способ, который в PostgreSQL относится к конкретному серверу/экземпляру, запущенному на хостинге (или кластере) баз данных. Это не означает, что множество серверов установлены в многоузловой среде.

Принцип наименьших привилегий

Другой ключевой идеей является принцип наименьших привилегий (PoLP), методологии информационной безопасности, которая утверждает, что пользователям должен предоставляться доступ только к минимальному количеству информации, необходимой им для выполнения своей работы. Любой доступ вне файлов или данных, владельцем которых они являются, должен предоставляться им специально.

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

Например, только владелец объекта базы данных (схемы, таблицы, функции т.д.) может использовать или изменять его, если конкретный доступ к нему не был предоставлен другим ролям. Например, большинство пользователей будут, вероятно, ожидать, что две роли с одинаковыми привилегиями на базу данных или схему (например, на SELECT) должны позволять по умолчанию делать выборку из любой таблицы. Это не так работает в PostgreSQL без дополнительного вмешательства.

Напротив, объекты всегда являются собственностью роли (и только одной рол), которая в большинстве случаев должна установить привилегии для других ролей. Это поведение может быть изменено, чтобы на новые объекты, которые создает роль, автоматически предоставлялись конкретные привилегии другим ролям, но не из коробки, когда владение объектом вляется ключевой концепцией для понимания создания ролей и других объектов в базе данных PostgreSQL.

Мы рассмотрим это более подробно в следующей статье о владении объектом и привилегиях на него.

Суперпользователи

Прежде чем погружаться в создание роли и предоставления привилегий, осталось обсудить концепцию суперпользователя. В PostgreSQL суперпользователь - это роль, которая позволяет выполнять любые действия в системе и является аналогом пользователя root в Linux или аккаунтом sa в SQL Server.

Когда пользователь является суперпользователем, не проверяются никакие разрешения при выполнении кода SQL (DDL/DML) или администрирования кластера. Проверяется только разрешение на вход и подключение к кластеру. Назначение суперпользователя обходит все другие проверки, включая такие вещи, как безопасность на уровне строк (RLS). Такое поведение отличается от некоторых других систем баз данных.

Для правильного функционирования каждый кластер PostgreSQL необходимо должен иметь, по крайней мере, одного суперпользователя для выполнения административных задач. Изначально этими задачами могли быть создание баз данных или дополнительных ролей, но есть несколько задач, как в PostgreSQL 15 (установка некоторых расширений, изменение некоторых параметров сервера), которым все еще требуется суперпользователь.

В PostgreSQL аккаунт этого исходного суперпользователя по умолчанию называется postgres, что обычно совпадает с системным именем пользователя, который запускает кластерный процесс PostgreSQL. Этого исходного суперпользователя можно заменить с помощью команды initdb, чтобы создать свой собственный кластер PostgreSQL. Обсуждение этой темы выходит за рамки данной статьи, и обычно не рекомендуется делать при отсутствии опыта.

И последнее замечание. Если ваша база данных PostgreSQL размещается на таких сервисах, как AWS RDS или Azure Postgres, вы, вероятно, не будете иметь доступ к роли суперпользователя. Вместо этого для вас создается начальная роль с большинством привилегий, необходимых для администрирования пользователей и создания баз данных и объектов. Даже если вы размещаете свой собственный кластер PostgreSQL и имеете доступ к роли суперпользователя, рекомендуется (и считается лучшей практикой) создать, по крайней мере, одну роль, которая имеет разрешения CREATE USER и CREATE DATABASE, но не является суперпользователем. С этими конкретным атрибутами роль может создавать новых пользователей (или другую базу данных), но не будет обходиться другими проверками безопасности, подобными выборке данных из таблиц, на которые она не имеет разрешений. Это позволит вам выполнять почти все административные задачи без возможности обхода всех проверок привилегий при работе в базе данных или кластере.

Роли PostgreSQL

Напомню, что в PostgreSQL как пользователи, так и группы, технически являются ролями. Они всегда создаются на уровне кластера и получают привилегии на базы данных и другие объекты в них. В зависимости от сопровождения базы данных вас может удивить, что роли не создаются как принципалы внутри каждой базы данных. Просто помните, что роли теперь (пользователи и группы) создаются как принципалы кластера, которые (могут) владеть объектами в базе данных, и владение объектом предоставляет дополнительные привилегии, некоторые из которых мы исследуем позже в этой статье.

Для этой статьи все роли примеров пользователей будут создаваться с паролем аутентификации. Доступны и другие методы аутентификации, включая GSSPI, SSPI, Kerberos, Certificate и прочие. Однако установка этих альтернативных методов лежит вне рамок, необходимых нам для обсуждения владения объектами и привилегий.

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

Чтобы создать роль пользователя в PostgreSQL, выполните следующий код DDL под пользователем, который имеет привилегию CREATEROLE. Как обсуждалось выше, это обычно суперпользователь postgres или пользователь-администратор, предоставляемый вашим хостингом.

CREATE ROLE dev1 WITH LOGIN PASSWORD ‘supersecretpw’;

Как альтернативу, PostgreSQL все еще поддерживает старую команду CREATE USER, но это просто псевдоним для CREATE ROLE. Теоретически в какой-то момент она будет объявлена устаревшей, поэтому пользователям следует переходить на CREATE ROLE.

-- Это все еще работает в PostgreSQL 15. Тоже самое, что и выше

-- но неявно добавляет LOGIN

CREATE USER dev1 WITH PASSWORD ‘supersecretpw’;

На самом базовом уровне это все, что требуется для создания роли, которая может логиниться в кластере. Что они могут делать после аутентификации, зависит от привилегий, которые вы им предоставляете (которые мы обсудим дальше в этой статье).

Поддержка уникальных привилегий среди множества пользовательских ролей может быть трудоемкой и подверженной ошибкам. Поэтому желательно создавать роли, которые функционируют как группы, чтобы пользователи могли наследовать при необходимости привилегии группы. Например, рассмотрим следующую диаграмму:



Если роль является членом роли reader, то она имеет привилегии SELECT на объект public.table_name. Если она участвует в роли creator, то имеет также привилегии INSERT, поскольку каждая из этих ролей явно предоставляла привилегии на ресурс. Роль reader_and_creator, которая не имела явно предоставленных привилегий вне роли public, имеет привилегии SELECT и INSERT на public.table_name, поскольку ей предоставлено членство в обоих ролях reader и creator. Разумно создавая роли, вы можете управлять пользовательскими ролями посредством наследования, а не предоставления индивидуальных привилегий каждому пользователю. Это очень важно, поскольку пользовательские роли будут меняться в разных средах (Например, люди, которые могут модифицировать DEV, могут не иметь доступа на вход в кластер PROD).

Создание групповой роли

Чтобы создать групповую роль в PostgreSQL, создайте роль, которая не имеет разрешения на вход. Как говорилось ранее, это просто соглашение, которое представляет роль как группу.

CREATE ROLE devgrp WITH NOLOGIN;

Как и в случае пользовательской роли, PostgreSQL все еще поддерживает старую команду CREATE GROUP, хотя это просто псевдоним для CREATE ROLE, поскольку все роли создаются с NOLOGIN по умолчанию, что, как мы уже обсуждали, означает роль, которая используется как группа. Нет преимуществ в использовании CREATE GROUP, и она может быть в некоторый момент признана устаревшей.

Имеется большое число других атрибутов роли, которые могут быть применены во время создания или посредством ALTER ROLE. Позвольте мне перечислить несколько дополнительных атрибутов роли с их значениями по умолчанию.



Все возможные атрибуты и подробную информацию можно найти в документации. Также любой из этих атрибутов ролей (включая суперпользователя) могут быть изменены в любое время с помощью команды ALTER ROLE.

Теперь, когда у нас есть роли, одна из которых может логиниться (наш пользователь) и одна, которая не может (наша группа), нам нужно погрузиться в тему присвоения и применения привилегий.

Роль PUBLIC

Каждый кластер PostgreSQL имеет еще одну неявную роль, которая называется PUBLIC и которую нельзя удалить. Все остальные роли всегда имеют членство в PUBLIC по умолчанию и наследуют все привилегии, присвоенные этой роли. Если ничего не менялось, привилегиями, предоставленными роли PUBLIC, являются следующие.



Здесь важно заметить, что роль PUBLIC всегда имеет привилегию CONNECT, предоставляемую по умолчанию и служащую для удобства разрешения всем ролям подключаться к вновь созданной базе данных. Без привилегии подключения к базе данных никакая из создаваемых вами ролей не могла бы что-либо сделать.

Подключения к кластеру баз данных PostgreSQL всегда открыты для конкретной базы данных, а не для кластера. Помните, что мы начали эту статью, рассуждая об аутентификации и авторизации. Чтобы открыть соединение с кластером PostgreSQL, пользователю нужно сначала пройти аутентификацию (предоставив учетные данные для роли WITH LOGIN), а затем авторизацию для подключения к базе данных. Поскольку каждой роли предоставлено участие в роли PUBLIC, и эта роль имеет привилегию CONNECT по умолчанию, все роли, которые могут пройти аутентификацию, имеют также разрешение на CONNECT.

Многие из этих значений по умолчанию можно изменить. В качестве администратора базы данных вы можете запретить (REVOKE) возможность подключаться (CONNECT) для роли PUBLIC, а затем предоставить ее каждой роли отдельно, но сложность такого обслуживания редко оправдывает затраченные усилия.

Для ясности, эта привилегия позволяет пользователю только подключаться и видеть объекты верхнего уровня в схеме public (например, таблицы). Однако они не могут выбирать или изменять что-либо в таблицах без дальнейших разрешений. Давайте немного поговорим об этом.

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

Обратите внимание, что привилегии по умолчанию несколько изменились в PostgreSQL 15 и выше. Ранее роли PUBLIC было также позволено создавать объекты (CREATE) в схеме public базы данных, а каждая база данных имеет схему public по умолчанию. Это разрешение было предоставлено роли public много лет назад для обратной совместимости с очень старым версиями PostgreSQL, но также представляет уязвимость с точки зрения безопасности. С появлением служб DBaaS потенциальная возможность для новой роли создавать таблицы, триггеры и функции в схеме public означает, что она сможет потенциально выполнять код, который мог бы позже привести к повышению ее привилегий (если не хуже).

Поэтому, начиная с PostgreSQL 15, роль public больше не может ничего создавать по умолчанию, вне зависимости от схемы. Хотя это несколько усложняет работу администраторов, исключение привилегии CREATE было рекомендовано как лучшая практика на долгое время.

Тестирование новой роли

С помощью созданной нами новой роли пользователя, dev1, мы можем залогиниться на кластере PostgreSQL, используя инструмент типа psql и нижеследующую команду. В последующих примерах кластер PostgreSQL размещен локально на моем ноутбуке, используя Docker с портом по умолчанию 5432. Детали вашего подключения, включая имя базы данных, может отличаться и должны быть скорректированы при необходимости.

-- Используем флаги для получения приглашения ко вводу пароля,

-- не существует специального флага “password”

psql -h localhost -U dev1 -d postgres

 -- Альтернативный способ, использующий PostgreSQL URI

psql postgres://dev1:supersecretpw@localhost:5432/postgres

Это вызовет появление приглашения к вводу пароля (если вы не передал его посредством URI) и подключит пользователя к базе данных postgres. Это работает, поскольку роль dev1 автоматически является членом роли PUBLIC, которая в свою очередь имеет разрешение на подключение к базе данных postgres.

С аутентификацей по паролю и одним оператором DDL мы создали роль и залогинились на экземпляре PostgreSQL. Не очень сложно, правда?

Итак, теперь давайте приступим к разработке новой функции в качестве недавно созданной учетной записи dev1!

Создание объектов с помощью нового пользователя

В качестве пользователя-разработчика я должен сначала создать таблицу, необходимую для поддержки новой функциональности, над которой я работаю. Таблица требуется для хранения социальных аккаунтов каждого пользователя. Я не знаю, сколько учетных данных для социальных аккаунтов мы планируем поддерживать, поэтому я просто начинаю с базовой таблицы, которая хранит каждую учетную запись в отдельном столбце.

В зависимости от версии PostgreSQL, к которой мы подключены, это может быть легко или не очень. Помним, что PostgreSQL 14 и ниже позволяет роли PUBLIC (членом которой является `dev1`) создавать объекты в схеме public по умолчанию. Но PostgreSQL 15 и выше не позволяют это делать.

После входа под `dev1` мы пытаемся создать следующую таблицу.

CREATE TABLE user_social (

   user_id INT NOT NULL,

   twitter_handle TEXT NULL,

   facebook_handle TEXT NULL );

В PostgreSQL 14 и ниже это, вероятно, пройдет на большинстве установок с учетом настроек, которые мы делали до сих пор. Однако в PostgreSQL 15 и выше наш пользователь dev1 наиболее вероятно получит следующую ошибку:

ERROR: permission denied for schema public
LINE 1: CREATE TABLE user_social
(отсутствует разрешение на схему public)

Позвольте мне повторить, что это работает только в PostgreSQL 14 и ниже, поскольку роли PUBLIC предоставлена возможность создавать объекты (CREATE) в схеме public по умолчанию. Мы получили бы подобную ошибку, если попытались сделать что-то подобное созданию новой схемы в базе данных, поскольку PUBLIC не имеет привилегии CREATE для всей базы данных, а только для схемы public.

Для справки, привилегия CREATE может применяться к трем областям в PostgreSQL (базе данных, схеме и табличным пространствам) и позволяет создавать разные объекты в зависимости от того, где вы имеете разрешение на CREATE, как описано на странице привилегий в документации.

Можно ли нам создать таблицу, или новую схему, как убедиться, что новые роли разработчика позволят сделать это?

Мы должны предоставить им привилегии (GRANT).

Предоставление привилегий роли

PostgreSQL имеет исчерпывающий набор встроенных привилегий, которые все описаны в документации. В последующих примерах мы сосредоточимся на предоставлении привилегии CREATE пользователю dev1 в PostgreSQL 15.

-- В качестве суперпользователя или роли, которая может

-- предоставить эту привилегию другим

GRANT CREATE ON SCHEMA public TO dev1;

Теперь, когда мы предоставили dev1 разрешение на CREATE, мы можем снова попытаться создать нашу таблицу в PostgreSQL 15. Мы можем сохранять множество сессий открытыми (либо в psql, либо в вашей IDE), но еще одним вариантом является использование SET ROLE. Вы можете "переключиться" на другую роль, если вы заходили как суперпользователь, или являетесь членом этой роли. Это полезно в таких случаях, как настройка правильного владения объектом во время создания или для тестирования разрешений подобно тому, что мы делаем здесь сейчас.

-- Временно переключаем роль в текущей сессии на другую роль.

--Только суперпользователи или члены этой роли могут это сделать.

SET ROLE dev1;

-- Создаем таблицу под dev1, которой даны новые разрешения

CREATE TABLE user_social (

   user_id INT NOT NULL,

   twitter_handle TEXT NULL,

   facebook_handle TEXT NULL );

 -- Возвращаемся к исходной роли сессии

SET ROLE NONE;

Успешно! Мы на шаг ближе к добавлению новой функциональности в наше приложение.

К сожалению, мы сразу получили другую проблему. Для разработки этой функции нашему разработчику необходимо выбирать данные из таблицы `user`, но при попытке сделать это получаем другую ошибку:

-- снова устанавливаем нашу роль,

-- если подключались из другой пользовательской сессии

SET ROLE dev1;

-- выполняем запрос select к другой таблице

SELECT * FROM "user" u

INNER JOIN user_social usoc USING(user_id);

В результате имеем:

ERROR: permission denied for table user
(Доступ запрещен к таблице user)

Как вы могли догадаться, решением является предоставление другой привилегии роли dev1. В качестве суперпользователя или роли, которая имеет возможность предоставлять эти привилегии:

-- В этом примере предоставляется привилегия SELECT на ВСЕ таблицы в схеме.

-- При необходимости мы могли быть более избирательными

GRANT SELECT ON ALL TABLES IN SCHEMA public TO dev1;

Если мы знаем все привилегии, которые необходимы нашим разработчикам для выполнения их работы, мы можем добавить несколько привилегий одновременно. Например, если разработчику необходимо SELECT, INSERT, UPDATE и DELETE данные в таблицах схемы public, мы могли бы их предоставить в одном операторе.

GRANT SELECT, INSERT, UPDATE, DELETE 

     ON ALL TABLES IN SCHEMA public TO dev1;

Хотя, если быть честными, управление привилегиями по одной для каждой роли из базы данных в базу данных станет головной болью при обслуживании. Как только мы создаем аккаунт для следующего разработчика в нашей команде, dev2, мы должны начать весь этот процесс заново.

Конечно, есть лучший способ для этого.

Предоставление привилегий с помощью групп

PostgreSQL предоставляет возможность давать привилегии одной группы - другой. В частности, групповые роли (роли без входа) являются идеальным механизмом для применения набора привилегий многим пользователям сразу.

Ранее мы создали групповую роль с именем devgrp. Ей не позволено выполнять вход, и мы еще не предоставили ей никаких привилегий. Но мы могли бы предоставить этой роли привилегии, которые хотели бы давать всем разработчикам, а затем предоставлять роль devgrp каждому из них. Затем при всякой необходимости ослабить привилегии разработчика нам достаточно только изменить (GRANT или REVOKE) привилегии в роли devgrp.

-- В качестве суперпользователя или роли которая

-- имеет возможность предоставлять эту привилегию другим

GRANT CREATE ON SCHEMA public TO devgrp;

GRANT SELECT, INSERT, UPDATE, DELETE 

            ON ALL TABLES IN SCHEMA public TO devgrp;

И теперь любая роль, которой мы предоставляем членство, будет наследовать эти разрешения по умолчанию.

GRANT devgrp TO dev1;

Теперь dev1 наследует все привилегии, которым обладает devgrp. Всякий раз, когда новый разработчик присоединяется к команде и которому требуется доступ к базе данных, мы можем создать для него роль и предоставить членство в роли devgrp. Это можно сделать в любое время с помощью оператора GRANT, приведенного выше, или при создании роли, если групповая роль уже существует.

-- Создаем роль и автоматически добавляем ее

-- как члена роли devgrp

CREATE ROLE dev2 WITH LOGIN PASSWORD ‘supersecretpw2’ 

                                            IN ROLE devgrp;

Заключение

PostgreSQL имеет развитую систему ролей и привилегий, которая позволяет гибко управлять доступом к объектам сервера и ресурсам. В этой статье мы познакомились с ролями, как использовать их для эффективного управления пользователями и группами и как предоставлять некоторые исходные привилегии новым ролям, которые мы создаем.

Мы также обсудили важность ролей суперпользователя и то, что большинство служб DBaaS не обеспечит вас суперпользователем, поскольку для него обходятся все проверки привилегий. Вместо этого лучшей практикой считается создание одного или более пользователей-администраторов с привилегиям CREATEROLE и CREATEDB для ежедневного администрирования. Если вы используете службу, подобную AWS или Azure, пользователь-администратор, которого они предоставляют, вероятно, будет иметь эти привилегии, а не являться полным суперпользователем.

После создания новых ролей мы вкратце обсудили присвоение (GRANT) привилегий новым ролям, как индивидуально, так и посредством наследования, с тем, чтобы наши новые роли могли выполнять необходимую работу для разработки новой функциональности приложения.

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