Управление пользователями и группами

В PostgreSQL, как ив большинстве СУБД, важная роль отведена пользователям и группам. Правильное применение механизма пользователей ц групп обеспечивает гибкий, хорошо настраиваемый механизм контроля доступа к объектам баз данных.

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

Учетные записи пользователей определяют, кому разрешено выполнение определенных действий в системе (а также каких именно действий). Группы существуют как вспомогательное средство, упрощающее предоставление этих прав. Пользователи и группы существуют как глобальные объекты баз данных, а это означает, что они не связаны ни с какой конкретной базой данных.

Данная глава посвящена основам управления и практического применения пользователей и групп PostgreSQL.

Управление пользователями

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

Каждому пользователю PostgreSQL назначается внутренний системный идентификатор (sysid) и пароль, хотя пароль не всегда обязателен для подключения (это зависит от файла конфигурации pg_hba.conf, о котором говорилось в главе 8). Системный идентификатор связывает объекты базы данных с владельцем (так называется пользователь, которому разрешено предоставлять и отзывать права доступа к объекту).

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

В PostgreSQL по умолчанию создается один суперпользователь с именем postgres. Все остальные пользователи создаются этим суперпользователем или другим, созданным позднее.

Просмотр списка пользователей

Вся информация о пользователях PostgreSQL хранится в системной таблице pg_shadow, структуру которой иллюстрирует табл. 10.1. Выборка из этой таблицы разрешена только суперпользователям, хотя ограниченное представление этой таблицы с именем pg_user доступно и для обычных пользователей.

Таблица 10.1. Структура таблицы pg_shadow

Поле	Тип
usename	name
usesysid	integer
usecreatedb	boolean
usetrace	boolean
usesuper	boolean
usecatupd	boolean
passwd	text
valuntil	abstime

Основное различие между данными, содержащимися в таблицах pg_user и pg_shadow, заключается в том, что в pg_user отсутствует значение в поле passwd (оно заменяется несколькими звездочками). Эта мера безопасности гарантирует, что обычный пользователь не сможет получить несанкционированный доступ к чужим паролям.

В поле usename хранится имя системного пользователя, которое представляет собой уникальную строку. Существование одноименных пользователей не допускается, потому что пользователи являются глобальными объектами базы данных. В поле usesysid хранится уникальный целый идентификатор, связанный с пользователем. Поля usecreatedb и usesuper содержат информацию о привилегиях, которые могут присваиваться пользователю при создании (см. следующий подраздел).

Создание пользователей

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

Первый способ основан па выполнении команды SQL CREATE USER в клиенте PostgreSQL (например, psql) после прохождения аутентификации. Второй способ, основанный на использовании программы createuser, более удобен для системного администратора, поскольку все действия выполняются одной командой без взаимодействия с клиентом PostgreSQL.

Создание пользователей командой SQL CREATE USER

Команда CREATE USER имеет всего один обязательный параметр — имя нового пользователя. Ей также можно передать множество других параметров, в том числе пароль, системный идентификатор, группу и права, назначаемые новому пользователю. Полный синтаксис команды CREATE USER выглядит так:

CREATE USER пользователь [ WITH [ SYSID aid ]

[ PASSWORD 'пароль' ] ]

[ CREATEDB | NOCREATEDB ]

[ CREATEUSER | NOCREATEUSER ]

[ IN GROUP группа [. ...] ]

[ VALID UNTIL 'срок' ]

Имя создаваемого пользователя определяется параметром пользователь. В системе не допускается присутствие двух пользователей с одинаковыми именами. За ключевым словом WITH следуют секции SYSID и/или PASSWORD.

Все остальные необязательные секции могут следовать в произвольном порядке (ключевое слово WITH для них не обязательно). Ниже перечислены все необязательные ключевые слова.

SYSID uid. Пользователю назначается системный идентификатор aid. Если значение не указано, выбирается некоторое уникальное число. PASSWORD ' пароль'. Новому пользователю назначается заданный пароль. Если значение не указано, по умолчанию используется пароль NULL. CREATEDB | NOCREATEDB. Ключевое слово CREATEDB предоставляет новому пользователю право создания баз данных, а также право уничтожения принадлежащих ему баз данных. Ключевое слово NOCREATEDB явно указывает на отсутствие такого права (используется по умолчанию). CREATEUSER | NOCREATEUSER. Ключевое слово CREATEUSER предоставляет пользователю право создания пользователей, наделяя его тем самым правами суперпользователя. Пользователь с правом создания других пользователей обладает всеми правами во всех базах данных (включая право создания баз данных, даже если было указано ключевое слово NOCREATEDB). Ключевое слово NOCREATEUSER явно указывает на отсутствие права создания новых пользователей. IN GROUP группа [. ... ]. Новый пользователь включается в группу с заданным именем. Допускается перечисление нескольких групп через запятую. Чтобы создание пользователя прошло успешно, перечисленные группы должны существовать. VALID UNTIL 'срок'. Пароль пользователя становится недействительным в указанный момент, который задается в одном из поддерживаемых форматов времени. По истечении срока действия пароль необходимо сменить. VALID UNTIL 'infinity'. Срок действия пароля не ограничивается.

При отсутствии ключевых слов CREATEDB и CREATEUSER создается «обычный» пользователь, не обладающий особыми привилегиями. Он не может ни создавать, ни уничтожать базы данных и других пользователей. Обычные пользователи могут подключаться к базам данных PostgreSQL, но при этом они могут выполнять лишь те команды, выполнение которых им было разрешено (см. раздел «Предоставление привилегий» этой главы).

В листинге 10.1 создается обычный пользователь с именем salesuser и паролем N0rm4!, заданным в секции WITH PASSWORD. Секция VALID UNTIL отсутствует, поэтому пароль остается действительным в течение неограниченного времени.

Листинг 10.1. Создание обычного пользователя

tempiatel=# CREATE USER dbuser CREATEDB

tempiatel-# WITH PASSWORD 'DbuSSr'

tempiatel-# VALID UNTIL '2002-11-11';

CREATE USER

Сообщение CREATE USER, выданное сервером, означает, что пользователь был создан успешно. Также возможны сообщения об ошибках.

ERROR: CREATEUSER: permission denied. Сообщение выводится в том случае, если команда CREATE USER не была введена суперпользователем. Только суперпользователь имеет право создавать новых пользователей. ERROR: CREATE USER: user name "salesuser" already exists. Пользователь с указанным именем уже существует.

Если в PostgreSQL требуется создать пользователя, которому разрешено создавать базы данных, но запрещено создавать и уничтожать пользователей PostgreSQL, укажите ключевое слово CREATEDB вместо CREATEUSER. Пользователь сможет создавать базы данных, а также уничтожать базы данных, принадлежащие ему. Операции создания и удаления баз данных рассматривались в главе 9.

В листинге 10.2 создается пользователь dbuser, которому разрешено создавать новые базы данных. Для этого после имени пользователя указывается ключевое слово CREATEDB. Также обратите внимание на секции WITH PASSWORD и VALID UNTIL -пользователю dbuser назначается пароль DbuS3r, действующий до 11 ноября 2002 года.

Листинг 10.2. Создание пользователя с правом CREATEDB

tempiatel=# CREATE USER dbuser CREATEDB

tempiatel-# WITH PASSWORD 'DbuS3r'

tempiatel-f VALID UNTIL '2002-11-11';

CREATE USER

Замена пароля с истекшим сроком действия не изменяет значения, указанного в секции VALUD UNTIL. Чтобы заново активизировать пользователя с истекшим сроком действия пароля, следует выполнить команду ALTER USER с секциями WITH PASSWORD и VALID UNTIL. Дополнительная информация о команде ALTER USER приведена в подразделе «Модификация пользователей» этого раздела.

ВНИМАНИЕ

Секция VALID UNTIL не относится к доверенным хостам, для которых пароль не нужен. Аутентификация хостов рассматривалась в главе 8.

Вероятно, помимо суперпользователя postgres вам понадобятся и другие суперпользователи, однако при их создании необходима осторожность. Супериоль-зователям предоставляются все права PostgreSQL, включая права создания и удаления пользователей, а также уничтожения баз данных. В листинге 10.3 приведен пример создания суперпользователя PostgreSQL с именем manager из приглашения psql.

