WTFM.INFO
Write The F* Manual — Заметки о сетях, администрировании и вообще
PostgreSQL посмотреть права пользователя на таблицы
SELECT table_catalog, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'username';
Запись опубликована 07.03.2019 автором xinferum в рубрике Базы данных с метками PostgreSQL, SQL.
Добавить комментарий Отменить ответ
Мета
Найти на сайте
TOP 5
- Barman сжатие WAL (106 528)
- Как получить chat id из канала telegram (19 307)
- PostgreSQL посмотреть права пользователя на таблицы (14 028)
- PostgreSQL выдача прав пользователю с учетом создаваемых в будущем объектов (10 497)
- PostgreSQL посмотреть текущие запросы к базам (9 793)
Рубрики
- DevOps (5)
- English (2)
- Python (2)
- Автоматизация (1)
- Ansible (1)
- Elasticsearch (5)
- Zabbix (4)
- Администрирование Linux (35)
- Администрирование Windows (19)
- Mikrotik (1)
Свежие записи
- Linux выполнить команду каждые 10 секунд, записать результат
- Работа с Mikrotik в Ansible
- Netbox — search for duplicate IP addresses. Python script.
- Netbox — поиск дубликатов IP адресов. Python скрипт.
- Простой мониторинг или NetWatch для FreeBSD/Linux (bash), уведомления в Telegram
Свежие комментарии
- xinferum к записи pgsentinel настройка (хранение истории активных сессий PostgreSQL)
- Sergey Shumeev к записи pgsentinel настройка (хранение истории активных сессий PostgreSQL)
- Антон к записи iSpy — уведомления в Telegram при обнаружении движения
- Аноним к записи Удаление checkpoint(snapshot) VM без опции «Удалить» через PowerShell
- mno к записи Двухфакторная аутентификация OpenVpn клиентов (OpenVpn + Google Authenticator) на CentOs 7/8
© WTFM.INFO (ex. shiningapples.net) 2017 — 2023
Все статьи, представленные на сайте, являются авторскими, если не указан источник. При цитировании материалов сайта, пожалуйста, указывайте ссылку на оригинальную статью. По всем вопросам обращайтесь на admin@wtfm.info
Ссылки Hostiman - наш хостинг BearScience - полезный it (и не только) блог
Авторизация в PostgreSQL. Часть 1 — Роли и Привилегии
Никто не будет спорить с тем, как важно понимать механизмы прав доступа и безопасности в базах данных. Если вы не продумываете логику авторизации в вашей БД, то, вероятно, вы не следуете принципу наименьших привилегий — к вашей базе данных могут получить доступ коллеги (например, разработчики, аналитики данных, маркетологи, бухгалтеры), подрядчики, процессы непрерывной интеграции или развернутые службы, которые имеют больше привилегий, чем должны. Это увеличивает риск утечек, неправомерного доступа к данным (например, личной информации), а также случайного или злонамеренного повреждения и потери данных.
Несмотря на важность темы, авторизация в базе данных являлась моим слабым местом в начале карьеры. NoSQL был самым крутым парнем на районе, а мир веб-разработки соблазняли фреймворки (например Rails), которые давали более приятный опыт разработки, нежели сложные SQL-скрипты. Но мир меняется. SQL и реляционные базы данных снова оказались в центре внимания, поэтому важно научиться пользоваться ими безопасно и эффективно. В этой серии статей я раскрою основные области авторизации в базах данных с акцентом на PostgreSQL, поскольку это одна из самых зрелых и функциональных СУБД с открытым исходным кодом. Мы рассмотрим следующие темы:
- Роли и Привилегии (в этой статье).
- Безопасность на уровне строк.
- Производительность безопасности на уровне строк.
Зачем нужна авторизация в PostgreSQL
Прежде чем углубиться в вопрос как, сделаем шаг назад и зададимся вопросом, зачем использовать авторизацию в PostgreSQL.
Для любого приложения или веб-сайта, где пользователи проходят аутентификацию и имеют доступ к различному контенту или могут выполнять отличные друг от друга действия, вам необходима авторизация. При использовании фреймворков, таких, как Rails и Django, документация и комьюнити, как правило, советуют следующее: использовать одного пользователя БД с правами суперпользователя (или правами произвольного чтения/записи). Затем авторизация реализуется в виде логики и правил в кодовой базе Rails/Django. Если вы добавляете смежные службы, которые работают с одними и теми же данными (например, очереди, background workers, cronjobs, хранилища данных), вам может потребоваться дублировать некоторую логику авторизации в этих службах, или эти службы могут использовать логику авторизации через разделяемые библиотеки или прямое включение в их код (что усложняет разработку, развертывание, продумывание архитектуры и безопасности). Кроме того, если различные службы получают доступ к БД через учетную запись суперпользователя, поле для атак и ошибок, повреждающих данные, значительно шире.
Реализация авторизации в PostgreSQL позволяет определять правила доступа с одном месте (в базе данных), и эти правила буду последовательно применяться ко всем службам и приложениям, которые обращаются к данным в БД. Изучение и использование инструментов авторизации в базе данных естественным образом побуждает использовать отдельные роли с минимальными привилегиями, что ограничивает масштабы и серьезность атак и ошибок.
Еще одно преимущество использования PostgreSQL для авторизации: это мощный, хорошо протестированный инструмент, которые вы уже используете. Вам не нужно самостоятельно внедрять механизмы авторизации (подверженные ошибкам и отнимающие много времени) или заморачиваться с аудитом, интеграцией и обновлениями сторонних библиотек. Все эти проблемы справедливы и для PostgreSQL, однако PostgreSQL превосходит любую библиотеку авторизации с точки зрения стабильности, поддержки и безопасности.
Несмотря на приведенные выше аргументы, использование авторизации PostgreSQL подходит не для всех и каждого проекта! Если у вас простое приложение с ограниченной областью действия или у вас аллергия на SQL или его (относительно примитивный) инструментарий, реализовать авторизацию в одном месте на удобном для вас языке, скорее всего, будет быстрее и проще. Если вам нужна авторизация, которая охватывает множество источников данных и сервисов, вам может понадобиться что-то вроде Zanzibar. Создание авторизации с помощью PostgreSQL — это не панацея, но подход, который стоит рассмотреть в контексте вашего конкретного проекта и команды.
Тестовая схема
Теперь, когда мы понимаем, почему мы можем захотеть использовать PostgreSQL для авторизации, рассмотрим, как нам это реализовать. В качестве примера возьмем приложение, похожие на Bandcamp, где музыкальные исполнители могут публиковать альбомы, а фанаты — находить исполнителей и следить за ними. Возьмем схему «музыкальные исполнители и альбомы» из предыдущей статьи о загрузке текстовых данных в PostgreSQL и немного скорректируем ее, добавив другой тип пользователя (фанаты) и удалив таблицы с «жанрами», чтобы сохранить схему простой и доступной.
Пример схемы с музыкальным исполнителям, альбомам и подписчикам (фанатам).
Как написано в README репозитория, вы можете выполнить приведенную ниже команду, которая использует официальный образ Postgres Docker для локального запуска базы данных PostgreSQL. При первом подключении тома будет загружен файл schema.sql , который заполнит вашу базу данных таблицами, показанными на диаграмме выше.
docker run --name=postgres \ --rm \ --volume=$(pwd)/schema.sql:/docker-entrypoint-initdb.d/schema.sql \ --volume=$(pwd):/repo \ --env=PSQLRC=/repo/.psqlrc \ --env=POSTGRES_PASSWORD=foo \ postgres:latest -c log_statement=allЧтобы открыть консоль psql в контейнере, запустите следующую команду в другом терминале:
docker exec --interactive --tty postgres \ psql --username=postgresРоли
Первый уровень любого проекта авторизации в PostgreSQL — это роли. Роли базы данных могут представлять пользователей и/или группы. Поначалу в PostgreSQL обычно одна роль суперпользователя с именем «postgres».
Давайте запустим обе docker -команды из предыдущей части в отдельных окнах терминала, чтобы запустить базу данных PostgreSQL в контейнере и подключиться к ней с помощью psql. Обратите внимание, что в команде «docker exec» мы подключаемся с юзернеймом «postgres». Чтобы посмотреть роли, существующие в бд, запустите \du :
-- SQL comments (like this one) start with 2 hyphens (--). -- I'll represent the psql prompt as -- =# (when the current role is a superuser, i.e. «postgres») or -- => (when the current role is not a superuser). -- This corresponds to a psql PROMPT1 setting of '%R%# '. For more info about -- psql prompts, see https://www.postgresql.org/docs/current/app-psql.html. =# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | <>Увидеть роль, которую мы используем:
=# SELECT current_user, session_user; current_user | session_user --------------+-------------- postgres | postgresКогда вы тестируете привилегии и политики, вы, вероятно, будете часто менять роли. К сожалению, насколько я знаю, в приглашении командной строки psql нельзя настроить показ «current_user» (только «session_user»). Вы можете упростить выполнение приведенного выше запроса, установив псевдоним в файле .psqlrc , например \set whoami ‘SELECT current_user, session_user;’ . Затем вам нужно всего лишь ввести :whoami в psql, чтобы выполнить запрос. Спасибо этому ответу с StackOverflow от wjv за идею. Дополнительные советы по psqlrc можно найти в этой статье.
«session_user» обычно будет пользователем, от имени которого вы подключились к базе данных (хотя его можно изменить, установив SET SESSION AUTHORIZATION ). «current_user» — это пользователь, от имени которого вы действуете, — это пользователь, который будет проверяться при оценке привилегий и политик. current_user изменяется с помощью SET ROLE и RESET ROLE (или запуска функций SECURITY DEFINER , но об этом в другой раз).
Давайте создадим роли «artist» и «fan» для нашего примера и попрактикуемся в смене ролей:
=# CREATE ROLE fan LOGIN; =# CREATE ROLE artist LOGIN; -- Running \du will now show 3 users =# SET ROLE fan; => SELECT current_user, session_user; current_user | session_user --------------+-------------- fan | postgres => RESET ROLE; =# SELECT current_user, session_user; current_user | session_user --------------+-------------- postgres | postgres -- We can run the psql connect command (\c) to connect as a different user =# \c — artist You are now connected to database «postgres» as user «artist». => SELECT current_user, session_user; current_user | session_user --------------+-------------- artist | artist => SET ROLE fan; ERROR: 42501: permission denied to set role «fan»Из приведенного выше видно, что не суперпользователи («artist») не могут изменять свою роль. Исключением является случай, когда роль предоставляется другой роли, и именно так роли в БД могут действовать как группы — роли «пользователя» может быть предоставлена роль «группы», тем самым пользователь получает привилегии группы. Мы не будем углубляться в использование ролей в качестве групп, вся необходимая информация есть в документации PostgreSQL.
Из-под роли «artist» посмотрим, какие существуют исполнители:
=> SET ROLE artist; => SELECT * from artists; ERROR: 42501: permission denied for table artistsХотя у нас роль «исполнитель», мы еще ничего не сделали, чтобы предоставить нашей роли доступ к таблице исполнителей в базе данных. Для этого нужны…
Привилегии
Права доступа на работу с объектами базы данных контролируются привилегиями, управляемыми с помощью команд GRANT и REVOKE . Мы можем проверить привилегии в psql с помощью команды \dp :
=> \dp Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+-----------------------+----------+-------------------+-------------------+---------- public | albums | table | | | public | albums_album_id_seq | sequence | | | public | artists | table | | | public | artists_artist_id_seq | sequence | | | public | fan_follows | table | | | public | fans | table | | | public | fans_fan_id_seq | sequence | | |В столбцах привилегий ничего нет, поэтому над этими объектами базы данных (таблицами и последовательностями) не допускаются никакие действия, если они не выполняются суперпользователем или владельцем объекта базы данных. Перечисляя таблицы с помощью \dt , мы видим, что «postgres» является владельцем всех таблиц:
=> \dt List of relations Schema | Name | Type | Owner --------+-------------+-------+---------- public | albums | table | postgres public | artists | table | postgres public | fan_follows | table | postgres public | fans | table | postgresДавайте разрешим роли «artist» делать выборку из таблицы с исполнителями, а затем снова проверим привилегии:
-- Reconnect as the postgres superuser => \c — postgres You are now connected to database «postgres» as user «postgres». =# GRANT SELECT ON artists TO artist; =# SET ROLE artist; => SELECT * FROM artists; artist_id | name -----------+------ (0 rows) => \dp Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+-----------------------+----------+---------------------------+-------------------+---------- public | albums | table | | | public | albums_album_id_seq | sequence | | | public | artists | table | postgres=arwdDxt/postgres+| | | | | artist=r/postgres | | public | artists_artist_id_seq | sequence | | | public | fan_follows | table | | | public | fans | table | | | public | fans_fan_id_seq | sequence | | | (7 rows)Теперь мы видим некоторые привилегии. Привилегия select (read), которую мы предоставили роли «artist» из роли «postgres», появляется вместе с полным набором возможных привилегий, которые имплицитно предоставляются владельцу таблицы (postgres). Что означают все эти буквы? В документации PostgreSQL есть наглядная таблица:
Первые 7 строк — это привилегии, применимые к объектам базы данных «табличного» типа.
Давайте продолжим добавлять остальные привилегии в нашем примере. На естественном языке эти привилегии звучат так:
- Мы хотим, чтобы фанаты могли видеть свои данные и удалять свою учетную запись.
- Мы хотим, чтобы фанаты могли видеть, на каких артистов они подписаны, а также подписываться на артистов и отписываться от них.
- Мы хотим, чтобы фанаты могли видеть исполнителей и альбомы.
- Мы хотим, чтобы исполнители могли видеть свои собственные данные и редактировать свое имя.
- Мы хотим, чтобы исполнители могли создавать, редактировать и удалять альбомы.
Теперь то же самое на SQL:
=> RESET ROLE; =# GRANT SELECT, DELETE ON fans to fan; =# GRANT SELECT, INSERT, DELETE ON fan_follows TO fan; =# GRANT SELECT ON artists TO fan; =# GRANT SELECT ON albums TO fan; =# GRANT SELECT, UPDATE (name), DELETE ON artists to artist; =# GRANT SELECT, INSERT, UPDATE (title, released), DELETE ON albums to artist; -- I add the *s pattern to only match database objects with names ending in s, -- so it'll show our tables (which have plural names) and hide the sequence -- database objects that appeared in the output last time we ran \dp. =# \dp *s Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+-------------+-------+---------------------------+---------------------+---------- public | albums | table | postgres=arwdDxt/postgres+| title: +| | | | fan=r/postgres +| artist=w/postgres+| | | | artist=ard/postgres | released: +| | | | | artist=w/postgres | public | artists | table | postgres=arwdDxt/postgres+| name: +| | | | artist=rd/postgres +| artist=w/postgres | | | | fan=r/postgres | | public | fan_follows | table | postgres=arwdDxt/postgres+| | | | | fan=ard/postgres | | public | fans | table | postgres=arwdDxt/postgres+| | | | | fan=rd/postgres | | (4 rows)Вы можете заметить, что мы не предоставляем права на обновление столбцов с ID. Если бы вместо этого мы разрешили пользователям приложения редактировать идентификаторы, то они могли бы делать то, что нам не нужно, например изменять идентификатор строки или изменять отношения между строками (например, исполнитель мог бы назначить созданный им альбом другому исполнителю). Используя привилегии для конкретных столбцов, мы можем гарантировать, что пользователи смогут изменять только разрешенные нами значения. Еще один способ защиты от изменения пользователями идентификаторов, используемых в отношениях (внешних ключах), — это политики безопасности на уровне строк, которые мы рассмотрим в следующей статье.
Мы также могли бы опустить привилегии выбора в столбцах идентификаторов, которые вы, возможно, захотите сделать, чтобы скрыть внутреннюю информацию, такую как суррогатные ключи или бизнес-информацию, например скорость, с которой исполнители создают альбомы на вашей платформе (если вы используете автоинкрементные целочисленные идентификаторы). Однако, если пользователям недоступны идентификаторы, они не могут объединять таблицы (придется делать отдельные представления для объединений, которые вы хотите предоставить пользователям) и делать SELECT * (им нужно явно указывать столбцы для выборки).
Теперь, когда мы установили некоторые привилегии, давайте добавим данные и проверим, правильно ли все работает.
- First, we insert one fan and 3 artists as the postgres superuser =# INSERT INTO fans DEFAULT VALUES; =# INSERT INTO artists (name) VALUES ('DJ Okawari'), ('Steely Dan'), ('Missy Elliott'); -- We change role to «fan» and follow some artists (DJ Okawari and Steely Dan) =# SET ROLE fan; => INSERT INTO fan_follows (fan_id, artist_id) VALUES (1, 1), (1, 2); -- We unfollow DJ Okawari => DELETE FROM fan_follows WHERE artist_id = 1; -- Let's list what artists we're still following => SELECT * FROM fans INNER JOIN fan_follows USING (fan_id) INNER JOIN artists USING (artist_id); artist_id | fan_id | name -----------+--------+------------ 2 | 1 | Steely Dan -- Try to change an artist's name, which doesn't work from the «fan» role => UPDATE artists SET name = 'TWRP' WHERE artist_id = 2; ERROR: 42501: permission denied for table artists -- Change roles to «artist» and change an artist's name => SET ROLE artist; => UPDATE artists SET name = 'TWRP' WHERE artist_id = 2; -- Add a new album => INSERT INTO albums (artist_id, title, released) VALUES (3, 'Under Construction', '2002-11-12'); -- Try to assign the album to a different artist, which doesn't work => UPDATE albums SET artist_id = 2; ERROR: 42501: permission denied for table albums => DELETE FROM artists; -- Deleting all artists in the database executes without error! *gulp*
Наши привилегии в целом выглядят неплохо… за исключением того, что пользователь, вошедший в систему как «artist», может удалить всех исполнителей из базы данных! Мы бы предпочли, чтобы артисты могли удалять только свои собственные учетные записи, но такая логика авторизации не выражается с точки зрения привилегий, GRANT и REVOKE . Это связано с тем, что решение об авторизации («должно ли быть разрешено это действие?») зависит от значений в конкретной строке базы данных. Как вы уже догадались, нам нужны политики безопасности на уровне строк для принятия детальных решений о том, какие строки в базе данных могут обрабатываться конкретными пользователями. Мы рассмотрим это в следующей статье.
- postgresql
- авторизация
- привилегии
- безопасность
- администрирование баз данных
- Блог компании Timeweb Cloud
- Системное администрирование
- PostgreSQL
- Администрирование баз данных
Как узнать права пользователя в Postgres-е?
Допустим, есть некий пользователь (роль) в Postgres-е с именем vasya. Хочется достоверно узнать к каким базам данных / таблицам у этого Васи есть доступ, чтобы он не увидел случайно чего-нибудь лишнего.
Смешно, но самый простой способ это сделать — попытаться дропнуть этого пользователя. Обернув в транзакцию, разумеется. Лишние приключения нам ни к чему.
Нравится мне этот движок СУБД. Чем дольше с ним работаю, тем больше нравится. 😀
Управление правами пользователей
На работающем сервере все пользователи делятся как минимум на две группы: администраторы и конечные пользователи. Причем администраторы могут делать все (являются суперпользователями), а конечные пользователи могут совсем немного: как правило, изменять данные в нескольких таблицах и читать еще несколько таблиц.
Не очень разумно давать простым пользователям право создавать или изменять определения объектов БД, а значит, они не должны иметь права create для всех схем, включая public.
Для конечных пользователей существуют и другие роли. Аналитики, например, могут только делать выборку данных из одной таблицы или представления или выполнять несколько функций. Менеджер уполномочен только давать или отнимать права.
Для того чтобы забрать у пользователя права доступа к таблице, текущий пользователь должен быть суперпользователем, владельцем таблицы или иметь доступ grant для этой таблицы. Вы можете отнять права и у пользователя, который является суперпользователем.
Чтобы отнять все права на таблицу mysecrettabie у пользователя userwhoshouldnotseeit, необходимо выполнить следующую SQL- команду:
REVOKE ALL ON mysecrettabie FROM userwhoshouldnotseeit;
Однако таблица все еще остается открытой для пользователей через роль PUBLIC, поэтому следует также записать:
REVOKE ALL ON mysecrettabie FROM PUBLIC;
По умолчанию у всех пользователей есть права (SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES и TRIGGER) на все вновь созданные таблицы посредством специальной роли PUBLIC. Чтобы определенный пользователь больше не мог получить доступ к таблице, права на нее необходимо отнять и у этого пользователя, и у роли PUBLIC.
На рабочих системах лучше всего всегда включать выражения GRANT и REVOKE в скрипт создания БД, и тогда вы будете уверены, что доступ к этой базе получат только нужные пользователи. Если делать это вручную, можно что-нибудь забыть. Кроме того, таким образом вы удостоверитесь, что одни и те же роли используются для разработки и тестирования окружения, так что на стадии развертывания с этой стороны неожиданностей не будет.
Вот пример части скрипта для создания базы данных:
CREATE TABLE table 1 (.
REVOKE ALL ON table 1 FROM GROUP PUBLIC;
GRANT SELECT ON table 1 TO GROUP webreaders;
GRANT SELECT, INSERT, UPDATE, DELETE ON table 1 TO editors;
GRANT ALL ON tablet TO admins;
При отъеме или назначении прав рекомендуется использовать полное имя, иначе может неожиданно оказаться, что вы работали не с той таблицей.
Чтобы посмотреть эффективный путь БД, выполните:
pguser=# show search_path;
«$user» .public (1 row)
Чтобы увидеть, на какую таблицу будет оказано влияние, если вы опустите имя схемы, запустите в psql: pguser=# d х Table “public.x»
Column | Type | Modifiers
В результате будет получено полное имя таблицы public.x, включая схему.
Чтобы выполнять какие-либо действия над таблицей, пользователь должен иметь права доступа к ней. По умолчанию PostgreSQL дает всем полные права посредством роли PUBLIC, но, если настройки были изменены для усиления безопасности, при создании таблицы права на нее у роли PUBLIC могут быть отозваны.
Указанные ниже команды сначала дают роли полный доступ к схеме, права на просмотр (SELECT) и изменение (INSERT, UPDATE, DELETE), а затем назначают эту роль двум пользователям БД:
GRANT ALL ON someschema TO somerole;
GRANT SELECT, INSERT, UPDATE, DELETE ON someschema. sometable TO somegroup;
GRANT somerole TO someuser, otheruser;
В PostgreSQL нет требования одних прав для получения других: у пользователя могут быть таблицы только для записи, куда нужно вставлять данные, но нельзя их просматривать. Администратор обычно создает по этому принципу очередь сообщений: сотрудники отправляют данные одному из менеджеров, но не видят, что отправили другие.
С другой стороны, вы не сможете изменить или удалить сделанную вами запись. Это необходимо для аудита таблиц журнала, в котором фиксируются все изменения и который нельзя модифицировать.
Также нужен доступ к схеме: чтобы получить доступ к таблице, пользователь должен иметь доступ к схеме, содержащей эту таблицу: GRANT USAGE ON SCHEMA someschema TO someuser;
Часто приходится давать группам пользователей схожие права на какие-либо объекты БД. Для этого сначала необходимо предоставить все права промежуточной роли (или разрешенной группе), а затем принять в эту группу избранных пользователей:
CREATE GROUP webreaders;
GRANT SELECT ON pages TO webreaders;
GRANT INSERT ON viewlog TO webreaders;
GRANT webreaders TO tim, bob;
Теперь и tim, и bob имеют права select на таблицу pages и insert на таблицу viewlog. Можно также добавлять права роли уже после принятия в нее пользователей. Таким образом, после
GRANT INSERT, UPDATE, DELETE ON comments TO webreaders; у пользователей bob и tim появятся все эти права на таблицу comments.
В более ранних версиях PostgreSQL не было простого способа предоставить права для работы с двумя или несколькими объектами, кроме перечисления их в команде grant и revoke.
В версии 9.0 команды grant или revoke можно распространять на все однотипные объекты схемы:
GRANT SELECT ON ALL TABLES IN SCHEMA staging TO bob;
Но все еще нужно дополнительно отдельной командой давать права доступа к схеме.
Чтобы создавать новых пользователей, вы должны быть суперпользователем или иметь права createrole или createuser.
В командной строке запустите команду createuser и ответьте на несколько вопросов:
pguser@vhost:~$ createuser bob
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) у
Shall the new role be allowed to create more new roles? (y/n) n pguser@vhost:-$ createuser tim
Shall the new role be a superuser? (y/n) у
Программа createuser — это просто обертка для выполнения команд SQL над кластером БД. Она подключается к базе данных postgres, задает вопрос, а затем выполняет команды SQL для создания пользователя. То же самое можно сделать, запустив SQL-команду CREATE USER:
CREATE ROLE bob WITH NOSUPERUSER INHERIT NOCREATEROLE CREATEDB LOGIN;
CREATE ROLE tim WITH SUPERUSER;
Проверка ролей пользователя: pguser=# du tim
List of roles Role name | Attributes | Member of Tim | Superuser | <>
Начиная с версий 8.x, команды CREATE USER и CREATE GROUP фактически являются вариациями команды create role.
Выражение CREATE USER u; эквивалентно CREATE ROLE u LOGIN; а выражение CREATE GROUP g; равносильно CREATE ROLE g NOLOGIN;
Иногда необходимо временно отозвать у пользователя права на подключение, при этом не удаляя пользователя и не меняя его пароль.
Чтобы менять права доступа пользователей, вы должны быть суперпользователем или иметь право revoke (в этом случае вы не сможете менять права суперпользователей).
Временно запретить пользователю вход в систему можно так: pguser=# alter user bob nologin;
Чтобы вернуть пользователю возможность устанавливать соединение, выполните
pguser=# alter user bob login;
В системном каталоге PostgreSQL ставится флажок, запрещающий пользователю вход в систему. При этом текущие соединения не разрываются.
Есть и другой способ не дать пользователю войти в систему. Вы можете установить число возможных соединений для этого пользователя (connection limit) равным 0:
pguser=# alter user bob connection limit 0;
Если вы хотите разрешить пользователю bob устанавливать до 10 соединений одновременно, выполните:
pguser=# alter user bob connection limit 10;
Кроме того, можно полностью снять ограничение числа возможных соединений:
pguser=# alter user bob connection limit -1;
Если вы отозвали права на установление соединений у пользователей и хотите сейчас же отключить их, выполните следующее (для этого у вас должны быть права суперпользователя):
FROM from pg_stat_activity a
JOIN pg roles r ON a.usename = r.rolname AND not rolcanlogin; В ранних версиях PostgreSQL, где нет функции pg_terminate_ backend (), вы можете в командной строке на сервере набрать в качестве пользователя postgres:
postgres@vhost: ~$ psql -t -с «
select ‘kill’ || procpid from pg_stat_activity a
join pg roles r on a.usename = r.rolname and not rolcanlogin;»
и получить тот же результат.
В этом случае из запроса формируются команды kill, которые направляются затем оболочке для выполнения.
При попытке сбросить пользователя, работающего с таблицами или другими объектами БД, вы получите следующее сообщение об ошибке: testdb=# drop user bob;
ERROR: role «bob» cannot be dropped because some objects depend on it DETAIL: owner of table bobstable owner of sequence bobstable id seq
Проще всего не сбрасывать пользователя, а запретить пользователю устанавливать соединения: pguser=# alter user bob nologin;
Еще один плюс состоит в том, что при последующем аудите и тестировании вы будете знать, кто создал таблицу или откуда она взялась.
Если вам действительно нужно избавиться от пользователя, вы должны передать все, чем он владел, другому пользователю, так что выполните следующий запрос, который является дополнением PostgreSQL к стандарту SQL:
REASSIGN OWNED BY bob TO bobsreplacement;
При этом владение всеми объектами БД, которые принадлежали роли bob, будет передано роли bobs_replacement.
Однако для этого у вас должны быть права изменения обеих ролей, и необходимо повторить этот запрос во всех базах данных, где bob владеет какими-либо объектами, поскольку REASSIGN OWNED работает только в текущей базе данных.
Команда REASSIGN OWNED была добавлена в PostgreSQL в версии 8.2. Если ваша БД имеет более раннюю версию, вам придется поработать в командной строке Unix.
Для начала извлеките из дампа схемы назначения прав: dbuser:~$ pg dump -s mydatabase I grep -i «alter.* owner to bob» ALTER FUNCTION public.somefunction() OWNER TO bob;
ALTER TABLE public.directory OWNER TO bob;
ALTER TABLE public.directory_seq OWNER TO bob;
ALTER TABLE public.document_id_seq OWNER TO bob;
ALTER TABLE public.documents OWNER TO bob;
Затем просто замените в полученном результате bob на нового пользователя и передайте команды обратно базе данных: