Как посмотреть роли имеющиеся в кластере postgresql

Роли уровня базы данных

Чтобы легко управлять разрешениями в базах данных, SQL Server предоставляет несколько ролей, которые являются субъектами безопасности, которые группируют другие субъекты. Они похожи на группы в операционной системе Microsoft Windows. Разрешения ролей уровня базы данных распространяются на всю базу данных.

Чтобы добавлять и удалять пользователей в роли базы данных, используйте параметры ADD MEMBER и DROP MEMBER инструкции ALTER ROLE . Система платформы аналитики (PDW) и Azure Synapse Analytics не поддерживают использование ALTER ROLE . Используйте вместо этого более старые процедуры sp_addrolemember и sp_droprolemember .

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

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

Вы можете добавить любую учетную запись базы данных и другие роли SQL Server в роли уровня базы данных.

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

Разрешения определенных пользователями ролей базы данных можно настроить с помощью инструкций GRANT, DENY и REVOKE. Дополнительные сведения см. в разделе Разрешения (компонент Database Engine).

Список всех разрешений см. в афише с разрешениями для ядра СУБД . Разрешения на уровне сервера не могут быть предоставлены ролям базы данных. Имена входа и другие субъекты уровня сервера (например, роли сервера) нельзя добавлять в роли базы данных. Для обеспечения безопасности на уровне сервера в SQL Server используйте роли сервера . Разрешения на уровне сервера нельзя предоставлять с помощью ролей в База данных SQL Azure и Azure Synapse Analytics.

Фиксированные роли базы данных

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

Имя предопределенной роли базы данных	Description
db_owner	Члены предопределенных ролей базы данных db_owner могут выполнять все действия по настройке и обслуживанию базы данных в базе данных, а также drop базу данных в SQL Server. (В База данных SQL и Azure Synapse некоторые действия обслуживания требуют разрешений на уровне сервера и не могут выполняться db_owners .)
db_securityadmin	Элементы предопределенной роли базы данных db_securityadmin могут изменять членство в роли (только для настраиваемых ролей) и управлять разрешениями. Элементы этой роли потенциально могут повышать свои права доступа, поэтому необходимо отслеживать их действия.
db_accessadmin	Члены предопределенных ролей базы данных db_accessadmin могут добавлять или удалять доступ к базе данных для имен входа Windows, групп Windows и имен входа SQL Server.
db_backupoperator	Члены предопределенной роли базы данных db_backupoperator могут создавать резервные копии базы данных.
db_ddladmin	Члены предопределенной роли базы данных db_ddladmin могут выполнять любые команды языка определения данных (DDL) в базе данных. Члены этой роли могут потенциально повысить свои привилегии, управляя кодом, который может выполняться под высокими привилегиями, и их действия должны отслеживаться.
db_datawriter	Члены предопределенной роли базы данных db_datawriter могут добавлять, удалять или изменять данные во всех пользовательских таблицах. В большинстве случаев эта роль будет сочетаться с db_datareader членством, чтобы разрешить чтение измененных данных.
db_datareader	Члены предопределенной роли базы данных db_datareader могут считывать все данные из всех пользовательских таблиц и представлений. Пользовательские объекты могут существовать в любой схеме, кроме sys и INFORMATION_SCHEMA.
db_denydatawriter	Члены предопределенных ролей базы данных db_denydatawriter не могут добавлять, изменять или удалять данные в пользовательских таблицах в базе данных.
db_denydatareader	Члены предопределенных ролей базы данных db_denydatareader не могут считывать данные из пользовательских таблиц и представлений в базе данных.

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

fixed_database_role_permissions

Специальные роли для База данных SQL и Azure Synapse

Эти роли базы данных существуют только в виртуальной master базе данных. Их разрешения ограничены действиями, выполняемыми в master . К этим ролям могут добавляться только пользователи master базы данных. Имена входа не могут быть добавлены в эти роли, но пользователи могут создаваться на основе имен входа, а затем эти пользователи могут быть добавлены в роли. Пользователи автономной базы данных master также могут быть добавлены в эти роли. Однако пользователи автономной базы данных, добавленные в роль dbmanager , master не могут использоваться для создания новых баз данных.

