NetBash.ru

Вывод списка пользователей и методов их аутентификации

SELECT type, database, user_name, auth_method FROM pg_hba_file_rules(); type | database | user_name | auth_method -------+---------------+-------------+------------- local | {all} | {all} | trust local | {all} | {pgbouncer} | md5 host | {imaplan} | {imaplan} | trust local | {all} | {postgres} | peer local | {all} | {all} | peer host | {all} | {all} | md5 host | {all} | {all} | md5 local | {replication} | {all} | peer host | {replication} | {all} | md5 host | {replication} | {all} | md5 (10 rows)

Роли и атрибуты

Роль может быть не связана с пользователем ОС
Роли можно включать друг в друга
Свойства роли определяются атрибутами: LOGIN, SUPERUSER, CREATEDB, CREATEROLE и др.
Роль может выступать как пользователь, подключающийся к СУБД
psql, запущенный от имени пользователя ОС student, автоматически использует одноименную роль student, если в ключах утилиты не указана какая-либо другая роль
При создании кластера определяется одна начальная роль, имеющая суперпользовательский доступ (обычно она называется postgres)

megaplan=# --Создание роли megaplan=# CREATE ROLE user1 megaplan-# ; CREATE ROLE megaplan=# megaplan=# --Удаление роли megaplan=# DROP ROLE user1; DROP ROLE megaplan=#

createuser и dropuser

#Для удобства поставляются программы createuser и dropuser, которые являются обёртками для этих команд SQL и вызываются из командной строки оболочки ОС: sudo -u postgres createuser user1 sudo -u postgres dropuser user1

Получить список существующих роле

postgres=# --получить список существующих ролей postgres=# SELECT rolname FROM pg_roles;

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

SELECT rolname FROM pg_roles WHERE rolcanlogin; #Можно использовать метакоманду \du для получения списка ролей и их прав postgres=# \du List of roles -[ RECORD 1 ]---------------------------------------------------------- Role name | postgres Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS Member of | {}

Роль обладает некоторыми атрибутами, определяющими ее общие особенности и права (не связанные с определенными объектами)
Любой атрибут обычно имеет два варианта, например CREATEDB и NOCREATEDB
Если у роли есть атрибут LOGIN, тогда эта роль - пользователь.
Если у роли есть атрибут NOLOGIN - роль не является пользовательской и не сможет подключиться к серверу, такая роль может использоваться для включения других ролей
Иначе говоря, поль с атрибутом LOGIN можно рассматривать как пользователя базы данных, для создания такой роли можно использовать два варианта:
CREATE ROLE имя LOGIN;
CREATE USER имя;
Разница в том, что CREATE USER по умолчанию включает атрибут LOGIN

postgres=# CREATE ROLE user1; CREATE ROLE postgres=# postgres=# CREATE USER user2; CREATE ROLE postgres=# postgres=# \du List of roles -[ RECORD 1 ]---------------------------------------------------------- Role name | postgres Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS Member of | {} -[ RECORD 2 ]---------------------------------------------------------- Role name | user1 Attributes | Cannot login Member of | {} -[ RECORD 3 ]---------------------------------------------------------- Role name | user2 Attributes | Member of | {} postgres=# postgres=# SELECT rolname FROM pg_roles WHERE rolcanlogin; -[ RECORD 1 ]----- rolname | postgres -[ RECORD 2 ]----- rolname | user2 postgres=#

Статус суперпользователя позволяет обходить все проверки прав доступа, за исключением права на вход в систему

postgres=# CREATE USER user3 SUPERUSER ; CREATE ROLE postgres=# postgres=# SELECT rolname FROM pg_roles WHERE rolcanlogin; -[ RECORD 1 ]----- rolname | postgres -[ RECORD 2 ]----- rolname | user2 -[ RECORD 3 ]----- rolname | user3 postgres=# \du List of roles -[ RECORD 1 ]---------------------------------------------------------- Role name | postgres Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS Member of | {} -[ RECORD 2 ]---------------------------------------------------------- Role name | user1 Attributes | Cannot login Member of | {} -[ RECORD 4 ]---------------------------------------------------------- Role name | user3 Attributes | Superuser Member of | {} postgres=#