Листинг 10.3. Создание суперпользователя

tempiatel=# CREATE USER manager CREATEUSER;

CREATE USER

Создание пользователей при помощи сценария createuser

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

Как и в других приложениях командной строки PostgreSQL, ключи задаются в коротком (с одним дефисом и одним символом) или в длинном формате (с двумя дефисами и полным ключом).

Синтаксис командной строки приложения createuser:

createuser [ параметры ] [ пользователь ]

Параметр пользователь представляет имя создаваемого-пользователя. Строка параметров содержит один из ключей, перечисленных ниже.

- d, -- createdb. Аналог ключевого слова CREATEDB в команде SQL CREATE USER. Разрешает новому пользователю создавать базы данных. -D, - -nocreatedb. Аналог ключевого слова NOCREATEDB в команде SQL CREATE USER. Запрещает новому пользователю создавать базы данных (используется по умолчанию). - а, - - adduser. Аналог ключевого слова CREATEUSER в команде SQL CREATE USER. Разрешает новому пользователю создавать других пользователей и поднимает его статус до суперпользователя (с предоставлением всех прав в PostgreSQL). -А, - -no- adduser. Аналог ключевого слова NOCREATEUSER в команде SQL CREATE USER. Указывает на то, что новый пользователь не является суперпользователем (используется по умолчанию). -i sysid, -sys1d=sz/5zW. Новому пользователю присваивается системный идентификатор sysid. -Р, --pwprompt. Пароль нового пользователя вводится в специальном приглашении. - h хост, -- host=xocm. Хост, с которым устанавливается связь для создания базы данных. По умолчанию используется хост local host или значение переменной среды PGHOST. -р порт, --port=nopm. Порт, по которому должно производиться подключение к серверу, вместо порта по умолчанию (обычно 5432). -U пользователь, полъзователъ. Имя пользователя, с которым производится подключение к PostgreSQL. По умолчанию используется имя системного пользователя, запустившего программу (createuser). -W, --password. У подключающегося пользователя запрашивается пароль. Это происходит автоматически, если в файле pg_hba.conf хост, от которого поступил запрос, не объявлен доверенным. - е, - - echo. Команда CREATE USER, передаваемая PostgreSQL, выводится на экран в момент ее выполнения программой createuser. -q, - -quiet. Ключ передается без параметров и запрещает вывод данных в поток stdout (хотя ошибки по-прежнему направляются в поток stderr).

Если ключи -d, -D, -а, -А или имя пользователя отсутствуют, программа createuser запрашивает все недостающие данные. Это связано с тем, что PostgreSQL не строит никаких предположений относительно прав нового пользователя или его имени. В листинге 10.4 создается пользователь newuser, которому запрещено создавать новые базы данных и новых пользователей.

Листинг 10.4. Создание пользователя сценарием createuser

[jworsley@booktown -]$ createuser -U manager -D -A newuser

CREATE USER

Обратите внимание на ключ -U manager, переданный сценарию createuser. Он указывает на то, что к PostgreSQL подключается пользователь manager, а не jworsl ey, как предположил бы сценарий по имени системной учетной записи, вызвавшей сценарий.

Если вы предпочитаете, чтобы все параметры запрашивались в интерактивном режиме (и вам не приходилось запоминать все ключи или каждый раз обращаться к документации), просто не указывайте те ключи, в которых вы не уверены. Сценарий createuser сам запросит все недостающие данные — имя пользователя, право создания баз данных и пользователей PostgreSQL.

В листинге 10.5 приведен пример использования сценария createuser в интерактивном режиме. При этом выполняются те же действия, как при однострочном вызове в листинге 10.4.

Листинг 10.5. Интерактивное создание пользователя сценарием createuser

[jworsley@booktown -]$ createuser

Enter name of user to add: newuser

Shall the new user be allowed to create databases? (y/n) n

Shall the new user be allowed to create more new users? (y/n) n

CREATE USER

Модификация пользователей

Существующие учетные записи пользователей могут изменяться только суперпользователями PostgreSQL. При этом допускается изменение всех параметров, указываемых при создании пользователя (пароль, срок действия, глобальные права), кроме системного идентификатора. Модификация существующих пользователей производится командой SQL ALTER USER. Синтаксис команды ALTER USER:

ALTER USER пользователь

[ WITH PASSWORD 'пароль' ]

[ CREATEDB NOCREATEDB ]

[ CREATEUSER NOCREATEUSER ]

[ VALID UNTIL 'срок' ]

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

WITH PASSWORD 'пароль'. Пользователю назначается заданный пароль. CREATEDB [ NOCREATEDB. Пользователю разрешается или запрещается создавать новые базы данных. CREATEUSER | NOCREATEUSER. Пользователю предоставляется статус суперпользователя (или он лишается этого статуса), при котором он обладает всеми правами в PostgreSQL, в том числе правом создания и уничтожения пользователей и суперпользователей. VALID UNTIL 'срок'. Пароль пользователя становится недействительным в указанный момент, который задается в одном из поддерживаемых форматов времени. Значение действует только для систем, требующих парольной аутентификации, и в остальных случаях игнорируется (например, для доверенных хостов).

Команда ALTER USER часто используется для изменения пароля и/или срока действия пароля. Если срок действия пароля, заданного при создании пользователя, истек, а пользователь требует парольной аутентификации, суперпользователю придется изменить пароль и срок его действия, чтобы пользователь смог снова подключиться к базе. Если срок действия пароля не ограничивается, воспользуйтесь специальным значением infinity. .

В листинге 10.6 изменяются параметры пользователя salesuser. Секция WITH PASSWORD назначает пароль n3Wp4s4, а секция VALID UNTIL указывает, что этот пароль действует до 1 января 2003 года.

Листинг 10.6. Изменение пароля

tempiatel=# ALTER USER salesuser

tempiatel-# WITH PASSWORD 'n3WP4s4'

tempiatel-# VALID UNTIL '2003-01-01':

ALTER USER

Иногда пользователю требуется предоставить дополнительные права помимо тех, которые предоставлены ему при создании. Ключевое слово CREATEUSER в листинге 10.7 изменяет права пользователя sal esuser и превращает его в суперпользователя. При этом отдельное назначение права CREATEDB становится излишним, поскольку это право предоставляется суперпользователям автоматически.

Листинг 10.7. Предоставление прав суперпользователя

tempiatel=# ALTER USER salesuser

tempiatel-# CREATEUSER;

ALTER USER

Возможна и обратная ситуация — когда требуется отобрать у пользователя права, предоставленные ему в прошлом. Задача легко решается суперпользователем при помощи ключевых слов NOCREATEDB или NOCREATEUSER (листинг 10.8).

Листинг 10.8. Отзыв прав суперпользователя

tempiatel=# ALTER USER salesuser

tempiatel-# NOCREATEDB NOCREATEUSER;

ALTER USER

ВНИМАНИЕ

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

Удаление пользователей

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

Задача удаления пользователей PostgreSQL, как и их создание, решается двумя способами. Вы можете воспользоваться командой SQL DROP USER и приложением командной строки dropuser.

Удаление пользователей командой SQL DROP USER

Чтобы удалить пользователя PostgreSQL, суперпользователь может ввести команду DROP USER в любом клиенте. Чаще всего для этого применяется клиент psql.

Синтаксис команды DROP USER:

DROP USER пользователь

Параметр пользователь определяет имя пользователя, удаляемого из системы. В листинге 10.9 клиент psql подключается к PostgreSQL с именем пользователя manager и удаляет пользователя salesuser.

Листинг 10.9. Удаление пользователя командой DROP USER

[jworsley@booktown -]$ psql -U manager

template! Welcome to psql. the PostgreSQL interactive terminal.

Type: \copyright for distribution terms

\h for help with SQL commands

\? for help on internal slash commands

\g or terminate with semicolon to execute query

\q to quit

tempiatel=# DROP USER salesuser:

DROP USER

Сообщение DROP USER, выданное сервером, означает, что пользователь был успешно удален из системы. Также возможны сообщения об ошибках.

ERROR: DROPUSER: permission denied. Команда удаления введена пользователем, не обладающим правом удаления других пользователей. Только суперпользователь может удалять других пользователей. ERROR: DROP USER: user "salesuser" does not exist. Пользователь с указанным именем не существует.

Удаление пользователей при помощи сценария dropuser

Сценарий dropuser имеет много общего со сценарием createuser. Он поддерживает те же параметры подключения, что позволяет запускать его как в локальном, так и в удаленном режиме. Единственным обязательным аргументом является имя пользователя, удаляемого из системы.

Синтаксис командной строки приложения dropuser:

dropuser [ параметры ] [ пользователь ]

Ключи, входящие в строку параметров, были описаны в пункте «Создание пользователей при помощи сценария createuser» подраздела «Создание пользователей» этого раздела. В листинге 10.10 приложение dropuser выполняет ту же операцию, которая выполнялась командой SQL из листинга 10.9, — пользователь manager удаляет из системы пользователя salesuser.

Листинг 10.10. Удаление пользователя приложением dropuser

[jworsley@booktown -]$ dropuser -U manager salesuser

DROP USER

Приложение dropuser выводит те же сообщения, что и команда SQL DROP USER. Если при выполнении сценария dropuser в командной строке отсутствует имя удаляемого пользователя, оно запрашивается в интерактивном режиме.

Управление группами

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

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

ПРИМЕЧАНИЕ

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

Создание и удаление групп

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

Создание группы

Любой суперпользователь может создать новую группу PostgreSQL командой SQL CREATE GROUP. Синтаксис команды CREATE GROUP:

CREATE GROUP группа [ WITH

[ SYSID идентификатор_группы ]

[ USER пользователь [. ...]]]

Параметр группа определяет имя создаваемой группы. Имена групп должны начинаться с алфавитного символа, а их длина не может превышать 31 символа. В секции WITH передаются дополнительные атрибуты. Ключевое слово SYSID задает системный идентификатор новой группы. Ключевое слово USER позволяет включить одного или нескольких пользователей в группу на стадии ее создания. Имена членов группы разделяются запятыми.

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

В листинге 10.11 создается группа sales, в которую при создании включаются два пользователя, all en и vi ncent.

Листинг 10.11. Создание группы

booktown=# CREATE GROUP sales

booktown-# WITH USER alien, Vincent;

CREATE GROUP

Сообщение CREATE GROUP означает, что группа успешно создана. Чтобы убедиться в этом, а также получить список всех существующих групп, достаточно выполнить запрос к системной таблице рд_дгоир. Пример такого запроса приведен в листинге 10.12.

Листинг 10.12. Получение списка групп

booktown=# SELECT * FROM pg_group;

groname grosysid | grolist

sales | 1 | {7017.7016}

accounting | 2 |

marketing | 3

(3 rows)

Обратите внимание на столбец grol 1st — в нем перечислены идентификаторы пользователей PostgreSQL, входящих в группу. Это те самые идентификаторы, которые выводятся в представлении pg_user. Пример:

booktown=# SELECT usename FROM pg_user

booktown-# WHERE usesysid = 7017 OR usesysid = 7016;

usename

alien

vincent

(2 rows)

Удаление группы

Любой суперпользователь может удалять существующие группы командой SQL DROP GROUP. Будьте внимательны при выполнении этой команды, поскольку она необратима и вам не будет предложено подтвердить удаление группы (даже если в ней остаются пользователи). В отличие от команды DROP DATABASE, команда DROP GROUP может выполняться в транзакционном блоке.

Синтаксис команды DROP GROUP:

DROP GROUP группа

Параметр группа определяет имя удаляемой группы. В листинге 10.13 из базы данных booktown удаляется ненужная группа marketi ng.

Листинг 10.13. Удаление группы

booktown=# DROP GROUP marketing;

DROP GROUP

Сообщение DROP GROUP означает, что группа успешно удалена. Учтите, что при удалении группы назначенные ей права не уничтожаются, а всего лишь становятся «бестелесными», не связанными с реальным субъектом. Все права объекта базы данных для удаленной группы фактически относятся не к самой группе, а ее системному идентификатору.

ПРИМЕЧАНИЕ

Случайно удаленную группу можно восстановить. Для этого следует создать новую группу с тем же системным идентификатором, что у удаленной группы. Системный идентификатор назначается ключевым словом SYSID, о котором говорилось в пункте «Создание группы». Если присвоить группе права доступа к таблице и затем удалить эту группу, то права группы для таблицы останутся. Разумеется, в созданную группу нужно будет заново включить пользователей.

Назначение пользователей в группы

Включение пользователей в группы и удаление их из групп в PostgreSQL выполняется командой SQL ALTER GROUP. Синтаксис команды ALTER GROUP:

ALTER GROUP группа { ADD DROP } USER пользователь [. ... ]

Параметр группа определяет имя группы, а параметр пользователь — имя включаемого или удаляемого пользователя (в зависимости от присутствия ключевого слова ADD или DROP).

Включение пользователя в группу

Предположим, ваша компания принимает в отдел сбыта двух новых работников, для которых создаются учетные записи пользователей с именами davi d и ben. В листинге 10.14 приведена команда ALTER GROUP для включения новых пользователей в группу sales.

Листинг 10.14. Включение пользователей в группу

booktown=# ALTER GROUP sales ADD USER david. ben;

ALTER GROUP

Сообщение ALTER GROUP означает, что пользователи david и ben успешно включены в группу sales. В листинге 10.15 приведен запрос к таблице рд_дгоир, проверяющий наличие новых пользователей в группе. Обратите внимание: количество системных идентификаторов в столбце grolist для группы sales увеличилось до четырех.

Листинг 10.15. Проверка факта включения пользователей в группу

booktown=# SELECT * FROM pg_group WHERE groname = 'sales':

groname | grosysid | grolist

sales 1 | {7019.7018.7017.7016}

(1 row)

Удаление пользователя из группы

Предположим, один из работников был переведен из отдела сбыта в бухгалтерию. Чтобы сохранить правильный состав групп и убедиться в том, что он не обладаетправами, предоставляемыми только группе sales, пользователя david необходимо исключить из группы. Задача решается командой ALTER GROUP, приведенной в листинге 10.16.

Листинг 10.16. Удаление пользователя из группы

booktown=# ALTER GROUP sales DROP USER david;

ALTER GROUP

Сообщение ALTER GROUP означает, что пользователь david успешно удален из группы sales.

Перевод работника завершается его включением в группу accounti ng. В следующем фрагменте использованы команды, аналогичные командам в листингах 10.14 и 10.15. В результате пользователь david включается в группу accounting, и любые права, предоставляемые этой группе, будут автоматически предоставляться пользователю david до тех пор, пока он остается членом этой группы.

booktown=# ALTER GROUP accounting ADD USER david:

ALTER GROUP

booktown=# SELECT * FROM pg_group;

groname | grosysid | grolist

sales | 1 | {7016.7017.7019}

accounting 2 | {7018}

(2 rows)

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

В PostgreSQL учет привилегий ведется при помощи списков управления доступом (access control lists, ACL). Информация, хранящаяся в этих списках, указывает, каким пользователям разрешена выборка, обновление и другие способы модификации объектов в базе данных. Для всех объектов баз данных PostgreSQL (таблиц, представлений и последовательностей) существует набор привилегий и ограничений доступа. Суперпользователи и владельцы объектов изменяют ACL при помощи пары команд SQL: GRANT и REVOKE.

Как было сказано в главе 9, когда пользователь создает базу данных, он косвенно становится владельцем этой базы. Аналогичный принцип распространяется и на другие объекты: они также принадлежат пользователю, который ввел соответствующую команду SQL CREATE.

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

Управление доступом

Как упоминалось выше, списки управления доступом относятся к трем категориям объектов баз данных: таблицам, спискам и последовательностям. Для этих объектов существуют четыре общие привилегии, которые могут предоставляться пользователям или группам и отбираться у них. Отзыв прав требуется только для отмены их предшествующего предоставления. Изначально пользователи и группы не имеют никаких прав.

Сводка прав ACL просматривается в клиенте psql при помощи команды \z. Команда выводит информацию обо всех правах доступа для базы данных, подключенной в настоящий момент. Чтобы просмотреть права для конкретного объекта, передайте имя этого объекта в качестве параметра команды \z. Вместо имени можно задать регулярное выражение, чтобы просмотреть привилегии для группы объектов.

В табл. 10.2 перечислены привилегии доступа, поддерживаемые в PostgreSQL. Каждой привилегии соответствует определенная буква, которая используется как сокращенное обозначение этой привилегии и включается в выходные данные команды \z клиента psql.

Таблица 10.2. Привилегии доступа в PostqreSQL

Ключевое слово	Символ	Описание
SELECT	r	Пользователю разрешается выборка данных из таблицы, представления или последовательности (хотя функция nextval О не может вызываться при наличии только права SELECT). Также используется термин «право чтения»
INSERT	a	Пользователю разрешается вставка новых записей в таблицу. Также используется термин «право дополнения»
UPDATE, DELETE	w	Пользователю разрешается модификация и удаление записей из таблицы. При предоставлении хотя бы одного из прав UPDATE или DELETE другое право предоставляется автоматически. Также используется термин «право записи»
RULE	R	Пользователю разрешается создание правила перезаписи для таблицы или представления
ALL	arwR	Сокращенное обозначение, используемое для предоставления или отзыва всех прав. Само по себе ключевое слово ALL правом не является. Указание ключевого слова ALL приводит к предоставлению прав SELECT, INSERT, UPDATE, DELETE и RULE

Предоставление привилегий командой GRANT

Для предоставления привилегий пользователям и группам используется команда SQL GRANT. Синтаксис команды GRANT:

GRANT привилегия [, ...] ON объект [, ...]

ТО { PUBLIC | пользователь \ GROUP группа }

Параметр привилегия заменяется любой из привилегий, перечисленных в табл. 10.2, а параметр объект определяет имя объекта базы данных (таблицы, представления или последовательности), для которого предоставляется привилегия. Секция, следующая после ключевого слова ТО, указывает, кому предоставляется привилегия. Допускается перечисление нескольких привилегии и объектов через запятую.

В одной команде GRANT может использоваться только один из вариантов, перечисленных за ключевым словом ТО. С ключевым словом PUBLIC привилегия предоставляется всем пользователям. Если указано имя пользователя или группы, привилегия предоставляется конкретному пользователю или группе.

Предположим, пользователю manager понадобились все права доступа к таблицам customers, books, editions и publishers. В листинге 10.17 все эти права предоставляются пользователю manager в одной команде GRANT.

Листинг 10.17. Предоставление привилегий пользователям

booktown=# GRANT ALL ON customers, books, editions, publishers

booktown-# TO manager;

CHANGE

Ключевое слово ALL предоставляет пользователю manager все возможные права доступа (SELECT, UPDATE и т. д.) к перечисленным объектам. Сообщение CHANGE означает, что модификация привилегий прошла успешно. Помните, что для проверки прав доступа к объекту базы данных в psql может использоваться команда \z:

booktown=# \z publishers

Access permissions for database "booktown"

Relation Access permissions

publishers | {"="."manager=arwR"}

(1 row)

Рассмотрим другой пример — использование ключевого слова GROUP для предоставления привилегий членам группы. Допустим, всему отделу сбыта нужно предоставить право просмотра таблицы customers, но без возможности ее модификации. В листинге 10.18 всем членам группы sales предоставляется право доступа SELECT к таблице customers.

Листинг 10.18. Предоставление групповых привилегий

booktown=# GRANT SELECT ON customers TO GROUP sales:

CHANGE

booktown=# \z customers

Access permissions for database "booktown"

Relation | Access permissions

customers | {"="."manager=arwR"."group sales=r"}

(1 row)

Ограничение прав командой REVOKE

По умолчанию обычный пользователь не имеет прав доступа к объектам данным, которые ему не принадлежат. Чтобы отозвать право после его предоставления, владелец объекта (или суперпользователь) может воспользоваться командой REVOKE, имеющей много общего с командой GRANT. Синтаксис команды REVOKE:

REVOKE привилегия [. ...] ON объект [. ... ]

FROM { PUBLIC | пользователь \ GROUP группа }

По своей структуре команда REVOKE идентична команде GRANT. Отличается только название самой команды и ключевое слово FROM заменяется словом ТО.

ПРИМЕЧАНИЕ

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

Предположим, пользователю david было предоставлено право доступа UPDATE к таблице books. В другом отделе пользователю уже не придется редактировать сведения о книгах, и теперь вы хотите отнять у него привилегию UPDATE на доступ к таблице books.

В листинге 10.19 команда \z клиента psql выводит информацию о правах для таблицы books; из результатов видно, что пользователь david имеет право записи в эту таблицу. Команда REVOKE отнимает у пользователя david привилегии UPDATE и DELETE для таблицы books. Фрагмент завершается еще одной командой \z, которая показывает, что привилегия была отозвана успешно.

Листинг 10.19. Отзыв прав

booktown=# \z books

Access permissions for database "booktown"

Relation | Access permissions

books | {"="."manager=arwR"."david=w"}

(1 row)

booktown=# REVOKE UPDATE, DELETE ON books

booktown-# FROM david;

CHANGE

booktown=# \z books

Access permissions for database "booktown"

Relation Access permissions

books | {"="."manager=arwR"}

(1 row)

Представления при контроле доступа

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

Чтобы ограничить доступ к полям, перечислите их в списке полей команды SELECT при создании представления. В результате представление будет возвращать только те поля, которые были указаны вами. Ограничение доступа к записям осуществляется включением секции WHERE в команду SELECT, после чего представление будет состоять из записей, удовлетворяющих условию в секции WHERE (за дополнительной информацией о создании представлений обращайтесь к главе 4).

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

Допустим, в базе данных booktown имеется таблица stock, в которой код ISBN книги связывается с закупочной ценой, розничной ценой продажи и количеством экземпляров на складе. Структуру таблицы иллюстрирует табл. 10.3.

Таблица 10.3. Таблица stock

Поле	Тип	Модификатор
isbn	text	NOT NULL
cost	numeric (5. 2)
retail	numeric (5. 2)
stock	integer

Допустим, вы не хотите, чтобы работники из отдела продаж знали закупочную цену книг. Чтобы ограничить доступ к этой информации, создайте представление, в которое входят данные из полей 1 sbn, retai 1 и stock. В листинге 10.20 мы создаем представление, даем право доступа к нему группе sales и затем проверяем результат командой \z в клиенте psql.

Листинг 10.20. Управление привилегией SELECT с использованием представления

booktown=# CREATE VIEW stock_view

booktown-# AS SELECT isbn, retail, stock

booktown-# FROM stock;

CREATE

booktown=# GRANT SELECT ON stockjhew TO GROUP sales;

CHANGE

booktown=# \z stock

Access permissions for database "booktown"

Relation | Access permissions

stock |

stock_backup j

stock_view | {"=","manager=arwR"."group sales=r"}

(3 rows)

В листинге 10.21 создается новый пользователь barbara, которому предоставляется право SELECT для представления stock_view. Поскольку пользователь barbara изначально не обладает никакими правами доступа к таблице stock, таблица будет недоступна; ситуация не изменяется даже после получения доступа к представлению в результате выполнения команды GRANT.

Листинг 10.21. Управление привилегией SELECT

booktown=# CREATE USER barbara;

CREATE USER

booktown=# GRANT USER barbara SELECT ON stock_view;

booktown=# \c - barbara

You are now connected as new user barbara.

booktown=> SELECT * FROM stock;

ERROR: stock: Permission denied.

booktown=> SELECT * FROM stock_view;

Isbn retail stock

0385121679 36.95 65

039480001X 32.95 31

0394900014 23.95 0

044100590X 45.95 89

0441172717 21.95 77

0451160916 28.95 22

0451198492 46.95 0

0451457994 22.95 0

0590445065 23.95 10

0679803335 24.95 18

0694003611 28.95 50

0760720002 23.95 28

0823015505 28.95 16

0929605942 21.95 25

1885418035 24.95 77

0394800753 16.95 4

(16 rows)

При подключении под именем barbara команда SELECT для представления stock_v1ew выполняется успешно, а выборка из таблицы stock приводит к ошибке Permission denied.

---

---