Название роли	Description
dbmanager	Может создавать и удалять базы данных. Член роли dbmanager, который создает базу данных, становится ее владельцем, что позволяет такому члену подключиться к этой базе данных в качестве пользователя dbo. Пользователь dbo имеет все разрешения в этой базе данных. Члены роли dbmanager необязательно имеют разрешения на доступ к базам данных, которые им не принадлежат.
db_exporter	Применяется только к выделенным пулам SQL Azure Synapse Analytics (ранее SQL DW). Члены предопределенной роли базы данных db_exporter могут выполнять все действия для экспорта данных. Разрешения, предоставляемые для этой роли: CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL DATA SOURCE, ALTER ANY EXTERNAL FILE FORMAT.
loginmanager	Может создавать и удалять имена входа в виртуальной master базе данных.

Субъект уровня сервера и администратор Microsoft Entra (если настроен) имеют все разрешения в База данных SQL и Azure Synapse Analytics без необходимости быть членами каких-либо ролей. Дополнительные сведения см. в разделе SQL Database Authentication and Authorization: Granting Access(Проверка подлинности и авторизация базы данных SQL: предоставление доступа).

Некоторые роли баз данных не применимы к Azure SQL или Azure Synapse:

db_backupoperator неприменимо в База данных SQL Azure (не Управляемый экземпляр SQL Azure) и бессерверном пуле Azure Synapse Analytics, так как команды резервного копирования и восстановления T-SQL недоступны.
db_datawriter и db_denydatawriter не применимы к бессерверным службам Azure Synapse Analytics, так как он просто считывает внешние данные.

Роли базы данных msdb

База данных msdb содержит специальные роли, показанные в следующей таблице.

db_ssisoperator

dc_operator

Члены роли db_ssisadmin и роли dc_admin могут повышать свои права доступа до sysadmin. Это повышение привилегий может произойти, так как эти роли могут изменять пакеты служб Integration Services и пакеты служб Integration Services можно выполнять SQL Server с помощью контекста безопасности sysadmin агент SQL Server. Чтобы защититься от этого повышения привилегий при выполнении планов обслуживания, наборов сбор данных и других пакетов служб Integration Services, настройте агент SQL Server задания, которые запускают пакеты, чтобы использовать учетную запись прокси с ограниченными привилегиями или добавлять только членов sysadmin в роли db_ssisadmin и dc_admin.

Работа с ролями уровня базы данных

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

Компонент	Тип	Описание
sp_helpdbfixedrole (Transact-SQL)	Метаданные	Возвращает список всех предопределенных ролей базы данных.
sp_dbfixedrolepermission (Transact-SQL)	Метаданные	Отображает разрешения предопределенной роли базы данных.
sp_helprole (Transact-SQL)	Метаданные	Возвращает информацию о ролях, относящихся к текущей базе данных.
sp_helprolemember (Transact-SQL)	Метаданные	Возвращает сведения о членах роли в текущей базе данных.
sys.database_role_members (Transact-SQL)	Метаданные	Возвращает одну строку для каждого члена каждой роли базы данных.
IS_MEМБ ER (Transact-SQL)	Метаданные	Указывает, является ли текущий пользователь членом указанной группы Microsoft Windows или роли базы данных Microsoft SQL Server.
CREATE ROLE (Transact-SQL)	Команда	Создает новую роль базы данных в текущей базе данных.
ALTER ROLE (Transact-SQL)	Команда	Изменяет имя или членство роли базы данных.
DROP ROLE (Transact-SQL)	Команда	Удаляет роль из базы данных.
sp_addrole (Transact-SQL)	Команда	Создает новую роль базы данных в текущей базе данных.
sp_droprole (Transact-SQL)	Команда	Удаляет роль базы данных из текущей базы данных.
Хранимая процедура Хранимая процедура sp_addrolemember (Transact-SQL)	Команда	Добавляет пользователя базы данных, роль базы данных, имя входа Windows или группу Windows к роли текущей базы данных. Вместо этого следует использовать ALTER ROLE все платформы, кроме платформы Analytics Platform System (PDW) и Azure Synapse.
sp_droprolemember (Transact-SQL)	Команда	Удаляет учетную запись безопасности из роли SQL Server в текущей базе данных. Вместо этого следует использовать ALTER ROLE все платформы, кроме платформы Analytics Platform System (PDW) и Azure Synapse.
GRANT	Разрешения	Добавляет разрешение для роли.
DENY	Разрешения	Запрещает разрешение для роли.
REVOKE	Разрешения	Удаляет разрешения, выданные или запрещенные ранее.