Роль должна явно иметь разрешение на создание базы данных

postgres=# CREATE ROLE user4 CREATEDB ; postgres=# \du List of roles ... -[ RECORD 5 ]---------------------------------------------------------- Role name | user4 Attributes | Create DB, Cannot login Member of | {}

Роль должна явно иметь разрешение на создание других ролей
Роль с правом CREATEROLE может изменять и удалять роли, которые были назначены пользователю с правами CREATEROLE и ADMIN OPTION
Поэтому по умолчанию пользователь с правом CREATEROLE может изменять и удалять созданные им роли. Изменение роли включает большинство действий команды ALTER ROLE, например смену пароля, а также действия команд COMMENT и SECURITY LABEL в отношении ролей.
Однако, имея право CREATEROLE, нельзя ни создавать, ни каким-либо образом влиять на роли SUPERUSER. Кроме того, с правом CREATEROLE нельзя ни создавать пользователей REPLICATION, ни предоставлять или отзывать право REPLICATION, ни изменять свойства ролей таких пользователей. Тем не менее, это право позволяет использовать команды ALTER ROLE ... SET и ALTER ROLE ... RENAME в отношении ролей REPLICATION, а также COMMENT ON ROLE, SECURITY LABEL ON ROLE и DROP ROLE. Наконец, право CREATEROLE не даёт возможности предоставлять или отзывать право BYPASSRLS.
Источник: https://postgrespro.ru/docs/postgresql/16/role-attributes

---

Роль, используемая для потоковой репликации, также должна иметь атрибут LOGIN
Роль должна иметь явное разрешение на запуск потоковой репликации (за исключением суперпользователей)
Для создания такой роли используется CREATE ROLE имя REPLICATION LOGIN

---

Пароль имеет значение, если метод аутентификации клиентов требует, чтобы пользователи предоставляли пароль при подключении к базе данных. Методы аутентификации password и md5 используют пароли
При указании пустого значения будет задан пароль NULL, что не позволит данному пользователю пройти проверку подлинности по паролю. При желании пароль NULL можно установить явно, указав PASSWORD NULL.
Пароль указывается при создании роли:

postgres=# CREATE ROLE user5 PASSWORD '123456';

Наследование прав

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

postgres=# CREATE ROLE user6 NOINHERIT ; CREATE ROLE postgres=# \du List of roles ... -[ RECORD 7 ]---------------------------------------------------------- Role name | user6 Attributes | No inheritance, Cannot login Member of | {}

Атрибуты наследования отдельных прав можно переопределить, указав WITH INHERIT TRUE или WITH INHERIT FALSE

Ограничение соединений

Для роли можно ограничить число одновременных соединений.

postgres=# CREATE ROLE user7 CONNECTION LIMIT 2; CREATE ROLE postgres=# postgres=# \du List of roles ..... -[ RECORD 8 ]---------------------------------------------------------- Role name | user7 Attributes | Cannot login + | 2 connections Member of | {}

ALTER ROLE

Атрибуты ролей могут быть изменены после создания командой ALTER ROLE

