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

SQL-Ex blog

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

• Роль логина — роли, которые могут осуществлять вход. Они эквивалентны пользователям в других системах баз данных.
• Роль группы — когда роли содержат другие роли, они называются групповыми ролями.

Создать роль

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

CREATE ROLE role_name;

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

CREATE ROLE admin1;

Чтобы получить список всех имеющихся ролей на сервере баз данных PostgreSQL, используйте команду ‘\du’, как показано ниже:

Как говорилось выше, роль не зависит от базы данных, что мы тут в точности наблюдаем. Она доступна для двух баз данных, ‘postgres’, а также ‘demo’.

Замечание: Роли admin1 присвоен атрибут ‘cannot login’, что означает, что она не имеет прав на вход в базу (базы) данных. Однако есть способы дать разрешения роли. Например, мы хотим дать роли права на вход. Давайте рассмотрим разные способы в разделе ниже.

Изменить роль

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

ALTER ROLE "admin1" WITH LOGIN;

Второй вариант — это удалить существующую роль и снова создать ее с необходимым разрешением.

Drop role role_name; 
CREATE ROLE role_name WITH OPTION;

Во втором варианте ключевое слово WITH не обязательно. Другими опциями, доступными для использования, являются SUPERUSER, CREATEDB, CREATEROLE и т.д., что видно для других ролей на скриншоте ниже:

Создать роль с паролем

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

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

CREATE ROLE role_name 
LOGIN 
PASSWORD 'password';

Пароль должен заключаться в одинарные кавычки (‘). Давайте рассмотрим пример для этого сценария:

CREATE ROLE admin3 
LOGIN 
PASSWORD 'test123';

Используя оператор ‘select current_user’, можно узнать текущего пользователя. Роль и пользователь — одно и то же в PostgreSQL.

Для переключения на другую роль можно использовать следующую команду:

\c database_name role_name

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

Создать роль с привилегиями суперпользователя (superuser)

Роль ‘SUPERUSER’ обходит все проверки разрешений, за исключением права на вход. Это опасная привилегия и не должна использоваться беззаботно; лучше всего выполнять большую часть работы с ролью, которая не является суперпользователем. Для создания нового суперпользователя базы данных используйте следующий синтаксис. Суперпользователь может переопределить все ограничения доступа в базе данных и, следовательно, должен создаваться только в случае необходимости. Другой момент — вы должны быть суперпользователем, чтобы создать другую роль superuser.

CREATE ROLE shivayan 
SUPERUSER 
LOGIN 
PASSWORD 'test123';

Поскольку по умолчанию роль ‘superuser’ не имеет прав на вход, здесь эта возможность явно указывается во время создания роли.

Создание роли с привилегией создания баз данных

Эти предложения определяют возможность роли создавать базы данных. Если указано ‘CREATEDB’, создаваемой роли будет позволено конструировать новые базы данных. Указание ‘NOCREATEDB’ будет запрещать роли создавать базы данных. Если ничего не указано, ‘NOCREATEDB’ принимается по умолчанию.

CREATE ROLE sysdba 
CREATEDB 
LOGIN 
PASSWORD 'test123';

Роль ‘CREATE DB’ дает права на создание базы данных.

Доступ к ролям посредством pgAdmin

В pgAdmin вы можете выполнить навигацию к узлу ‘Login/Group Roles’, как показано ниже, чтобы получить список всех имеющихся ролей.

Обратите внимание, что роли с префиксом ‘pg_’ являются системными ролями. Системные роли — это набор ролей по умолчанию, которые предоставляют доступ к определенным, часто необходимым, специальным возможностям и информации. Администраторы могут представлять (GRANT) эти роли пользователям и/или другим ролям в своей среде, обеспечивая пользователям доступ к специальным возможностям и информации.

Привилегии роли доступны и могут обновляться на вкладке ‘Privileges’, как показано ниже:

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

Обновление кластера PostgreSQL 10 до 15

При обновлении PostgreSQL до основной версии используется утилита pg_upgrade . Она позволяет обновлять данные, хранящиеся в файлах данных PostgreSQL, до более поздней версии PostgreSQL.

Обновление кластера PostgreSQL состоит из шести этапов:

1. Резервное копирование базы данных.
2. Установка PostgreSQL 15.
3. Обновление данных в каталоге базы данных.
4. Запуск кластера Patroni.
5. Проверка после установки.
6. Очистка старых данных.

Шаг 1. Резервное копирование базы данных

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

Если на сервере PostgreSQL имеются сторонние базы данных, их резервные копии также необходимо создать.

Вы можете создать резервную копию базы данных elma365 с помощью утилиты pg_dump :

sudo -u postgres pg_dump elma365 -Fc > path/to/elma365.bak

Шаг 2. Установка PostgreSQL 15

Установите PostgreSQL 15 на все ноды кластера, в которых установлена версия 10:

sudo apt install postgresql-15

Шаг 3. Обновление данных в каталоге базы данных

1. В кластере PostrgeSQL определите Leader-ноду:

patronictl -c /etc/patroni/config.yml list

1. На всех нодах кластера PostrgeSQL остановите Patroni:

sudo systemctl stop patroni.service

1. На всех нодах кластера остановите PostgreSQL 10 и PostgreSQL 15:

sudo systemctl stop postgresql@10 postgresql@15

1. Убедитесь, что кластеры PostgreSQL 10 и PostgreSQL 15 остановлены:

1. На Leader-ноде выполните проверку на предмет совместимости конфигураций баз данных:

sudo -iu postgres /usr/lib/postgresql/15/bin/pg_upgrade \
-o «-c config_file=/etc/postgresql/10/main/postgresql.conf»\
-O «-c config_file=/etc/postgresql/15/main/postgresql.conf» \
—old-datadir=/var/lib/postgresql/10/main/ \
—new-datadir=/var/lib/postgresql/15/main/ \
—old-bindir=/usr/lib/postgresql/10/bin \
—new-bindir=/usr/lib/postgresql/15/bin —check

• /usr/lib/postgresql/10/bin и /usr/lib/postgresql/15/bin — пути к бинарным файлам PostgreSQL версий 10 и 15;
• /var/lib/postgresql/10/main и /var/lib/postgresql/15/main — пути к каталогам данных.

1. На Leader-ноде выполните перенос данных из PostgreSQL 10 в PostgreSQL 15:

sudo -iu postgres /usr/lib/postgresql/15/bin/pg_upgrade \
-o «-c config_file=/etc/postgresql/10/main/postgresql.conf» \
-O «-c config_file=/etc/postgresql/15/main/postgresql.conf» \
—old-datadir=/var/lib/postgresql/10/main/ \
—new-datadir=/var/lib/postgresql/15/main/ \
—old-bindir=/usr/lib/postgresql/10/bin \
—new-bindir=/usr/lib/postgresql/15/bin

• /usr/lib/postgresql/10/bin и /usr/lib/postgresql/15/bin — пути к бинарным файлам PostgreSQL версий 10 и 15;
• /var/lib/postgresql/10/main и /var/lib/postgresql/15/main — пути к каталогам данных.

Шаг 4. Запуск кластера Patroni

1. На пути до PostgreSQL 15 откройте файл конфигурации Patroni:

sudo nano /etc/patroni/config.yml

1. На каждой ноде кластера PostgreSQL замените параметры data_dir , bin_dir и config_dir , например:

.
data_dir: /var/lib/postgresql/15/main
bin_dir: /usr/lib/postgresql/15/bin
config_dir: /etc/postgresql/15/main
.

1. Получите наименование кластера PostgreSQL 10:

sudo cat /etc/patroni/config.yml | grep «scope» | awk ‘< print $2 >‘

1. На Leader-ноде в Patroni удалите кластер PostgreSQL 10:

patronictl -c /etc/patroni/config.yml remove postgres-cluster

• postgres-cluster — наименование кластера PostgreSQL 10, полученное на предыдущем этапе.

1. На Leader-ноде запустите Patroni:

sudo systemctl start patroni.service

1. На Replica-нодах удалите каталог данных PostgreSQL 15:

sudo rm -rf /var/lib/postgresql/15/main

1. На Replica-нодах запустите Patroni:

sudo systemctl start patroni.service

Шаг 5. Проверка после установки

1. Проверьте состояние кластера:

patronictl -c /etc/patroni/config.yml list

1. Убедитесь, что PostgreSQL 15 работает корректно и данные доступны.
2. Для оптимизации базы данных выполните команду на Leader-ноде:

sudo -u postgres /usr/lib/postgresql/15/bin/vacuumdb —all —analyze-in-stages

Шаг 6. Очистка старых данных

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

При необходимости после успешного обновления и проверки PostgreSQL 15 вы можете очистить старые данные:

sudo -u postgres ./delete_old_cluster.sh

Создание ролей

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

Алиса имеет возможность подключения (атрибут LOGIN) и создания других ролей (CREATEROLE).

postgres=# \c - alice

You are now connected to database "access_roles" as user "alice".

alice=> CREATE ROLE bob LOGIN; 

CREATE ROLE

Действительно, получилось и подключиться, и создать другого пользователя, Боба.

А вот Боб не сможет создать другую роль:

postgres$ psql -U bob -d access_roles

bob=> CREATE ROLE charlie LOGIN; 

ERROR: permission denied to create role

Посмотреть роли, имеющиеся в кластере, можно так:

alice=> \du 

List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- alice | Create role | <> bob | | <> postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | <>

Или в системном каталоге:

alice=> SELECT usename FROM pg_user; 

usename ---------- postgres alice bob (3 rows)

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

alice=> ALTER ROLE bob NOLOGIN; 

ALTER ROLE

А у себя самой Алиса отберет CREATEROLE:

alice=> ALTER ROLE alice NOCREATEROLE; 

ALTER ROLE

Такие пары, как LOGIN-NOLOGIN или CREATEROLE-NOCREATEROLE, есть и у других атрибутов.

Теперь Боб не сможет подключиться:

bob=> \c - bob

\connect: FATAL: role "bob" is not permitted to log in

Групповые роли

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

alice=> ALTER ROLE bob LOGIN; 

ERROR: permission denied

Чтобы снова наделить Алису супервозможностями, включим ее в суперпользовательскую роль postgres:

alice=> \c - postgres

You are now connected to database "access_roles" as user "postgres".

postgres=# GRANT postgres TO alice; 

GRANT ROLE

postgres=# \du 

List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+------------ alice | | bob | Cannot login | <> postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | <>

А чтобы Алиса не злоупотребляла полномочиями, сделаем так, чтобы все ее команды попадали в журнал сообщений:

postgres=# ALTER ROLE alice SET log_min_duration_statement=0; 

ALTER ROLE

Это еще один вариант установки конфигурационных параметров — он срабатывает при подключении пользователя к серверу.

Можно ограничить действие и конкретной базой данных:

postgres=# ALTER ROLE alice RESET log_min_duration_statement; 

ALTER ROLE

postgres=# ALTER ROLE alice IN DATABASE postgres SET log_min_duration_statement=0; 

ALTER ROLE

Алиса не получает возможности групповой роли автоматически. Она может ими воспользоваться, только если переключится на эту роль:

postgres=# \c - alice

You are now connected to database "access_roles" as user "alice".

alice=> SET ROLE postgres; 

alice=> ALTER ROLE bob LOGIN; 

ALTER ROLE

Это напоминает команду su в ОС Unix.

Чтобы понять, кем является пользователь на самом деле, и на какую роль он переключился, есть функции:

alice=> SELECT session_user, current_user; 

session_user | current_user --------------+-------------- alice | postgres (1 row)

Вернемся к прежней роли:

alice=> RESET ROLE; 

RESET

alice=> SELECT session_user, current_user; 

session_user | current_user --------------+-------------- alice | alice (1 row)

Владение объектами

Когда Алиса создает какой-либо объект в БД, она становится его владельцем.

alice=> CREATE TABLE test(id integer); 

CREATE TABLE

Как в этом убедиться? Владелец указан в столбце owner:

alice=> \dt test

List of relations Schema | Name | Type | Owner --------+------+-------+------- public | test | table | alice (1 row)

Удаление ролей

Удалить роль можно, если нет объектов, которыми она владеет.

alice=> \c - postgres

You are now connected to database "access_roles" as user "postgres".

postgres=# DROP ROLE alice; 

ERROR: role "alice" cannot be dropped because some objects depend on it DETAIL: owner of table test

Чтобы удалить Алису, можно передать ее объекты другой роли:

postgres=# REASSIGN OWNED BY alice TO bob; 

REASSIGN OWNED

postgres=# \dt test

List of relations Schema | Name | Type | Owner --------+------+-------+------- public | test | table | bob (1 row)

postgres=# DROP ROLE alice; 

DROP ROLE

Другой вариант — удалить объекты.

postgres=# DROP OWNED BY bob; 

DROP OWNED

postgres=# DROP ROLE bob; 

DROP ROLE

Надо только иметь в виду, что роль может владеть объектами в разных базах данных.

Как посмотреть роли имеющиеся в кластере 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. Создание базы данных