Роль базы данных public

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

Примеры

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

А. Добавление пользователя к роли уровня базы данных

В следующем примере пользователь Бен добавляется к фиксированной роли уровня базы данных db_datareader .

ALTER ROLE db_datareader ADD MEMBER Ben; GO

B. Вывод всех субъектов базы данных, которые являются членами роли уровня базы данных

Следующая инструкция возвращает все члены любой роли базы данных.

SELECT roles.principal_id AS RolePrincipalID , roles.name AS RolePrincipalName , database_role_members.member_principal_id AS MemberPrincipalID , members.name AS MemberPrincipalName FROM sys.database_role_members AS database_role_members JOIN sys.database_principals AS roles ON database_role_members.role_principal_id = roles.principal_id JOIN sys.database_principals AS members ON database_role_members.member_principal_id = members.principal_id; GO

Связанный контент

Представления каталога безопасности (Transact-SQL)
Хранимые процедуры безопасности (Transact-SQL)
Функция безопасности (Transact-SQL)
Обеспечение безопасности SQL Server
sp_helprotect (Transact-SQL)
Роли сервера в Базе данных SQL Azure

SQL-Ex blog

Привилегии и роли в SQL Server, Oracle и PostgreSQL. Часть 1

Добавил Sergey Moiseenko on Среда, 19 июля. 2023

Предоставление привилегий на объект базы данных является наиболее распространенным видом деятельности, которую выполняют администраторы баз данных, стараясь при этом дать разрешения, достаточные для выполнения работы, но не слишком большой доступ. Помните о принципе наименьших привилегий! Иногда это сложно реализовать, и, как мы видели на многих других примерах, имеются некоторые различия в реализации этой функциональности в трех рассматриваемых РСУБД, плюс появились некоторые новые роли в SQL Server 2022. В настоящей статье мы рассмотрим то, как предоставить разрешения в SQL Server, Oracle и PostgreSQL.

В этом руководстве мы дадим обзор различных способов предоставления привилегий в SQL Server, Oracle и PostgreSQL. Будут также представлены различия в концепциях ролей, схем и владельцев, а также способ предоставления разрешений на всю схему. Работая над этой статьей, я осознал, что она становится все больше и больше, поэтому я разбил ее на 2 части. В этой первой части мы сосредоточим внимание на концепции ролей.

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

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

Принципалы — это логины SQL или Active Directory, которые используются для доступа к SQL Server, а также соответствующие пользователи базы данных и роли:
- Принципалы уровня сервера.
- Принципалы уровня базы данных.
- Роли сервера.
- Роли базы данных.
- Роли приложения.
Давайте создадим роль базы данных:
```
CREATE ROLE [Finance]
```
Предоставим привилегию SELECT на эти две таблицы (Invoice и InvoiceLine) этой роли:
```
GRANT SELECT ON [dbo].[Invoice] TO [Finance] 
GRANT SELECT ON [dbo].[InvoiceLine] TO [Finance]
```
Теперь для предоставления финансисту привилегии на чтение этих таблиц нам необходимо назначить пользователю роль Finance. Давайте создадим логин и пользователя с назначенной ролью:
```
USE [master] 
GO 
CREATE LOGIN [l.callahan] WITH PASSWORD=N'GoldenHorde', DEFAULT_DATABASE=[Chinook], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF 
GO 
USE [Chinook] 
GO 
CREATE USER [l.callahan] FOR LOGIN [l.callahan] 
GO 
ALTER ROLE [Finance] ADD MEMBER [l.callahan] 
GO
```
Теперь давайте попробуем подключиться, используя логин SQL, и проверим, что этот пользователь может делать:

Запрос ниже выбирает 30 первых строк из таблицы Invoice:
```
select top 30 * from Invoice;
```
Но что, если мы попытаемся обратиться к другой таблице, например, Artist:
```
select top 30 * from artist;
```
Поскольку пользователь не имеет привилегий, связанных с этой таблицей, а только на Invoice и InvoiceLine, то ему запрещен доступ. Вспомните принцип минимальных привилегий!

Мы только что создали сами пример роли базы данных. Имеется также несколько фиксированных ролей базы данных, которые встроены в SQL Server и которые могут помочь управлять некоторыми разрешениями. Например, каждому пользователю базы данных приписана роль public в базе данных. В официальной документации проводится список таких ролей с их описанием и разрешениями; при этом разрешения, приписанные фиксированным ролям базы данных, не могут быть изменены: роли уровня базы данных.

Одной из фиксированных ролей базы данных, которую я часто использую, является db_datareader. Как говорит ее название, любой принципал, обладающий этой ролью, может делать выборку из любой таблицы в базе данных. Например, нам нужно позволить пользователям, которые создают отчеты, читать из любой таблицы. Итак, мы создаем новый логин и пользователя базы данных с именем Report и припишем ему роль db_datareader:
```
USE [master] 
GO 
CREATE LOGIN [Report] WITH PASSWORD=N'pippo120', DEFAULT_DATABASE=[Chinook], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF 
GO 
USE [Chinook] 
GO 
CREATE USER [Report] FOR LOGIN [Report] 
GO 
ALTER ROLE [db_datareader] ADD MEMBER [Report] 
GO
```
Теперь мы можем получить доступ к базе данных Chinook под пользователем Report:

Сделаем выборку из пары различных таблиц:
```
select top 30 * from Artist;
```
```
select * from Genre;
```
Как видим, этот пользователь теперь может читать из любой таблицы в базе данных. Однако если мы попытаемся выполнить запись:
```
update Genre 
set name='Punk' 
where GenreId = 4
```
то получим сообщение об отсутствии разрешений.

Серверные роли в SQL Server 2022

До сих пор мы говорили о ролях уровня базы данных. Но, как было упомянуто ранее, имеются также роли уровня сервера. Они могут быть созданы ad-hoc как пользовательские серверные роли (начиная с SQL Server 2012), а также имеется несколько фиксированных ролей.

Имеется несколько новинок, введенных в SQL Server 2022, включая 10 дополнительных фиксированных серверных ролей, которые отличаются префиксом ##MS_ и суффиксом ##. (Официальная документация).

Давайте обсудим несколько примеров. Наиболее мощной фиксированной серверной ролью является роль sysadmin. Получив эту роль, мы получим разрешения ко всему на экземпляре SQL Server, почти как SA.

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

Замечание. Создавая новый логин в SSMS, новые фиксированные роли появляются, когда мы выбираем страницу Server Roles (роли сервера).

Как и прежде, мы можем использовать T-SQL:
```
USE [master] 
GO 
CREATE LOGIN [Management] WITH PASSWORD=N'Riesenhotter44', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF 
GO 
ALTER SERVER ROLE [sysadmin] ADD MEMBER [Management] 
GO
```
Теперь мы можем протестировать новый логин:

Давайте попробуем перевести базу данных в автономный режим на сервере:
```
USE [master] 
GO 
ALTER DATABASE [WideWorldImporters] SET OFFLINE 
GO
```
Для проверки, что это невозможно для другого пользователя, мы можем попытаться вернуть базу данных онлайн посредством логина Report:
```
USE [master] 
GO 
ALTER DATABASE [WideWorldImporters] SET ONLINE 
GO
```
Теперь давайте вернем ее в режим онлайн с помощью пользователя Management
```
USE [master] 
GO 
ALTER DATABASE [WideWorldImporters] SET ONLINE 
GO
```
Опять таки, новые фиксированные серверные роли идентифицируются префиксом ##MS_ и суффиксом ##. Полный список этих ролей можно найти в официальной документации.