Когда не суперпользователь с правом CREATEROLE создаёт роль, созданная роль автоматически становится членом создавшей её роли, как если бы начальный суперпользователь выполнил команду GRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE, INHERIT FALSE
Поскольку пользователь с правом CREATEROLE может пользоваться специальными правами в отношении существующей роли только при наличии атрибута ADMIN OPTION, такого права вполне достаточно для того, чтобы пользователь с правом CREATEROLE мог администрировать созданную им роль. Тем не менее, поскольку роль создаётся с атрибутами INHERIT FALSE, SET FALSE, пользователь с правом CREATEROLE не будет наследовать права созданной роли и не сможет переключиться на неё с помощью команды SET ROLE. Однако поскольку любой пользователь с правом ADMIN OPTION для роли может разрешать членство в этой роли любым пользователям, пользователь с правом CREATEROLE может стать членом созданной им роли, просто назначив эту роль на себя при помощи INHERIT и/или SET. Поэтому факт того, что права не наследуются по умолчанию и что право не назначается по умолчанию командой SET ROLE, — это способ оградиться от случайных ошибок, а не средство защиты.
Роли определяются на уровне кластера баз данных, так что они действуют во всех базах в кластере
Во время создания роли можно сразу назначить создаваемую роль членом существующей роли, а также назначить существующие роли членами создаваемой роли. Правила, по которым включаются начальные параметры членства в роли, описаны ниже в предложениях IN ROLE, ROLE и ADMIN.
Команда GRANT позволяет управлять назначением членов ролей и даёт возможность изменять эти параметры после создания новой роли
Источник: https://postgrespro.ru/docs/postgresql/16/sql-createrole

Дополнительные параметры

VALID UNTIL устанавливает дату и время, после которого пароль роли перестаёт действовать
IN ROLE новая роль автоматически становится членом указанных существующих ролей
IN GROUP — устаревшее написание предложения IN ROLE
ADMIN работает подобно ROLE, но перечисленные в нём роли добавляются как члены новой роли с включённым атрибутом ADMIN, что даёт им право включать в новою роль другие роли.

GRANT и REVOKE

Для добавления и удаления членов ролей, используемых в качестве групп, рекомендуется использовать GRANT и REVOKE
Определённые здесь атрибуты роли не наследуются, т. е. членство в роли, например, с правом CREATEDB, не позволит её участнику создавать новые базы данных, даже если членство было выдано с атрибутом INHERIT. Разумеется, если в выдаче членства есть атрибут SET, роль участника сможет выполнить команду SET ROLE для роли createdb, а затем создать новую базу данных.
Источник: https://postgrespro.ru/docs/postgresql/16/sql-createrole

--Создать роль postgres=# CREATE ROLE user8 LOGIN PASSWORD '1234'; postgres=# --Создать базу данных postgres=# CREATE DATABASE newbase; CREATE DATABASE postgres=# postgres=# \c newbase You are now connected to database "newbase" as user "postgres". newbase=#

Парольная аутентификация

Если используется аутентификация по паролю, для роли должен быть установлен пароль. Пользователю без пароля будет отказано в доступе при попытке подключиться. (Проверить )
Пароль хранится в системном каталоге pg_authid
Пароль можно ввести вручную, из переменной окружения PGPASSWORD, из файла ~/.pgpass При использовании файла .pgpass на клиенте, необходимо правильно установить права (600) для файла, в противном случае входи будет игнорирован сервером

#Посмотреть путь к файлу hba_conf postgres=# postgres=# SHOW hba_file; hba_file ------------------------------------- /etc/postgresql/14/main/pg_hba.conf (1 row) postgres=# postgres=# --Прочитать содержимое файла с помощью SQL postgres=# SELECT type, database, user_name, address, auth_method FROM pg_hba_file_rules(); type | database | user_name | address | auth_method -------+---------------+------------+-----------+--------------- local | {all} | {postgres} | | peer local | {all} | {all} | | peer host | {all} | {all} | 127.0.0.1 | scram-sha-256 host | {all} | {all} | ::1 | scram-sha-256 local | {replication} | {all} | | peer host | {replication} | {all} | 127.0.0.1 | scram-sha-256 host | {replication} | {all} | ::1 | scram-sha-256 (7 rows) postgres=# postgres=# postgres=# CREATE DATABASE access_overview; CREATE DATABASE postgres=# postgres=# \c access_overview You are now connected to database "access_overview" as user "postgres". access_overview=#--Создать ползователя access_overview=# CREATE ROLE user9 PASSWORD '12345'; CREATE ROLE access_overview=#-- Изменить пароль для пользователя access_overview=# ALTER ROLE user9 PASSWORD '123456'; ALTER ROLE access_overview=# access_overview=# --Изменить права на LOGIN access_overview=# ALTER ROLE user9 LOGIN; ALTER ROLE access_overview=# #Попробуем установить соединение psql 'host=localhost user=user9 dbname=access_overview password=123456' psql (14.13 (Ubuntu 14.13-0ubuntu0.22.04.1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. access_overview=>

Файл сопоставления имён пользователей

Для локальных подключений обычно рекомендуется использовать метод peer, хотя в некоторых обстоятельствах может быть достаточно и режима trust. Для удалённых подключений самой простой будет аутентификация по паролю.
https://postgrespro.ru/docs/postgresql/16/auth-methods Когда используется внешняя система аутентификации, например Ident или GSSAPI, имя пользователя операционной системы, устанавливающего подключение, может не совпадать с именем целевого пользователя (роли) базы данных. В этом случае можно применить сопоставление имён пользователей, чтобы сменить имя пользователя операционной системы на имя пользователя БД. Чтобы задействовать сопоставление имён, укажите map=имя-сопоставления в поле параметров в pg_hba.conf. Этот параметр поддерживается для всех методов аутентификации, которые принимают внешние имена пользователей. Так как для разных подключений могут требоваться разные сопоставления, сопоставление определяется параметром имя-сопоставления в pg_hba.conf для каждого отдельного подключения
Сопоставления имён пользователя определяются в файле сопоставления ident, который по умолчанию называется pg_ident.conf и хранится в каталоге данных кластера
Синтаксис: имя-сопоставления имя-системного пользователя имя-пользователя-БД
имя-сопоставления является произвольным именем, на которое будет ссылаться файл сопоставления файла pg_hba.conf
Системное представление pg_ident_file_mappings может быть полезно для предварительной проверки изменений в файле pg_ident.conf или для диагностики проблем, когда перезагрузка этого файла не даёт желаемого эффекта. Строки в этом представлении, содержащие в поле error не NULL, указывают на проблемы в соответствующих строках файла
Аутентификация ident, для которой используется служба, реализующая «Identification Protocol» (RFC 1413) на клиентском компьютере. (Для подключений через локальный сокет Unix этот метод работает как peer.)
Аутентификация peer, которая полагается на средства операционной системы, позволяющие узнать пользователя процесса на другой стороне локального подключения. Для удалённых подключений она не поддерживается.
Источник: https://postgrespro.ru/docs/postgresql/16/auth-username-maps

Пример файла pg_ident.conf # MAPNAME SYSTEM-USERNAME PG-USERNAME omicron bryanh bryanh omicron ann ann # на этих машинах у пользователя bob имя robert omicron robert bob # bryanh также может подключаться как guest1 omicron bryanh guest1

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

Чтобы разрешить использовать его другим ролям, нужно дать им права
Привилегии определяют права доступа ролей к объектам
Для таблиц и представлений:
SELECT чтение данных. Также позволяет выполнять COPY TO. Данное право требуется для обращения к существующим значениям столбцов в UPDATE, DELETE или MERGE.
INSERT вставка строк. Может назначаться для отдельных столбцов. Также позволяет выполнять COPY FROM.
UPDATE изменение строк. Для любой нетривиальной команды UPDATE потребуется и право SELECT, так как она должна обратиться к столбцам таблицы, чтобы определить, какие строки подлежат изменению, и/или вычислить новые значения столбцов.
REFERENCES внешний ключ (для таблиц). Позволяет создавать ограничение внешнего ключа, обращающееся к таблице или определённым столбцам таблицы.
DELETE удаление строк. Потребуется также право SELECT, так как она должна обратиться к колонкам таблицы, чтобы определить, какие строки подлежат удалению.
TRUNCATE опустошение (для таблиц)
TRIGGER создание триггеров. Позволяет создавать триггер для таблицы, представления и т. п.
CREATE - Для баз данных это право позволяет создавать схемы и публикации, а также устанавливать доверенные расширения в конкретной базе. Для схем это право позволяет создавать новые объекты в заданной схеме. Чтобы переименовать существующий объект, необходимо быть его владельцем и иметь это право для схемы, содержащей его.
Для табличных пространств это право позволяет создавать таблицы, индексы и временные файлы в определённом табличном пространстве, а также создавать базы данных, для которых это пространство будет основным.
CONNECT - Позволяет подключаться к базе данных
TEMPORARY - Позволяет создавать временные таблицы в определённой базе данных.
EXECUTE - Позволяет вызывать функцию или процедуру, в том числе использовать любые операторы, реализованные данной функцией.
SET - Позволяет задать для параметра конфигурации сервера новое значение в текущем сеансе
ALTER SYSTEM - Позволяет присваивать значение параметру конфигурации сервера, используя команду ALTER SYSTEM

---

Привилегии устанавливают связь между субъектами(ролями) и объектами кластера. Они определяют действия, доступные для ролей в отношении этих объектов
Список возможных привилегий отличается в зависимости от типа объектов
Право изменять или удалять объект является неотъемлемым правом владельца объекта, его нельзя лишиться или передать другому. (Однако как и все другие права, это право наследуют члены роли-владельца)
Объекту можно назначить нового владельца с помощью команды ALTER
ALTER TABLE имя_таблицы OWNER TO новый_владелец;
Суперпользователь может делать это без ограничений, а обычный пользователь — только если он является одновременно текущим владельцем объекта (или наследует права роли владельца) и может выполнять SET ROLE для новой роли владельца.
Для назначения прав применяется команда GRANT. Например, если в базе данных есть роль joe и таблица accounts, право на изменение таблицы можно дать этой роли так:
GRANT UPDATE ON accounts TO joe;
Если вместо конкретного права написать ALL, роль получит все права, применимые для объекта этого типа
PostgreSQL по умолчанию назначает роли PUBLIC права для некоторых типов объектов, когда эти объекты создаются
Для таблиц, столбцов, последовательностей, обёрток сторонних данных, сторонних серверов, больших объектов, схем, табличных пространств или параметров конфигурации PUBLIC по умолчанию никаких прав не получает. Для других типов объектов PUBLIC получает следующие права по умолчанию: CONNECT и TEMPORARY (создание временных таблиц) для баз данных; EXECUTE — для функций и процедур; USAGE — для языков и типов данных (включая домены). Владелец объекта, конечно же, может отозвать (посредством REVOKE) как явно назначенные права, так и права по умолчанию.
Для назначения права всем ролям в системе можно использовать специальное имя «роли»: PUBLIC
Чтобы лишить пользователей ранее данных им прав, используйте команду REVOKE:
REVOKE ALL ON accounts FROM PUBLIC;
Владелец объекта может лишить себя обычных прав, например запретить не только всем остальным, но и себе, вносить изменения в таблицу. Однако владельцы всегда имеют возможность управлять правами, так что они могут в любом случае вернуть себе права, которых лишились.
Сокращённые обозначения прав в ACL можно увидеть в таблице 5.1 на официальном сайте Postgresql
В Таблица 5.2 для каждого типа SQL-объекта показаны относящиеся к нему права, с использованием приведённых выше сокращений. Также в ней для каждого типа приведена команда psql, которая позволяет узнать, какие права назначены для объекта этого типа
Права, назначенные для определённого объекта, выводятся в виде списка элементов aclitem, где каждый aclitem имеет такой формат:
правообладатель=аббревиатура-права[*].../праводатель
Каждый aclitem содержит разрешения, которые были предоставлены субъекту определённым праводателем. Конкретные права представлены однобуквенными аббревиатурами из таблицы Таблица 5.1 с добавлением *, если право было выдано с параметром передачи. Например, запись calvin=r*w/hobbes означает, что роль calvin имеет право SELECT (r) с возможностью передачи (*), а также непередаваемое право UPDATE (w), и оба эти права даны ему ролью hobbes. Если calvin имеет для этого объекта и другие права, предоставленные ему другим праводателем, они выводятся в отдельном элементе aclitem. Пустое поле правообладателя в aclitem соответствует роли PUBLIC.
https://postgrespro.ru/docs/postgresql/16/ddl-priv
https://postgrespro.ru/docs/postgresql/16/sql-grant

