PostgreSQL права пользователя

Для начала работы вам понадобится собственно PostgreSQL сервер и доступ в его консоль. Если вы хотите просто поработать в режиме лаборатории, то можно воспользоваться free tier (бесплатной пробной версией) в одном из клаудов, например AWS, или быстро поднять PostgreSQL на лэптопе, используя docker:

docker run -d --name postgres postgres
docker exec -ti postgres psql -U postgres

Пользователь "postgres" - пользователь PostgreSQL по-умолчанию. Он также является суперпользователем. При подключении через сокет обычно пароль не требуется. Подключение по TCP/IP однако может сопровождаться запросом пароля, но в большинстве установок PostgreSQL пароль postgres по-умолчанию "postgres".

На заметку: в PostgreSQL права суперпользователя не ограничены, однако чтобы изменить суперпользователя или создать нового, вам самим нужно быть суперпользователем.

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

Чтобы в PostgreSQL создать роль введите в консоль

CREATE ROLE test WITH LOGIN PASSWORD 'test';

Данная команда позволяет в PostgreSQL создать роль с именем "test" и паролем "test" и, внимание - мы уже начали работать с правами, выдать ей право подключаться с серверу баз данных. Очень важно - заострите внимание на слове "сервер баз данных", так как эта привилегия регулирует именно возможность подключения к серверу, но не к базе. Да, в отличие от того же MySQL, PostgreSQL разделяет эти два понятия. Т.е. если у вас есть право подключаться к серверу баз данных (PostgreSQL), это еще далеко не факт, что у вас есть право также подключаться к базам, хранящимся на этом сервере. Если LOGIN не указать, то по-умолчанию свежие роли такой привилегии не получают и не могут подключаться к серверу баз данных. А зачем нам вообще может понадобиться пользователь без права подключения к серверу баз данных? Например, снимая этот атрибут, мы можем временно блокировать пользователей, или использовать такие роли в качетве группы, т.е. выдавая права на группу, мы обычно не хотим, чтобы кто-то мог подключаться от имени самой группы.

Давайте посмотрим, какие еще атрибуты мы можем передавать команде CREATE ROLE

И так у нас есть роль "test", а это значит, что время создать базу и дать нашей роли право чтения или записи в эту базу. Как было отмечено выше, мы можем дать право самой роли создавать базы, или создать ее от имени суперпользователя. Выбор за вами, если вы указали при создании роли атрибут CREATEDB, то можете смело переподключаться, используя роль "test". Если нет, то тут два пути: продолжить от имени суперпользователя или дать роли право создания баз. Сделать это можно командой ALTER ROLE как показано ниже

ALTER ROLE test CREATEDB;

Проверим наш сетап. В PostgreSQL список ролей можно получить командой '\du'

postgres=# \du
               List of roles
 Role name | Attributes | Member of
-----------+------------+-----------
 test      | Create DB  | {}
...

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

CREATE DATABASE test WITH ENCODING UTF8;

База создана! Как и в случае с созданием ролей, при создании базы тоже можно указать ряд атрибутов. Давайте рассмотрим наиболее интересные с точки зрения прав доступа

OWNER

TEMPLATE

CONNECTION LIMIT

Начнем с OWNER. Мы тем самым плавно подошли к концепции владения. В PostgreSQL действует правило: тот, кто объект создает, будь-то база, схема, таблица или что-либо еще, получает неограниченные права на этот оъект. Т.е., если вы создавали базы от имени суперпользователя, то он будет владеть этой базой и всеми правами на нее, если от имени роли, то роль. И никакие другие непривелегированные роли к этим объектам доступ иметь не будут.

Казалось бы логично, но нет. В PostgreSQL есть такая штука как PUBLIC - неявная псевдо роль, неявными членами которой является все роли. И у нее есть некоторые права по-умолчанию, которые некоторых заставляют чесаться. К примеру, создали вы базу, и даже не важно от имени суперпользоателя или непривелегированной роли, тут кто-то другой логинится и начинает совершенно спокойно создавать в вашей базе таблицы, и PostreSLQ даже не заикнется, что возможно это не то, что вы имели ввиду. Почему так происходит и как это лечить? Давайте разбираться

