Superuser postgresql что это
Superuser postgresql что это
CREATE ROLE — создать роль в базе данных
Синтаксис
Описание
Учтите, что роли определяются на уровне кластера баз данных, так что они действуют во всех базах в кластере.
Параметры
Имя создаваемой роли. SUPERUSER
NOSUPERUSER
Заметьте, что pg_dump по умолчанию отключает row_security (устанавливает значение OFF ), чтобы гарантированно было выгружено всё содержимое таблицы. Если пользователь, запускающий pg_dump, не будет иметь необходимых прав, он получит ошибку. Однако суперпользователи и владелец выгружаемой таблицы всегда обходят защиту RLS. CONNECTION LIMIT предел_подключений
Эти ключевые слова определяют, будет ли пароль храниться в системных каталогах в зашифрованном виде. (При отсутствии явного указания поведение по умолчанию определяется конфигурационным параметром password_encryption.) Если пароль представлен в виде MD5-хеша, он сохраняется как есть, вне зависимости от того, присутствует ли указание ENCRYPTED или UNENCRYPTED (так как система не может расшифровать зашифрованный пароль). Это позволяет выгружать/загружать зашифрованные пароли при экспорте/импорте данных. VALID UNTIL ‘ дата_время ‘
Предложение VALID UNTIL устанавливает дату и время, после которого пароль роли перестаёт действовать. Если это предложение отсутствует, срок действия пароля будет неограниченным. IN ROLE имя_роли
Предложение SYSID игнорируется, но принимается для обратной совместимости.
Замечания
Предложение VALID UNTIL определяет срок действия только пароля, но не роли как таковой. В частности, ограничение срока пароля не действует при входе пользователя без проверки подлинности по паролю.
Атрибут INHERIT устанавливается по умолчанию в целях обратной совместимости: в предыдущих выпусках PostgreSQL пользователи всегда обладали всеми правами групп, в которые они были включены. Однако NOINHERIT по смыслу ближе к тому, что описано в стандарте SQL.
Примеры
Создание роли, для которой разрешён вход, но не задан пароль:
Создание роли с паролем:
Создание роли с паролем, действующим до конца 2004 г., то есть пароль перестаёт действовать в первую же секунду 2005 г.
Создание роли, которая может создавать базы данных и управлять ролями:
Совместимость
Оператор CREATE ROLE описан в стандарте SQL, но стандарт требует поддержки только следующего синтаксиса:
В стандарте SQL определяются концепции пользователей и ролей, но в нём они рассматриваются как отдельные сущности, а все команды создания пользователей считаются внутренней спецификой СУБД. В PostgreSQL мы решили объединить пользователей и роли в единую сущность, так что роли получили дополнительные атрибуты, не описанные в стандарте.
Sysadminium
База знаний системного администратора
Роли и атрибуты в PostgreSQL
В PostgreSQL пользователи и группы – это роли. Одна роль может быть членом другой роли. Роли в PostgreSQL не имеют связи с пользователями в операционной системе. Роли это глобальные объекты для всего кластера баз данных.
Псевдороль public неявно включает в себя все остальные роли. Её нельзя найти в списке ролей, но она существует.
У ролей есть несколько атрибутов. Эти атрибуты указываются при создании роли:
Создают роль следующим способом:
Если при создании роли не указать атрибуты, то роль получит запрещающие атрибуты (NOLOGIN, NOSUPERUSER) автоматом.
Для включения одной роли в другую нужно использовать команду GRANT:
А чтобы исключить роль из группы можем использовать следующую команду:
Право включать роли в другие роли имеют роли с одним из следующих атрибутов:
При включении в роль можно дать этой роли право управлять групповой ролью, то-есть включать в неё другие роли. Это делается с помощью опции WITH ADMIN OPTION при включении в роль:
Также существует возможность забрать права управления ролью следующим способом:
Владельцы объекта
Владелец объекта – это роль, которая этот объект создала, а также роли включённые в неё.
Владельца можно переназначить с помощью ALTER:
Практика
Создадим новую роль:
Переподключмся под пользователем alice:
В примере выше чтобы подключиться к той-же базе данных мы использовали знак “-“. Если бы нам нужно было подключиться к другой базе данных, то вместо “-” нужно указать имя базы данных.
Далее под ролью alice создадим другую роль bob и разрешим ему только подключаться к база данных:
Попробуем под bob создать другую роль:
Посмотрим, какие роли есть в кластере:
Из вывода мы видим:
Или тоже самое можно посмотреть в представлении pg_user из системного каталога:
Отнимем у Боба право подключаться к базам и проверим это:
Алиса у самой себя отберёт право создавать роли:
Подключимся под ролью postgres и включим Алису в группу postgres:
Раз мы включили Алису в группу Суперпользователя, будем заносить все её запросы в журнал. Вначале включим параметр log_min_duration_statement=0 для Алисы, затем сбросим его. И в конце концов назначим этот же параметр, но только когда Алиса подключена к базе postgres:
Хоть Алиса и входит в группу postgres, но просто так привилегиями она воспользоваться не сможет. Вначале Алисе нужно выполнить SET ROLE postgres:
Для того чтобы понять под кем мы работаем существует функция session_user. А чтобы понять какую роль использует текущее подключение нужно использовать current_user:
Вывод из предыдущего примера означает, что Алиса использует сейчас не свою роль, а роль postgres. Другими словами она выполнила SET ROLE postgres.
Вернемся к прежней роли:
Создадим под Алисой таблицу test и посмотрим, кто её владелец (предварительно удалим созданную ранее таблицу test):
Из вывода выше мы видим, что для таблицы test владелец (Owner) это Алиса.
Чтобы удалить роль, у неё не должно быть объектов которыми она владеет во всём кластере:
Из вывода выше мы видим, что Алиса владеет таблицей test и поэтому Алису нельзя удалить.
Можно все объекты одной роли передать другой роли с помощью REASSIGN OWNED. Например передадим все объекты Алисы к Бобу:
Здесь мы разобрали некоторый минимум. Если хотите знать больше, можете почитать о ролях в документации.
Использование ролей и управление доступом в PostgreSQL
PostgreSQL – это открытая система управления базами данных (СУБД), основанная на языке запросов SQL. PostgreSQL – очень производительный инструмент, предназначенный для систематизации и хранения данных приложения.
Данное руководство научит вас управлять правами доступа PostgreSQL.
Примечание: Руководство выполнено на облачном сервере Ubuntu 12.04, однако все инструкции, кроме раздела по установке, можно применить и в других современных дистрибутивах Linux.
Установка PostgreSQL
Если СУБД PostgreSQL не была установлена ранее, установите её сейчас. Для этого используйте команды:
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib
Во время установки PostgreSQL создает стандартного пользователя для работы. Перейдите в сессию этого пользователя.
Права доступа PostgreSQL
PostgreSQL управляет доступом при помощи так называемых ролей.
Роли похожи на обычные системные права доступа Unix, однако, в отличие от Unix, они не делятся на пользователей и группы.
Роли могут быть членами других ролей, что позволяет им наследовать параметры привилегий определённых ранее ролей.
Просмотр ролей
Чтобы просмотреть существующие роли PostgreSQL, нужно открыть командную строку СУБД:
а затем ввести команду:
\du
List of roles
Role name | Attributes | Member of
————+————————————————+————
postgres | Superuser, Create role, Create DB, Replication | <>
Как видите, после установки в PostgreSQL существует всего одна роль, которая обладает широкими правами доступа.
Создание ролей PostgreSQL
Существует два базовых способа создания ролей: в командной строке PostgreSQL и в командной строке системы.
Создание роли в PostgreSQL
Проще всего создавать новые роли в командной строке PostgreSQL.
Для этого используется следующий синтаксис:
CREATE ROLE new_role_name;
Попробуйте создать новую роль (в руководстве она условно называется demo_role):
CREATE ROLE demo_role;
CREATE ROLE
Проверьте список существующих ролей:
\du
List of roles
Role name | Attributes | Member of
————+————————————————+————
demo_role | Cannot login | <>
postgres | Superuser, Create role, Create DB, Replication | <>
Как видите, в списке появилась новая роль. Обратите внимание: на данный момент у неё нет привилегий входа.
Создание роли в командной строке системы
Также можно создать роль при помощи команды createuser.
Закройте командную строку PostgreSQL:
Чтобы создать роль в командной строке системы, введите следующую команду (в руководстве эта роль будет условно называться test_user):
createuser test_user
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
Команда задаст ряд вопросов, которые определят начальные привилегии данной роли.
Снова откройте командную строку Postgres и запросите список существующих ролей:
psql
\du
List of roles
Role name | Attributes | Member of
————+————————————————+————
demo_role | Cannot login | <>
postgres | Superuser, Create role, Create DB, Replication | <>
test_user | | <>
Как видите, роли, созданные разными методами, не идентичны. Роль, созданная в командной строке системы, имеет привилегии входа.
Удаление ролей PostgreSQL
Теперь попробуйте уровнять привилегии ролей demo_role и test_user. Это можно сделать во время создания роли (то есть нужно удалить и заново создать demo_role). Также можно просто отредактировать привилегии существующей роли.
Но прежде чем приступить к управлению привилегиями PostgreSQL, нужно научиться удалять роли.
Для этого используется следующий синтаксис:
DROP ROLE role_name;
Delete the «demo_role» role by typing:
DROP ROLE demo_role;
DROP ROLE
Если заданной в команде роли не существует, команда вернёт ошибку:
DROP ROLE demo_role;
ERROR: role «demo_role» does not exist
Оператор IF EXISTS позволяет избежать этой ошибки; команда с таким оператором удалит роль, если она существует. Если указанной роли нет, команда не вернёт ошибку.
DROP ROLE IF EXISTS role_name;
То есть, в любом случае команда будет выполнена успешно и не вернёт ошибку.
DROP ROLE IF EXISTS demo_role;
NOTICE: role «demo_role» does not exist, skipping
DROP ROLE
Определение привилегий во время создания роли
Теперь попробуйте снова создать роль demo_role, заранее установив её права доступа. Права роли можно указать сразу после главного оператора create.
Синтаксис выглядит так:
CREATE ROLE role_name WITH optional_permissions;
Полный список опций доступа можно просмотреть при помощи команды:
Чтобы у пользователя, связанного с этой ролью, были привилегии входа, введите:
CREATE ROLE demo_role WITH LOGIN;
CREATE ROLE
Проверьте список существующих ролей и обратите внимание на то, что теперь обе роли имеют одинаковые привилегии:
\du
List of roles
Role name | Attributes | Member of
————+————————————————+————
demo_role | | <>
postgres | Superuser, Create role, Create DB, Replication | <>
test_user | | <>
Чтобы роль имела права входа без аргумента login, используйте вместо CREATE ROLE такую команду:
CREATE USER role_name;
Команда CREATE USER отличается только тем, что автоматически даёт роли привилегии входа.
Управление правами роли PostgreSQL
Чтобы изменить права доступа уже существующей роли, используйте команду ALTER ROLE.
Её базовый синтаксис:
ALTER ROLE role_name WITH attribute_options;
Для примера попробуйте вернуть роли demo_role её исходные привилегии:
ALTER ROLE demo_role WITH NOLOGIN;
ALTER ROLE
Просмотрите список ролей:
\du
List of roles
Role name | Attributes | Member of
————+————————————————+————
demo_role | Cannot login | <>
postgres | Superuser, Create role, Create DB, Replication | <>
test_user | | <>
Теперь у роли demo_role нет привилегий входа.
Вернуть привилегии входа можно при помощи команды:
ALTER ROLE demo_role WITH LOGIN;
Смена пользователя PostgreSQL
По умолчанию пользователи могут входить только локально, если имя системного пользователя совпадает с именем роли PostgreSQL.
Чтобы изменить это поведение, можно изменить тип входа или настроить PostgreSQL для прослушивания локального интерфейса (это изменит тип подключения на удалённый).
Рассмотрим второй вариант.
Для начала нужно установить пароль для пользователя, в сессию которого нужно перейти.
Установите пароль для test_user:
Команда предложит ввести и подтвердить пароль. Затем закройте интерфейс PostgreSQL и вернитесь в сессию системного пользователя.
По умолчанию PostgreSQL подразумевает, что для входа будет использоваться роль, одноименная системному пользователю, и что такая роль будет подключаться к одноименной базе данных.
Но в данном случае это не так, потому нужно явно указать опции. Для этого используйте синтаксис:
Примечание: Вместо user_name укажите имя пользователя, при помощи которого нужно установить соединение; вместо database_name укажите имя БД, к которой нужно подключиться.
Чтобы открыть сессию пользователя test_user, введите:
Программа запросит установленный ранее пароль.
Примечание: Данная команда подключит пользователя к БД postgres, стандартной БД, созданной во время установки.
Попробуйте поработать в этой сессии; как видите, данный пользователь имеет довольно узкие привилегии.
Вернитесь в сессию администратора:
Управление привилегиями PostgreSQL
Как передать привилегии
Как правило, при создании БД или таблицы права доступа к ней есть только у создавшей её роли. Но такое поведение можно изменить.
Передавать права доступа другим ролям можно при помощи команды GRANT; её базовый синтаксис:
GRANT permission_type ON table_name TO role_name;
Для примера создайте таблицу:
CREATE TABLE demo (
name varchar(25),
id serial,
start_date date);
NOTICE: CREATE TABLE will create implicit sequence «demo_id_seq» for serial column «demo.id»
CREATE TABLE
\d
List of relations
Schema | Name | Type | Owner
———+————-+———-+———-
public | demo | table | postgres
public | demo_id_seq | sequence | postgres
(2 rows)
Теперь попробуйте передать некоторые права доступа к таблице demo роли demo_role (пусть это будет право на обновление, UPDATE).
GRANT UPDATE ON demo TO demo_role;
Чтобы передать полные права на таблицу, используйте оператор ALL:
GRANT ALL ON demo TO test_user;
Чтобы передать права доступа всем пользователям системы, вместо имени пользователя укажите PUBLIC:
GRANT INSERT ON demo TO PUBLIC;
Чтобы просмотреть назначенные привилегии доступа, введите:
\z
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
———+————-+———-+—————————-+—————————
public | demo | table | postgres=arwdDxt/postgres +|
| | | demo_role=w/postgres +|
| | | test_user=arwdDxt/postgres+|
| | | =a/postgres |
public | demo_id_seq | sequence | |
(2 rows)
Как отнять привилегии
Команда REVOKE отнимает привилегии.
REVOKE permission_type ON table_name FROM user_name;
Данная команда тоже может использовать операторы all и public.
REVOKE INSERT ON demo FROM PUBLIC;
Групповые роли PostgreSQL
PostgreSQL позволяет группировать роли, благодаря чему роли могут наследовать заранее установленные права доступа.
Для примера можно создать роль temporary_users и добавить в неё роли demo_role и test_user:
CREATE ROLE temporary_users;
GRANT temporary_users TO demo_role;
GRANT temporary_users TO test_user;
Теперь групповая роль temporary_users управлят привилегиями ролей demo_role и test_user.
Чтобы просмотреть сведения о принадлежности ролей можно с помощью команды:
\du
List of roles
Role name | Attributes | Member of
——————+————————————————+——————-
demo_role | |
postgres | Superuser, Create role, Create DB, Replication | <>
temporary_users | Cannot login | <>
test_user | |
Команда set role позволяет выбрать групповую роль, права которой нужно использовать.
Например, текущий пользователь postgres имеет права суперпользователя. Даже несмотря на то, что этот пользователь не является членом роли temporary_users, он может использовать её права:
SET ROLE temporary_users;
Теперь любая созданная таблица будет принадлежать групповой роли temporary_users.
CREATE TABLE hello (
name varchar(25),
id serial,
start_date date);
Просмотреть владельцев таблиц можно с помощью команды:
\d
List of relations
Schema | Name | Type | Owner
———+—————+———-+——————
public | demo | table | postgres
public | demo_id_seq | sequence | postgres
public | hello | table | temporary_users
public | hello_id_seq | sequence | temporary_users
(4 rows)
Как видите, новая таблица принадлежит роли temporary_users.
Чтобы вернуть оригинальные права текущей роли, введите:
Чтобы передать пользователю привилегии всех ролей, членом которых он является, используйте команду:
ALTER ROLE test_user INHERIT;
Чтобы удалить групповую роль (или любую роль), используйте:
DROP ROLE temporary_users;
ERROR: role «temporary_users» cannot be dropped because some objects depend on it
DETAIL: owner of table hello
owner of sequence hello_id_seq
Эта команда вернёт ошибку, потому что роли temporary_users принадлежит таблица. Сначала нужно передать права на таблицу другой роли:
ALTER TABLE hello OWNER TO demo_role;
Теперь роль таблица принадлежит роли demo_role.
\d
List of relations
Schema | Name | Type | Owner
———+—————+———-+————
public | demo | table | postgres
public | demo_id_seq | sequence | postgres
public | hello | table | demo_role
public | hello_id_seq | sequence | demo_role
(4 rows)
После этого роль temporary_users можно удалить:
DROP ROLE temporary_users;
Это удалит роль temporary_users; члены этой групповой роли не будут удалены.
Заключение
Теперь у вас есть базовые навыки работы с привилегиями PostgreSQL. Управление правами доступа – очень важный аспект работы с данными; это позволяет каждому приложению использовать только необходимые ему данные, не вмешиваясь в работу других приложений.
Superuser postgresql что это
CREATE ROLE — создать роль в базе данных
Синтаксис
Описание
Учтите, что роли определяются на уровне кластера баз данных, так что они действуют во всех базах в кластере.
Параметры
Имя создаваемой роли. SUPERUSER
NOSUPERUSER
Заметьте, что pg_dump по умолчанию отключает row_security (устанавливает значение OFF ), чтобы гарантированно было выгружено всё содержимое таблицы. Если пользователь, запускающий pg_dump, не будет иметь необходимых прав, он получит ошибку. Однако суперпользователи и владелец выгружаемой таблицы всегда обходят защиту RLS. CONNECTION LIMIT предел_подключений
Примечание
Если задана пустая строка, пароль также будет сброшен в NULL, но в PostgreSQL до версии 10 было не так. В ранних версиях пустая строка могла приниматься или нет, в зависимости от метода аутентификации и версии сервера, а libpq не давала использовать такой пароль в любом случае. Во избежание неоднозначности указывать пустую строку в качестве пароля не следует.
Пароль всегда хранится в системных каталогах в зашифрованном виде. Ключевое слово ENCRYPTED не имеет эффекта, но принимается для обратной совместимости. Метод шифрования определяется параметром конфигурации password_encryption. Если представленная строка пароля уже зашифрована с применением MD5 или SCRAM, она сохраняется как есть вне зависимости от значения password_encryption (так как система не может расшифровать переданный зашифрованной пароль, чтобы зашифровать его по другому алгоритму). Это позволяет пересохранять зашифрованные пароли при выгрузке/восстановлении. VALID UNTIL ‘ дата_время ‘
Предложение VALID UNTIL устанавливает дату и время, после которого пароль роли перестаёт действовать. Если это предложение отсутствует, срок действия пароля будет неограниченным. IN ROLE имя_роли
Предложение SYSID игнорируется, но принимается для обратной совместимости.
Замечания
Предложение VALID UNTIL определяет срок действия только пароля, но не роли как таковой. В частности, ограничение срока пароля не действует при входе пользователя без проверки подлинности по паролю.
Атрибут INHERIT устанавливается по умолчанию в целях обратной совместимости: в предыдущих выпусках PostgreSQL пользователи всегда обладали всеми правами групп, в которые они были включены. Однако NOINHERIT по смыслу ближе к тому, что описано в стандарте SQL.
Примеры
Создание роли, для которой разрешён вход, но не задан пароль:
Создание роли с паролем:
Создание роли с паролем, действующим до конца 2004 г., то есть пароль перестаёт действовать в первую же секунду 2005 г.
Создание роли, которая может создавать базы данных и управлять ролями:
Совместимость
Оператор CREATE ROLE описан в стандарте SQL, но стандарт требует поддержки только следующего синтаксиса:
В стандарте SQL определяются концепции пользователей и ролей, но в нём они рассматриваются как отдельные сущности, а все команды создания пользователей считаются внутренней спецификой СУБД. В PostgreSQL мы решили объединить пользователей и роли в единую сущность, так что роли получили дополнительные атрибуты, не описанные в стандарте.
PostgreSQL права пользователя
Для начала работы вам понадобится собственно PostgreSQL сервер и доступ в его консоль. Если вы хотите просто поработать в режиме лаборатории, то можно воспользоваться free tier (бесплатной пробной версией) в одном из клаудов, например AWS, или быстро поднять PostgreSQL на лэптопе, используя docker:
На заметку: в PostgreSQL права суперпользователя не ограничены, однако чтобы изменить суперпользователя или создать нового, вам самим нужно быть суперпользователем.
Чтобы в PostgreSQL создать роль введите в консоль
CREATE ROLE test WITH LOGIN PASSWORD ‘test’;
Давайте посмотрим, какие еще атрибуты мы можем передавать команде CREATE ROLE