--Access privileges postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ------------------+----------+----------+-------------+-------------+----------------------- access_overview | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | admin_monitoring | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | appdb | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | configdb | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | data_lowlevel | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | newbase | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | postgres | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | template0 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres testdb | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | (10 rows) postgres=# data_lowlevel=# data_lowlevel=# GRANT SELECT ON t TO PUBLIC ; GRANT data_lowlevel=# data_lowlevel=# GRANT SELECT, UPDATE, INSERT ON t TO user9; GRANT data_lowlevel=# data_lowlevel=# data_lowlevel=# GRANT SELECT (n), UPDATE (n) ON t TO user8; GRANT data_lowlevel=#--Показать привилегии пользователей в таблице t data_lowlevel=# \dp t Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+------+-------+---------------------------+---------------------+---------- public | t | table | postgres=arwdDxt/postgres+| n: +| | | | =r/postgres +| user8=rw/postgres | | | | user9=arw/postgres | | (1 row)

Если столбец «Права доступа» (Access privileges) для данного объекта пуст, это значит, что для объекта действуют стандартные права (то есть запись прав в соответствующем каталоге содержит NULL). Права по умолчанию всегда включают все права для владельца и могут также включать некоторые права для PUBLIC в зависимости от типа объекта, как разъяснялось выше. Первая команда GRANT или REVOKE для объекта приводит к созданию записи прав по умолчанию (например, {miriam=arwdDxt/miriam}), а затем изменяет эту запись в соответствии с заданным запросом. Подобным образом, строки, показанные в столбце «Права доступа к столбцам» (Column privileges), выводятся только для столбцов с нестандартными правами доступа. (Заметьте, что в данном контексте под «стандартными правами» всегда подразумевается встроенный набор прав, предопределённый для типа объекта. Если с объектом связан набор прав по умолчанию, полученный после изменения в результате ALTER DEFAULT PRIVILEGES, изменённые права будут всегда выводиться явно, показывая эффект команды ALTER.)
Заметьте, что право распоряжения правами, которое имеет владелец, не отмечается в выводимой сводке. Знаком * отмечаются только те права с правом передачи, которые были явно назначены кому-либо.
Источник: https://postgrespro.ru/docs/postgresql/16/ddl-priv

GRANT

Документация: https://postgrespro.ru/docs/postgresql/16/sql-grant
GRANT имеет две основные разновиности: права для доступа к объектам баз данных (таблицам, столбцам, представлениям, сторонним таблицам, последовательностям, базам данных, обёрткам сторонних данных, сторонним серверам, функциям, процедурам, процедурным языкам, большим объектам, параметрам конфигурации, схемам, табличным пространствам или типам), а вторая назначает одни роли членами других.
Ключевое слово PUBLIC означает, что права даются всем ролям, включая те, что могут быть созданы позже. PUBLIC можно воспринимать как неявно определённую группу, в которую входят все роли. Любая конкретная роль получит в сумме все права, данные непосредственно ей и ролям, членом которых она является, а также права, данные роли PUBLIC
Если указано WITH GRANT OPTION, получатель права, в свою очередь, может давать его другим. Без этого указания распоряжаться своим правом он не сможет. Группе PUBLIC право передачи права дать нельзя

---

ALL PRIVILEGES
Синтаксис FUNCTION распространяется на обычные, агрегатные и оконные функции, но не на процедуры; для последних предназначен синтаксис PROCEDURE
ALL TABLES распространяется и на представления со сторонними таблицами так же, как и вариант GRANT для конкретного объекта.
ALL FUNCTIONS распространяется также на агрегатные и оконные функции, но не на процедуры, тоже подобно варианту GRANT для конкретного объекта. Чтобы охватить и процедуры, воспользуйтесь формой ALL ROUTINES

GRANT для ролей

Включает роль в члены одной или нескольких других ролей, а также изменяет параметры членства SET, INHERIT и ADMIN
Каждый из параметров, описанных ниже, может принимать значение TRUE или FALSE. Ключевое слово OPTION принимается в качестве синонима TRUE, то есть WITH ADMIN OPTION и WITH ADMIN TRUE являются синонимами
Получивший членство в роли с указанием ADMIN сможет, в свою очередь, включать в члены этой роли, а также исключать из неё другие роли
Параметр INHERIT управляет статусом наследования нового членства. Если для этого параметра установлено значение TRUE, новый член наследует от выданной роли
Если для SET указать TRUE, член роли сможет менять назначенную ему роль, используя команду SET ROLE
Чтобы создать объект, владельцем которого будет другая роль, или назначить другой роли права владения существующим объектом, необходимо иметь право SET ROLE для этой роли, в противном случае команды ALTER ... OWNER TO или CREATE DATABASE ... OWNER выдадут ошибку. Однако пользователь, наследующий права роли, но не имеющий право SET ROLE для этой роли, может получить полный доступ к роли путём манипуляций с объектами, которыми владеет эта роль (например, переопределить существующую функцию с использованием «троянского кода»). Таким образом, если права роли должны наследоваться, но доступ к роли через SET ROLE предоставляться не должен, не следует выдавать ей права владения SQL-объектами
С указанием GRANTED BY права выдаются от имени указанной роли. Это можно сделать, только если у пользователя есть права для этой роли. Роль, назначаемая праводателем, должна иметь право ADMIN OPTION для целевой роли
В отличие от прав, членство в ролях нельзя назначить группе PUBLIC
Для лишения субъектов прав доступа применяется команда REVOKE
Если назначить пользователю требуемое право на уровне таблицы, а затем отозвать его для одного из столбцов, это не даст эффекта, которого можно было бы ожидать: операция с правами на уровне столбцов не затронет право на уровне таблицы
GRANT и REVOKE также могут быть выполнены ролью, которая не является владельцем заданного объекта, но является членом роли-владельца, либо членом роли, имеющей права WITH GRANT OPTION для этого объекта

---

Источник: https://postgrespro.ru/docs/postgresql/16/sql-grant

--Включение в роль admins пользователя joe GRANT admins TO joe;

Для табличных пространств есть привилегия CREATE, разрешающая создание объектов в этом пространстве
Для баз данных привилегия CREATE разрешает создавать схемы в этой БД, а для схемы привилегия CREATE разрешает создавать объектыв этой схеме
Поскольку точное имя схемы для временных объектов заранее неизвестно, привилегия на создание временных таблиц вынесена на уровень БД (TEMPORARY)
Привилегия USAGE схемы разрешает обращаться к объектам в этой схеме

Категории ролей

1. Роли с атрибутом SUPERUSER
2. Владелец объекта. Владельцем считается не только сама роль-владелец, но и любая другая роль, включенная в нее
3. Остальные роли имеют доступ к объекту только в рамках выданных им привилегий

---

Чтобы проверить, есть ли у роли необходимая привилегия в отношении некоторого объекта, можно воспользоваться функциями has_*_privilege
https://postgrespro.ru/docs/postgresql/16/functions-info

data_lowlevel=# --идентификатор серверного процесса, обслуживающего текущий сеанс data_lowlevel=# SELECT pg_backend_pid () ; pg_backend_pid ---------------- 101792 (1 row) data_lowlevel=# data_lowlevel=# --Выдаёт массив с идентификаторами процессов сеансов, которые блокируют серверный процесс с указанным идентификатором data_lowlevel=# --либо пустой массив, если указанный серверный процесс не найден или не заблокирован data_lowlevel=# SELECT pg_blocking_pids ( 101792 ); pg_blocking_pids ------------------ {} (1 row) data_lowlevel=# data_lowlevel=# --Выдаёт время, когда в последний раз сервер загружал файлы конфигурации data_lowlevel=# SELECT pg_conf_load_time (); pg_conf_load_time ------------------------------- 2024-11-11 10:05:54.796579+03 (1 row) data_lowlevel=# data_lowlevel=# --Выдаёт время, когда был запущен сервер. data_lowlevel=# SELECT pg_postmaster_start_time (); pg_postmaster_start_time ------------------------------- 2024-11-11 10:05:54.881587+03 (1 row) data_lowlevel=# data_lowlevel=# --Выдаёт массив с идентификаторами процессов сеансов, которые блокируют серверный процесс с указанным идентификатором, data_lowlevel=# --не давая ему получить безопасный снимок, либо выдаёт пустой массив, если такой серверный процесс не найден или он не заблокирован. data_lowlevel=# SELECT pg_safe_snapshot_blocking_pids ( 101792 ); pg_safe_snapshot_blocking_pids -------------------------------- {} (1 row) data_lowlevel=#-- Выдаёт имя пользователя сеанса data_lowlevel=# SELECT session_user; session_user -------------- postgres (1 row) data_lowlevel=#--Выдаёт текстовую строку, описывающую версию сервера PostgreSQL data_lowlevel=# SELECT version(); version ---------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 14.13 (Ubuntu 14.13-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit (1 row) data_lowlevel=#

Функция session_user обычно возвращает имя пользователя, установившего текущее соединение с базой данных, но суперпользователи могут изменить это имя, выполнив команду SET SESSION AUTHORIZATION. Функция current_user возвращает идентификатор пользователя, по которому будут проверяться его права. Обычно это тот же пользователь, что и пользователь сеанса, но его можно сменить с помощью SET ROLE. Этот идентификатор также меняется при выполнении функций с атрибутом SECURITY DEFINER. На языке Unix пользователь сеанса называется «реальным», а текущий — «эффективным». Имена current_role и user являются синонимами current_user. (В стандарте SQL current_role и current_user имеют разное значение, но в PostgreSQL они не различаются, так как пользователи и роли объединены в единую сущность.)

---

Источник: https://postgrespro.ru/docs/postgresql/16/functions-info

---

В Таблице 9.68 показаны функции, позволяющие программно проверить права доступа к объектам.
Этим функциям в качестве идентификатора пользователя, для которого запрашиваются права, можно передать его имя или OID (pg_authid.oid), а также можно передать public (это будет указывать на псевдороль PUBLIC). Кроме того, аргумент user можно полностью опустить, тогда будет подразумеваться current_user
Объект, права для доступа к которому запрашиваются, также можно задать по имени или по OID
Запрашиваемое право записывается текстовой строкой, которая должна задавать одно из прав доступа, соответствующих типу объекта (например, SELECT)

--Имеет ли право пользователь на чтение таблицы t data_lowlevel=# SELECT has_any_column_privilege('user4', 't', 'select'); has_any_column_privilege -------------------------- t (1 row) --Имеен ли право пользователь на изменение таблицы t
data_lowlevel=# SELECT has_any_column_privilege('user4', 't', 'update'); has_any_column_privilege -------------------------- f (1 row) data_lowlevel=#

---

Источники