Начать пожалуй стоит с концепции наймспэйсинга

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

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

Каждая база обязана иметь хотя бы одну схему, и она создается по-умолчанию при создании базы и носит название PUBLIC. Отсюда и ноги растут у проблем с чрезмерно разрешительными, как некоторым кажется, правами доступа. Но вот уже однажды создав таблицу даже в схеме PUBLIC, скажем от имени непривелегированной роли, уже никакие другие роли доступ к данным, хранящимся внутри этой таблицы, не получат. Однако они все еще смогут вывести список ваших таблиц.

Как решить эту проблему, если вас не устраивает такое положение дел? Что ж, решать-то надо на самом деле две проблемы. Во-первых, отнять у PUBLIC возможность поключаться к базе данных:

REVOKE ALL PRIVILEGES ON DATABASE test FROM PUBLIC;
GRANT CONNECT ON DATABASE test to test;

Данный сет комманд снимает все привилегии с PUBLIC относительно базы данных "test", а их мы напомним всего 4: CREATE, CONNECT, TEMPORARY и TEMP, и выдает роли "test" возможность только подключаться к базе данных

Вариантов же решения проблемы со схемой ровно два: создать схему отличную от PUBLIC. Там уже никакие настройки по-умолчанию до вас не доберутся. Вы создали схему - вы ее владелец. Всем остальным для доступа к схеме понадобится явное разрешение вас или суперпользователя. Или наоборот, как и в случае с базой, отнять у PUBLIC доступ собственно к PUBLIC схеме. Сделать это можно командой

REVOKE ALL PRIVILEGES ON SCHEMA public FROM PUBLIC;

После такого никто не то что создавать таблицы - просмотреть список таблиц уже не сможет. Если вы все же хотите оставить возможность просмотра списка таблиц, то такую привилегию надо теперь явно выдать какой-то конкретной роли или всем (PUBLIC)

GRANT USAGE ON SCHEMA public TO PUBLIC

Давайте подытожим знания о правах по-умолчаю PUBLIC (заметьте, мы специально не называем это ролью) в таблице

Идем дальше. А дальше - хуже. Мы разобрались с сервером баз данных, базами и схемами. А как же элементы базы данных, такие как таблицы, вьюзы, последовательности? Тут тоже есть, что понастраивать, особенно если вам нужно в PostgreSQL создать пользователя и дать права только на чтение (readonly) для, например, выполнения дампа или гранта команде анализа лимитированного доступа к данным. Соответственно последовательность операций будет такая:

1. Создаем новую роль
2. Выдаем роли возможность подключаться к базе
3. Даем возможность роли читать схему public, или другую, если вы предпочитаете не работать со схемой public
4. Даем возможность читать таблицы в указанной схеме

Предположим, вы работаете в схеме public, то в командах PostgreSQL это будет выглядеть следующим образом

CREATE ROLE ro_user WITH LOGIN NOINHERIT PASSWORD 'changeme';
GRANT CONNECT ON DATABASE test TO ro_user;
GRANT USAGE ON SCHEMA public TO ro_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ro_user;

И вот, казалось бы момент счастья, но PostgreSQL и здесь свинью подложил. Если после такой операции вы создали новую таблицу, то внимание, ваш ro_user не будет иметь к ней доступа! Решить проблему можно выполнив команды выше еще раз, или же поработать над такой штукой, как привилегии по-умолчанию. Соответственно, чтобы ro_user мог радостно читать данные даже из вновь созданных таблиц, вам следует выполнить

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ro_user;

Если структура базы чуть более сложная, чем обычно, возможно вам понадобится дать права также на последовательности и процедуры

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE,SELECT ON SEQUENCES TO ro_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON ROUTINES TO ro_user;