Давайте попробуем другой пример, использующий ##MS_DatabaseConnector##. На практике члены этой фиксированной роли могут подключаться к любой базе данных на экземпляре SQL Server без необходимости иметь пользовательский аккаунт в базе данных.

Предположим, что мы имеем руководителя, которому необходимо подключаться ко всем базам данных на экземпляре без наличия сопоставленного пользователя или других мощных привилегий/ролей:
```
USE [master] 
GO 
CREATE LOGIN [Supervisor] WITH PASSWORD=N'PinoLavatrice', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF 
GO 
ALTER SERVER ROLE [##MS_DatabaseConnector##] ADD MEMBER [Supervisor] 
GO
```
Как можно увидеть, мы не имеем никакой сопоставляемой базы данных для этого логина. Давайте попробуем подключиться. Обычно мы не смогли бы развернуть любую из баз данных в Management Studio:

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

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

В Oracle нам приходится меть дело с несколько отличными принципалами и ролями. Даже если базовый оператор GRANT один и тот же во всех трех диалектах (с некоторыми предостережениями, которые мы обсудим во второй части), мы увидим, что принципалы различаются в каждой из трех РСУБД.

О разной терминологии и основных отличиях в трех РСУБД можно ознакомиться здесь.

Концепция схемы/пользователи в Oracle отличается от SQL Server. Но тут тоже имеются роли. Роли могут быть созданы непосредственно (ad hoc), как и в SQL Server, но также имеется несколько фиксированных ролей. Кроме этого, мы имеем системные привилегии, которые могут быть предоставлены непосредственно пользователю или роли ad-hoc. Эти роли могут быть присвоены пользователям/схемам, как и в SQL Server для логинов пользователей. Давайте рассмотрим аналогичный пример создания роли Finance и присвоении ее конкретному пользователю:
```
create role finance;
```
Мы создали роль как и в SQL Server. Теперь назначим этой роли привилегию select на таблицы счетов:
```
grant select on chinook.invoice to finance; 
grant select on chinook.invoiceline to finance;
```
Создадим конкретного пользователя и предоставим ему роль finance:
```
CREATE USER "l.callahan" IDENTIFIED BY "GoldenHorde" 
DEFAULT TABLESPACE "USERS" 
TEMPORARY TABLESPACE "TEMP"; 
GRANT "CONNECT" TO "l.callahan" ; 
GRANT "FINANCE" TO "l.callahan" ; 
ALTER USER "l.callahan" DEFAULT ROLE "CONNECT","FINANCE";
```
Здесь сразу бросаются в глаза некоторые отличия в создании пользователя. Во-первых, нам необходимо назначить пользователю временное табличное пространство и табличное пространство по умолчанию. Табличное пространство по умолчанию будет использоваться для создания новых объектов, в то время как пространство temp — для временных объектов типа CTE. Табличное пространство является базовой структурой хранения в Oracle, подобно файлам базы данных в SQL Server.

Затем мы назначаем роль пользователю с помощью простого оператора GRANT. Отличие от SQL Server состоит в том, что мы используем ALTER, чтобы добавить роль пользователю.

Наконец, этого недостаточно, чтобы назначить роль; мы также должны установить ее по умолчанию, или она не будет использоваться. Другое замечание состоит в том, что нам необходимо предоставить пользователю роль CONNECT, иначе он не сможет войти (и, естественно, эта роль также требует установки по умолчанию). Эта концепция может быть полезна, чтобы предоставить пользователю возможность выполнять определенные задачи, которые требуют высоких привилегий, но не постоянно. Поэтому мы просто назначаем пользователю роль с более высокими разрешениями без определения их как DEFAULT, а при необходимости пользователь сам может выполнить в сеансе оператор SET ROLE, и назначить ему роль; а когда сеанс закрывается, он теряет роль или, в качестве альтернативы, пользователь сам может отключить ее с помощью SET ROLE NONE;

Давайте попытаемся выполнить подключение с этим новым пользователем и протестировать его. Используя SQL Developer, мы создаем новое подключение:

Обратите внимание на имя пользователя: поскольку оно содержит «.», мы должны использовать двойные кавычки, как и ранее при создании пользователя; в противном случае мы получим ошбку о неверном имени пользователя/пароле!

Подключившись, мы можем попроболвать выполнить те же самые запросы, которые использовались в SQL Server:
```
select * 
from chinook.Invoice 
fetch FIRST 30 rows only;
```
Если не считать различия в диалекте Oracle PL/SQL, который не содержит предложения TOP, а использует конструкцию FETCH FIRST n ROWS ONLY, мы имеем те же самые результаты. Теперь давайте проверим таблицу, для которой пользователь не авторизован:
```
select * 
from chinook.artist 
fetch FIRST 30 rows only;
```
Обратите внимание на отличающиеся типы ошибок в Oracle. Она не дает информации о таблице artist. Говорится, что таблица не существует!

С сожалению, в Oracle нет такой роли, как db_datareader в SQL Server, но имеется системная привилегия для этого. Давайте сделаем тот же пример и создадим пользователя Report, которому необходимо читать данные из каждой схемы:
```
CREATE USER "report" IDENTIFIED BY "pippo120" 
DEFAULT TABLESPACE "USERS" 
TEMPORARY TABLESPACE "TEMP"; 
 
GRANT "CONNECT" TO "report" ; 
ALTER USER "report" DEFAULT ROLE "CONNECT"; 
-- Системные привлегии 
GRANT SELECT ANY TABLE TO "report" ;
```
Мы предоставили пользователю Report системную привилегию на выборку данных (select) из любой таблицы базы данных. Давайте попробуем:
```
select * 
from chinook.artist 
fetch first 30 rows only;
```
И из другой случайно выбранной таблицы:
```
select * 
from chinook.genre 
fetch first 30 rows only;
```
Теперь давайте попробуем выполнить запись в ту же самую таблицу:
```
update chinook.Genre 
set name='Punk' 
where GenreId=4;
```
Как ожидалось, мы получил ошибку недостаточных привлегий.

Говоря о системных привилегиях в Oracle, упомянем роль DBA (администратора баз данных), которой мы можем сопоставить подобную роль sysadmin в SQL Server. Давайте рассмотрим ее и другие роли, используя создание пользователя в графическом интерфейсе SQL Developer, и создадим такого же пользователя Management, как и прежде:

Перейдем теперь на вкладку Granted Roles, чтобы увидеть список всех доступных ролей:

Мы просто используем роли CONNECT и DBA. Мы вернемся к списку ролей и системных представлений во второй части этой статьи.

Теперь перейдем на вкладку SQL, чтобы получить скрипт SQL:
```
CREATE USER "Management" IDENTIFIED BY "Riesenhotter44" 
DEFAULT TABLESPACE "USERS" 
TEMPORARY TABLESPACE "TEMP"; 
-- Часть 
-- Роли 
GRANT "DBA" TO "Management" ; 
GRANT "CONNECT" TO "Management" ; 
ALTER USER "Management" DEFAULT ROLE "DBA","CONNECT";
```
И после создания этого пользователя давайте протестируем его:

Давайте попробуем заблокировать пользователя l.callahan. Вспомните, что в Oracle есть только одна база данных на экземпляр, если это не в среде CDB/PDB.
```
ALTER USER "l.callahan" ACCOUNT LOCK ;
```
Без проблем. Но если мы теперь попытаемся разблокировать пользователя с помощью пользователя Report:

Мы не сможем этого сделать, т.к. у нас нет достаточно привилегий для этого.

Теперь мы можем разблокировать его, используя пользователя Management:

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

В PostgreSQL имеется подобная Oracle концепция пользователей/схем, но они называются роли. Роли являются принципалами в PostgreSQL, как говорится в документации: концепция ролей включает понятия «пользователей» и «групп». В версиях PostgreSQL до 8.1 пользователи и группы были разным видами сущностей, но теперь это только роли. Любая роль может действовать как пользователь, группа или и то, и другое.

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

Давайте сделаем несколько примеров, как и ранее. Мы можем создать роль Finance с разрешением на выборку из таблиц Invoice и InvoiceLine и назначить ее пользователю.
```
CREATE ROLE "Finance" WITH 
NOLOGIN 
NOSUPERUSER 
NOCREATEDB 
NOCREATEROLE 
INHERIT 
NOREPLICATION 
CONNECTION LIMIT -1; 
grant select on "Invoice" to "Finance"; 
grant select on "InvoiceLine" to "Finance"; 
 
CREATE ROLE "l.callahan" WITH 
LOGIN 
NOSUPERUSER 
NOCREATEDB 
NOCREATEROLE 
INHERIT 
NOREPLICATION 
CONNECTION LIMIT -1 
PASSWORD 'Goldenhorde'; 
GRANT "Finance" TO "l.callahan";
```
Вы видите, что командой всегда является CREATE ROLE, но в случае логина/пользователя мы указывали опцию LOGIN и пароль. В то время как для роли Finance мы использовали опцию NOLOGIN без пароля.

Теперь подключимся к базе данных с этим новым пользователем и выполним обычные запросы:
```
select * 
from "Invoice" 
fetch first 30 rows only;
```
Обратите внимание, что PostgreSQL использует как стандартный SQL для возвращения n строк с помощью FETCH FIRST N ROWS ONLY, так и собственную нотацию LIMIT N. Теперь мы можем попытаться обратиться к другой таблице:
```
select * 
from "Artist" 
fetch first 30 rows only;
```
Также в PostgreSQL имеются предопределенные роли, как в SQL Server и Oracle. Вот полный список, приведенный в документации. В PostgreSQL имеется роль pg_read_all_data, которая почти эквивалентна роли db_datareader в SQL Server.

Теперь мы можем попробовать предоставить эту роль пользователю Report, как мы это делали в SQL Server:
```
CREATE ROLE "Report" WITH 
LOGIN 
NOSUPERUSER 
NOCREATEDB 
NOCREATEROLE 
INHERIT 
NOREPLICATION 
CONNECTION LIMIT -1 
PASSWORD 'pippo120'; 
 
GRANT pg_read_all_data TO "Report";
```
Теперь мы можем проверить запрос:
```
select * 
from "Artist" 
fetch first 30 rows only;
```
Попробуем другую таблицу:
```
select * 
from "Genre" 
fetch first 30 rows only;
```
Но если мы попробуем выполнить запись:
```
update "Genre" 
set "Name"='Punk' 
where "GenreId"=4;
```
Как и ожидалось, мы не имеем привилегии на запись в любую таблицу базы данных.

Как утверждалось ранее, роль pg_read_all_data почти идентична роли уровня базы данных db_datareader в SQL Server. Это потому что роли в PostgreSQL больше на уровне сервера (кластера), подобно серверным ролям, а не на уровне базы данных.

Есть также способ определить полномочного пользователя, подобно роли sysadmin в SQL Server или роли DBA в Oracle, но тут это не делается с помощью специальной роли. Во время создания скриптов для ролей мы использовали NOSUPERUSER. Но если поменять этот параметр на SUPERUSER, то мы получим эквивалент пользователя sysadmin в SQL Server. Давайте использовать те же примеры, что для двух других РСУБД. Сначала мы создаем суперпользователя Management:
```
CREATE ROLE "Management" WITH 
LOGIN 
SUPERUSER 
CREATEDB 
CREATEROLE 
INHERIT 
NOREPLICATION 
CONNECTION LIMIT -1 
PASSWORD 'Riesenhotter44';
```
Теперь мы можем подключиться и протестировать этого нового пользователя, создав новую базу данных:
```
CREATE DATABASE "Apache" 
WITH 
OWNER = "Management" 
ENCODING = 'UTF8' 
CONNECTION LIMIT = -1 
IS_TEMPLATE = False;
```
1. OWNER — Нам всегда нужно определить владение базой данных, приписанное роли (с или без логина).
2. IS_TEMPLATE — В PostgreSQL мы можем определить базу данных в качестве шаблона для использования при создании других баз данных. В данном случае это не шаблонная база данных.
Мы получим отказ в разрешении.

Заключение

В этой статье мы рассмотрели некоторые роли и способы присвоить их пользователям/логинам в SQL Server, Oracle и PostgreSQL. Мы также отметили различие в концепции ролей и некоторые из новых предопределенных ролей в SQL Server 2022. В следующей части статьи мы глубже погрузимся в оператор GRANT и способы предоставления привилегий на схемы, а не только таблицы, наследование, а также те различные системные представления, которые могут помочь управлять всеми этими привилегиями.

SQL-Ex blog

Информационная безопасность, касается ли это баз данных, приложений или больших ИТ-систем, основана на принципах аутентификации (Authentication) и авторизации (Authorization), которые часто именуют как AuthN и AuthZ соответственно.

Безопасность в PostgreSQL также включает создание ролей, которые разрешают доступ к ресурсам сервера (AuthN) с последующим предоставлением соответствующих привилегий на объекты базы данных (AuthZ). Понимание того, как каждый из этих компонентов работает совместно с другими, является существенным для эффективного управления безопасностью на кластере PostgreSQL.
- Роли: Имеется только один тип доверителя аутентификации в PostgreSQL, ROLE, которая существует на уровне кластера. По соглашению РОЛЬ, которая разрешает вход, считается пользователем (user), а роль, которой не разрешен вход, — группой (group). Заметьте, что хотя все еще существуют команды CREATE USER и CREATE GROUP, они просто являются псевдонимами команды CREATE ROLE.
- Объекты базы данных: Все, что может быть создано и доступно в кластере 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) привилегий новым ролям, как индивидуально, так и посредством наследования, с тем, чтобы наши новые роли могли выполнять необходимую работу для разработки новой функциональности приложения.

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

Как посмотреть роли имеющиеся в кластере postgresql

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

При подключении к серверу баз данных клиент должен указать имя базы в запросе подключения. Обращаться к нескольким базам через одно подключение нельзя, однако клиенты могут открыть несколько подключений к одной базе или к разным. Безопасность на уровне базы обеспечивают две составляющие: управление подключениями (см. Раздел 20.1), которое осуществляется на уровне соединения, и управление доступом к объектам (см. Раздел 5.7), для которого реализована система прав. Обёртки сторонних данных (см. postgres_fdw) позволяют создать в одной базе данных объекты, скрывающие за собой объекты в других базах или кластерах. Подобную же функциональность предоставляет и более старый модуль dblink (см. dblink). По умолчанию все пользователи могут подключаться ко всем базам данных, используя все методы подключения.

В случаях, когда в кластере PostgreSQL планируется размещать данные несвязанных проектов или с ним будут работать пользователи, которые в принципе не должны никак взаимодействовать, рекомендуется использовать отдельные базы данных и организовать управление подключением и доступом к объектам соответствующим образом. Если же проекты или пользователи взаимосвязаны и должны иметь возможность использовать ресурсы друг друга, они должны размещаться в одной базе данных, но, возможно, в отдельных схемах. Таким образом будет создана модульная структура с изолированными пространствами имён и управлением доступа. Подробнее об управлении схемами рассказывается в Разделе 5.9.

Хотя в одном кластере можно создать несколько баз данных, прежде чем это делать, рекомендуется тщательно взвесить все связанные с этим риски и ограничения. В частности, наличие общего WAL (см. Главу 29) может повлиять на возможности резервного копирования и восстановления данных. Отдельные базы в кластере изолированы друг от друга с точки зрения пользователя, но с точки зрения администратора баз данных они тесно связаны.

Базы данных создаются командой CREATE DATABASE (см. Раздел 22.2), а удаляются командой DROP DATABASE (см. Раздел 22.5). Список существующих баз данных можно посмотреть в системном каталоге pg_database , например,
```
SELECT datname FROM pg_database;
```
Метакоманда \l или ключ -l командной строки приложения psql также позволяют вывести список существующих баз данных.

Примечание

Стандарт SQL называет базы данных « каталогами » , но на практике у них нет отличий.

Пред. Наверх След.

Глава 22. Управление базами данных Начало 22.2. Создание базы данных
Похожие публикации: