Postgres error permission denied for table

In addition to the SQL-standard privilege system available through GRANT, tables can have row security policies that restrict, on a per-user basis, which rows can be returned by normal queries or inserted, updated, or deleted by data modification commands. This feature is also known as Row-Level Security. By default, tables do not have any policies, so that if a user has access privileges to a table according to the SQL privilege system, all rows within it are equally available for querying or updating.

When row security is enabled on a table (with ALTER TABLE … ENABLE ROW LEVEL SECURITY), all normal access to the table for selecting rows or modifying rows must be allowed by a row security policy. (However, the table’s owner is typically not subject to row security policies.) If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified. Operations that apply to the whole table, such as TRUNCATE and REFERENCES, are not subject to row security.

Row security policies can be specific to commands, or to roles, or to both. A policy can be specified to apply to ALL commands, or to SELECT, INSERT, UPDATE, or DELETE. Multiple roles can be assigned to a given policy, and normal role membership and inheritance rules apply.

To specify which rows are visible or modifiable according to a policy, an expression is required that returns a Boolean result. This expression will be evaluated for each row prior to any conditions or functions coming from the user’s query. (The only exceptions to this rule are leakproof functions, which are guaranteed to not leak information; the optimizer may choose to apply such functions ahead of the row-security check.) Rows for which the expression does not return true will not be processed. Separate expressions may be specified to provide independent control over the rows which are visible and the rows which are allowed to be modified. Policy expressions are run as part of the query and with the privileges of the user running the query, although security-definer functions can be used to access data not available to the calling user.

Superusers and roles with the BYPASSRLS attribute always bypass the row security system when accessing a table. Table owners normally bypass row security as well, though a table owner can choose to be subject to row security with ALTER TABLE … FORCE ROW LEVEL SECURITY.

Enabling and disabling row security, as well as adding policies to a table, is always the privilege of the table owner only.

Policies are created using the CREATE POLICY command, altered using the ALTER POLICY command, and dropped using the DROP POLICY command. To enable and disable row security for a given table, use the ALTER TABLE command.

Each policy has a name and multiple policies can be defined for a table. As policies are table-specific, each policy for a table must have a unique name. Different tables may have policies with the same name.

When multiple policies apply to a given query, they are combined using either OR (for permissive policies, which are the default) or using AND (for restrictive policies). This is similar to the rule that a given role has the privileges of all roles that they are a member of. Permissive vs. restrictive policies are discussed further below.

As a simple example, here is how to create a policy on the account relation to allow only members of the managers role to access rows, and only rows of their accounts:

CREATE TABLE accounts (manager text, company text, contact_email text);

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_managers ON accounts TO managers
    USING (manager = current_user);

The policy above implicitly provides a WITH CHECK clause identical to its USING clause, so that the constraint applies both to rows selected by a command (so a manager cannot SELECT, UPDATE, or DELETE existing rows belonging to a different manager) and to rows modified by a command (so rows belonging to a different manager cannot be created via INSERT or UPDATE).

If no role is specified, or the special user name PUBLIC is used, then the policy applies to all users on the system. To allow all users to access only their own row in a users table, a simple policy can be used:

CREATE POLICY user_policy ON users
    USING (user_name = current_user);

This works similarly to the previous example.

To use a different policy for rows that are being added to the table compared to those rows that are visible, multiple policies can be combined. This pair of policies would allow all users to view all rows in the users table, but only modify their own:

CREATE POLICY user_sel_policy ON users
    FOR SELECT
    USING (true);
CREATE POLICY user_mod_policy ON users
    USING (user_name = current_user);

In a SELECT command, these two policies are combined using OR, with the net effect being that all rows can be selected. In other command types, only the second policy applies, so that the effects are the same as before.

Row security can also be disabled with the ALTER TABLE command. Disabling row security does not remove any policies that are defined on the table; they are simply ignored. Then all rows in the table are visible and modifiable, subject to the standard SQL privileges system.

Below is a larger example of how this feature can be used in production environments. The table passwd emulates a Unix password file:

-- Simple passwd-file based example
CREATE TABLE passwd (
  user_name             text UNIQUE NOT NULL,
  pwhash                text,
  uid                   int  PRIMARY KEY,
  gid                   int  NOT NULL,
  real_name             text NOT NULL,
  home_phone            text,
  extra_info            text,
  home_dir              text NOT NULL,
  shell                 text NOT NULL
);

CREATE ROLE admin;  -- Administrator
CREATE ROLE bob;    -- Normal user
CREATE ROLE alice;  -- Normal user

-- Populate the table
INSERT INTO passwd VALUES
  ('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');
INSERT INTO passwd VALUES
  ('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');
INSERT INTO passwd VALUES
  ('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');

-- Be sure to enable row-level security on the table
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;

-- Create policies
-- Administrator can see all rows and add any rows
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
-- Normal users can view all rows
CREATE POLICY all_view ON passwd FOR SELECT USING (true);
-- Normal users can update their own records, but
-- limit which shells a normal user is allowed to set
CREATE POLICY user_mod ON passwd FOR UPDATE
  USING (current_user = user_name)
  WITH CHECK (
    current_user = user_name AND
    shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
  );

-- Allow admin all normal rights
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
-- Users only get select access on public columns
GRANT SELECT
  (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
  ON passwd TO public;
-- Allow users to update certain columns
GRANT UPDATE
  (pwhash, real_name, home_phone, extra_info, shell)
  ON passwd TO public;

As with any security settings, it’s important to test and ensure that the system is behaving as expected. Using the example above, this demonstrates that the permission system is working properly.

-- admin can view all rows and fields
postgres=> set role admin;
SET
postgres=> table passwd;
 user_name | pwhash | uid | gid | real_name |  home_phone  | extra_info | home_dir    |   shell
-----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------
 admin     | xxx    |   0 |   0 | Admin     | 111-222-3333 |            | /root       | /bin/dash
 bob       | xxx    |   1 |   1 | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | xxx    |   2 |   1 | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)

-- Test what Alice is able to do
postgres=> set role alice;
SET
postgres=> table passwd;
ERROR:  permission denied for relation passwd
postgres=> select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
 user_name | real_name |  home_phone  | extra_info | home_dir    |   shell
-----------+-----------+--------------+------------+-------------+-----------
 admin     | Admin     | 111-222-3333 |            | /root       | /bin/dash
 bob       | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)

postgres=> update passwd set user_name = 'joe';
ERROR:  permission denied for relation passwd
-- Alice is allowed to change her own real_name, but no others
postgres=> update passwd set real_name = 'Alice Doe';
UPDATE 1
postgres=> update passwd set real_name = 'John Doe' where user_name = 'admin';
UPDATE 0
postgres=> update passwd set shell = '/bin/xx';
ERROR:  new row violates WITH CHECK OPTION for "passwd"
postgres=> delete from passwd;
ERROR:  permission denied for relation passwd
postgres=> insert into passwd (user_name) values ('xxx');
ERROR:  permission denied for relation passwd
-- Alice can change her own password; RLS silently prevents updating other rows
postgres=> update passwd set pwhash = 'abc';
UPDATE 1

All of the policies constructed thus far have been permissive policies, meaning that when multiple policies are applied they are combined using the “OR” Boolean operator. While permissive policies can be constructed to only allow access to rows in the intended cases, it can be simpler to combine permissive policies with restrictive policies (which the records must pass and which are combined using the “AND” Boolean operator). Building on the example above, we add a restrictive policy to require the administrator to be connected over a local Unix socket to access the records of the passwd table:

CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin
    USING (pg_catalog.inet_client_addr() IS NULL);

We can then see that an administrator connecting over a network will not see any records, due to the restrictive policy:

=> SELECT current_user;
 current_user
--------------
 admin
(1 row)

=> select inet_client_addr();
 inet_client_addr
------------------
 127.0.0.1
(1 row)

=> TABLE passwd;
 user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell
-----------+--------+-----+-----+-----------+------------+------------+----------+-------
(0 rows)

=> UPDATE passwd set pwhash = NULL;
UPDATE 0

Referential integrity checks, such as unique or primary key constraints and foreign key references, always bypass row security to ensure that data integrity is maintained. Care must be taken when developing schemas and row level policies to avoid “covert channel” leaks of information through such referential integrity checks.

In some contexts it is important to be sure that row security is not being applied. For example, when taking a backup, it could be disastrous if row security silently caused some rows to be omitted from the backup. In such a situation, you can set the row_security configuration parameter to off. This does not in itself bypass row security; what it does is throw an error if any query’s results would get filtered by a policy. The reason for the error can then be investigated and fixed.

In the examples above, the policy expressions consider only the current values in the row to be accessed or updated. This is the simplest and best-performing case; when possible, it’s best to design row security applications to work this way. If it is necessary to consult other rows or other tables to make a policy decision, that can be accomplished using sub-SELECTs, or functions that contain SELECTs, in the policy expressions. Be aware however that such accesses can create race conditions that could allow information leakage if care is not taken. As an example, consider the following table design:

-- definition of privilege groups
CREATE TABLE groups (group_id int PRIMARY KEY,
                     group_name text NOT NULL);

INSERT INTO groups VALUES
  (1, 'low'),
  (2, 'medium'),
  (5, 'high');

GRANT ALL ON groups TO alice;  -- alice is the administrator
GRANT SELECT ON groups TO public;

-- definition of users' privilege levels
CREATE TABLE users (user_name text PRIMARY KEY,
                    group_id int NOT NULL REFERENCES groups);

INSERT INTO users VALUES
  ('alice', 5),
  ('bob', 2),
  ('mallory', 2);

GRANT ALL ON users TO alice;
GRANT SELECT ON users TO public;

-- table holding the information to be protected
CREATE TABLE information (info text,
                          group_id int NOT NULL REFERENCES groups);

INSERT INTO information VALUES
  ('barely secret', 1),
  ('slightly secret', 2),
  ('very secret', 5);

ALTER TABLE information ENABLE ROW LEVEL SECURITY;

-- a row should be visible to/updatable by users whose security group_id is
-- greater than or equal to the row's group_id
CREATE POLICY fp_s ON information FOR SELECT
  USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
CREATE POLICY fp_u ON information FOR UPDATE
  USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));

-- we rely only on RLS to protect the information table
GRANT ALL ON information TO public;

Now suppose that alice wishes to change the “slightly secret” information, but decides that mallory should not be trusted with the new content of that row, so she does:

BEGIN;
UPDATE users SET group_id = 1 WHERE user_name = 'mallory';
UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;
COMMIT;

That looks safe; there is no window wherein mallory should be able to see the “secret from mallory” string. However, there is a race condition here. If mallory is concurrently doing, say,

SELECT * FROM information WHERE group_id = 2 FOR UPDATE;

and her transaction is in READ COMMITTED mode, it is possible for her to see “secret from mallory”. That happens if her transaction reaches the information row just after alice‘s does. It blocks waiting for alice‘s transaction to commit, then fetches the updated row contents thanks to the FOR UPDATE clause. However, it does not fetch an updated row for the implicit SELECT from users, because that sub-SELECT did not have FOR UPDATE; instead the users row is read with the snapshot taken at the start of the query. Therefore, the policy expression tests the old value of mallory‘s privilege level and allows her to see the updated row.

There are several ways around this problem. One simple answer is to use SELECT ... FOR SHARE in sub-SELECTs in row security policies. However, that requires granting UPDATE privilege on the referenced table (here users) to the affected users, which might be undesirable. (But another row security policy could be applied to prevent them from actually exercising that privilege; or the sub-SELECT could be embedded into a security definer function.) Also, heavy concurrent use of row share locks on the referenced table could pose a performance problem, especially if updates of it are frequent. Another solution, practical if updates of the referenced table are infrequent, is to take an ACCESS EXCLUSIVE lock on the referenced table when updating it, so that no concurrent transactions could be examining old row values. Or one could just wait for all concurrent transactions to end after committing an update of the referenced table and before making changes that rely on the new security situation.

For additional details see CREATE POLICY and ALTER TABLE.

В PostgreSQL для работы с объектами роль должна иметь привилегии к этим объектам (таблицам, функциям). В этой статье разберёмся с привилегиями PostgreSQL.

Привилегий для разных объектов

Каждый вид объектов имеет разный набор привилегий, таблицы например имеют самый большой набор:

• SELECT – чтение данных;
• INSERT – вставка данных;
• UPDATE – изменение строк;
• REFERENCES – внешний ключ (право ссылаться на таблицу);
• DELETE – удаление строк;
• TRUNCATE – очистка таблицы;
• TRIGGER – создание триггеров.

Представления имеют всего две привилегии:

• SELECT – право читать представление;
• TRIGGER – право создавать триггеры.

Последовательности:

• SELECT – право читать последовательность;
• UPDATE – право изменять последовательность;
• USAGE – право использовать последовательность.

Табличные пространства:

• CREATE – разрешает создавать объекты внутри табличного пространства.

Базы данных имеют три привилегии:

• CREATE – разрешает создавать схемы внутри базы данных;
• CONNECT – даёт возможность подключаться к базе данных;
• TEMPORARY – разрешает создавать в базе данных временные таблицы.

У схем есть две привилегии:

• CREATE – разрешает создавать объекты внутри конкретной схемы;
• USAGE – позволяет использовать объекты в конкретной схеме.

У функций есть только одна привилегия:

• EXECUTE – даёт право выполнять функцию.

Категории ролей

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

• Суперпользователи – полный доступ ко всем объектам – проверки не выполняются;
• Владельцы – владельцем становиться тот, кто создал объект. Но право владения можно передать. Владелец имеет все привилегии на принадлежащий ему объект;
• Остальные роли – доступ только в рамках выданных привилегий на определённый объект. Такие привилегии могут выдать владельцы на свои объекты. Или может выдать суперпользователь на любой другой объект.

Выдача и отзыв привилегий

Выдать привилегию можно с помощью команды GRANT:

GRANT <привилегии> ON <объект> to <роль>;

Забрать привилегию можно с помощью команды REVOKE:

REVOKE <привилегии> ON <объект> FROM <роль>;

Выданной привилегией можно пользоваться, но нельзя передавать другим ролям. Но владелец или суперпользователь может вместе с привилегией выдать дополнительную опцию, которая разрешит передавать привилегию другим ролям. Выдача привилегии с правом её передачи выполняется с помощью WITH GRAND OPTION:

GRANT <привилегии> ON <объект> to <роль> WITH GRAND OPTION;

Если мы дали привилегию вместе с правом её передачи. А затем роль воспользовалась своим правом и передала привилегию другим ролям. То забрать эту привилегию можно только каскадно у этой роли и у других ролей с помощью CASCADE:

REVOKE <привилегии> ON <объект> FROM <роль> CASCADE;

Можно не отбирать привилегию, а только отобрать право её передачи. Это делается следующим способом:

REVOKE GRANT OPTION FOR <привилегии> ON <объект> FROM <роль>;

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

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

• INHERIT – атрибут роли, который включает автоматическое наследование привилегий;
• NOINHERIT – атрибут роли, который требует явное выполнение SET ROLE.

В 13 PostgreSQL при инициализации кластера создаются следующие роли вместе с суперпользователем postgres:

• pg_signal_backend – право посылать сигналы обслуживающим процессам, например можно вызвать функцию pg_reload_conf() или завершить процесс с помощью функции pg_terminate_backend();
• pg_read_all_settings – право читать все конфигурационные параметры, даже те, что обычно видны только суперпользователям;
• pg_read_all_stats – право читать все представления pg_stat_* и использовать различные расширения, связанные со статистикой, даже те, что обычно видны только суперпользователям;
• pg_stat_scan_tables – право выполнять функции мониторинга, которые могут устанавливать блокировки в таблицах, возможно, на длительное время;
• pg_monitor – право читать и выполнять различные представления и функции для мониторинга. Эта роль включена в роли pg_read_all_settings, pg_read_all_stats и pg_stat_scan_tables;
• pg_read_server_files – право читать файлы в любом месте файловой системы, куда имеет доступ postgres на сервере. А также выполняя копирование и другие функции работы с файлами;
• pg_write_server_files – право записывать файлы в любом месте файловой системы, куда имеет доступ postgres на сервере. А также выполнять копирование и другие функции работы с файлами.
• pg_execute_server_program – право выполнять программы на сервере (от имени пользователя, запускающего СУБД).

Псевдо роль public

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

Роль public по умолчанию имеет следующие привилегии:

• для всех баз данных:
  • CONNECT – это означает что любая созданная роль сможет подключаться к базам данных, но не путайте с привилегией LOGIN;
  • TEMPORARY – любая созданная роль сможет создавать временные объекты во всех база данных и объекты эти могут быть любого размера;
• для схемы public:
  • CREATE (создание объектов) – любая роль может создавать объекты в этой схеме;
  • USAGE (доступ к объектам) – любая роль может использовать объекты в этой схеме;
• для схемы pg_catalog и information_schema:
  • USAGE (доступ к объектам) – любая роль может обращаться к таблицам системного каталога;
• для всех функций:
  • EXECUTE (выполнение) – любая роль может выполнять любую функцию. Ещё нужны ещё права USAGE на ту схему, в которой функция находится, и права к объектам к которым обращается функция.

Это сделано для удобства, но снижает безопасность сервера баз данных.

Привилегии по умолчанию

Привилегии по умолчанию – это такие привилегии, которые добавятся к каким-то ролям на объект при его создании. Например роль Алиса хочет чтобы при создании новой таблицы доступ к ней сразу же получала роль Боб.

Привилегии по умолчанию создаются командой ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES [IN SCHEMA <схема>] GRANT <привилегии> ON <класс_объектов> TO <роль>;

В примере выше <класс_объектов> это может быть, например таблица, функция, представление и т.п. То есть создаём мы какой-то объект из этого класса и сразу срабатывает команда выдачи привилегий: GRANT <привилегии> ON ... .

Аналогично можно удалять такие привилегии:

ALTER DEFAULT PRIVILEGES [IN SCHEMA <схема>] REVOKE <привилегии> ON <класс_объектов> FROM <роль>;

Например сделаем так, чтобы при создании функций (любым пользователем), право их выполнять забиралось у роли public:

ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM public;

Затем вам придется отдельным ролям давать эту привилегию вручную. Или можете сделать отдельную групповую роль, которая сможет выполнять функции, и включать неё другие роли.

Практика

Выдаем различные привилегии на объекты

Подключимся к базе данных postgres под ролью postgres. Затем создадим роль alice и создадим схему alice. Дальше дадим Алисе привилегии создавать и использовать объекты в схеме alice. И наконец подключимся под Алисой:

postgres@s-pg13:~$ psql
Timing is on.
psql (13.3)
Type "help" for help.

postgres@postgres=# CREATE ROLE alice LOGIN;
CREATE ROLE
Time: 0,792 ms

postgres@postgres=# CREATE SCHEMA alice;
CREATE SCHEMA
Time: 0,853 ms

postgres@postgres=# GRANT CREATE, USAGE ON SCHEMA alice to alice;
GRANT
Time: 0,647 ms

postgres@postgres=# c - alice
You are now connected to database "postgres" as user "alice".

alice@postgres=>

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

Под Алисой создадим 2 таблицы, затем переключимся на роль postgres и дадим Бобу право подключаться к базам:

alice@postgres=> CREATE TABLE t1(n integer);
CREATE TABLE
Time: 1,903 ms

alice@postgres=> CREATE TABLE t2(n integer, m integer);
CREATE TABLE
Time: 0,715 ms

alice@postgres=> c - postgres
You are now connected to database "postgres" as user "postgres".

postgres@postgres=# ALTER ROLE bob LOGIN;
ALTER ROLE
Time: 0,613 ms

Теперь переключимся на роль Боб и под ним попытаемся прочитать табличку t1 в схеме alice:

postgres@postgres=# c - bob;
You are now connected to database "postgres" as user "bob".

bob@postgres=> SELECT * FROM alice.t1;
ERROR:  permission denied for schema alice
LINE 1: SELECT * FROM alice.t1;
                      ^
Time: 0,300 ms

Возникла ошибка, так как у Боба нет привилегии USAGE на схему alice.

Посмотрим какие есть привилегии у схемы alice с помощью команды dn+:

bob@postgres=> dn+ alice
                    List of schemas
 Name  |  Owner   |  Access privileges   | Description
-------+----------+----------------------+-------------
 alice | postgres | postgres=UC/postgres+|
       |          | alice=UC/postgres    |
(1 row)

В поле “Access privileges” написаны построчно привилегии в следующем формате: роль=привилегии/кем_выданы.

Привилегии сокращаются по первой букве:

• U = Usage;
• C = Create.

Подключимся под Алисой и попробуем от неё выдать права Бобу:

bob@postgres=> c - alice
You are now connected to database "postgres" as user "alice".

alice@postgres=> GRANT CREATE, USAGE ON SCHEMA alice TO bob;
WARNING:  no privileges were granted for "alice"
GRANT
Time: 0,710 ms

Ошибка появилась потому-что Алиса не является владельцем этой схемы и не имеет право передавать привилегии.

Переключимся на роль postgres и сделаем Алису владельцем схемы alice:

alice@postgres=> c - postgres
You are now connected to database "postgres" as user "postgres".

postgres@postgres=# ALTER SCHEMA alice OWNER TO alice;
ALTER SCHEMA
Time: 0,800 ms

postgres@postgres=# dn+ alice
                 List of schemas
 Name  | Owner | Access privileges | Description
-------+-------+-------------------+-------------
 alice | alice | alice=UC/alice    |
(1 row)

Снова переключимся на Алису и выдадим права Бобу:

postgres@postgres=# c - alice
You are now connected to database "postgres" as user "alice".

alice@postgres=> GRANT CREATE, USAGE ON SCHEMA alice TO bob;
GRANT
Time: 0,905 ms

Попробуем под Бобом снова прочитать табличку Алисы:

alice@postgres=> c - bob
You are now connected to database "postgres" as user "bob".

bob@postgres=> SELECT * FROM alice.t1;
ERROR:  permission denied for table t1
Time: 0,663 ms

Снова не получилось, так как у Боба нет привилегии читать (SELECT) эту таблицу.

Посмотрим привилегии на эту таблицу с помощью команды dp:

bob@postgres=> dp alice.t1
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
 alice  | t1   | table |                   |                   |
(1 row)

Привилегии записаны в колонке “Access privileges“. Здесь пока пусто, это означает что владелец может всё, остальные ничего. А для суперпользователя проверки не выполняются, поэтому он тоже может всё. В этой колонке появится информация, если мы выдадим или заберём привилегии у кого-нибудь для этой таблицы.

Переключимся опять на Алису, под которой выдадим привилегию SELECT на таблицу t1 для Боба. И снова проверим привилегии:

bob@postgres=> c - alice
You are now connected to database "postgres" as user "alice".

alice@postgres=> GRANT SELECT ON t1 TO bob;
GRANT
Time: 0,920 ms

alice@postgres=> dp t1
                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=r/alice         |                   |
(1 row)

Теперь мы видим 2 строки: для Алисы и для Боба. Привилегии сокращаются таким образом:

• a – insert;
• r – select;
• w – update;
• d – delete;
• D – truncate;
• x – reference;
• t – trigger.

Наконец Боб может выполнить запрос:

alice@postgres=> c - bob
You are now connected to database "postgres" as user "bob".

bob@postgres=> SELECT * FROM alice.t1;
 n
---
(0 rows)
Time: 0,470 ms

Но вставить в эту таблицу он ничего не может, так как привилегии INSERT у Боба нет:

bob@postgres=> INSERT INTO alice.t1 VALUES (42);
ERROR:  permission denied for table t1
Time: 0,395 ms

Привилегии на отдельные столбцы

Некоторые привилегии (INSERT и SELECT) можно выдавать на столбцы.

Переключимся на Алису, и дадим бобу INSERT на колонки m и n, и SELECT на колонку m. Затем просмотрим привилегии на таблицу t2:

bob@postgres=> c - alice
You are now connected to database "postgres" as user "alice".

alice@postgres=> GRANT INSERT(m,n) ON t2 TO bob;
GRANT
Time: 0,869 ms

alice@postgres=> GRANT SELECT(m) ON t2 TO bob;
GRANT
Time: 0,432 ms

alice@postgres=> dp t2
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
 alice  | t2   | table |                   | n:               +|
        |      |       |                   |   bob=a/alice    +|
        |      |       |                   | m:               +|
        |      |       |                   |   bob=ar/alice    |
(1 row)

В колонке “Column privileges” видны привилегии для отдельных столбцов. Видно что для столбца n Боб может только вставлять строки, а для столбца m вставлять и читать.

Проверим привилегии Боба на практике:

alice@postgres=> c - bob
You are now connected to database "postgres" as user "bob".

bob@postgres=> INSERT INTO alice.t2(n,m) VALUES (1,2);
INSERT 0 1
Time: 0,929 ms

bob@postgres=> SELECT * FROM alice.t2;
ERROR:  permission denied for table t2
Time: 0,355 ms

bob@postgres=> SELECT m FROM alice.t2;
 m
---
 2
(1 row)
Time: 0,278 ms

Из этого следует, что Боб смог вставить данные и в столбец n и в столбец m. А прочитать всё он не смог, так как нет прав на чтения для столбца n. Зато отдельно столбец m Боб прочитать смог.

Если необходимо Алиса может выдать все привилегии Бобу с помощью слова all:

bob@postgres=> c - alice
You are now connected to database "postgres" as user "alice".

alice@postgres=> GRANT ALL ON t1 TO bob;
GRANT
Time: 0,806 ms

alice@postgres=> dp t1
                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=arwdDxt/alice   |                   |
(1 row)

Теперь Бобу доступны все действия, например, удаление строк:

alice@postgres=> c - bob
You are now connected to database "postgres" as user "bob".

bob@postgres=> DELETE FROM alice.t1;
DELETE 0
Time: 0,709 ms

Но саму таблицу Боб удалить не сможет, так как удалить таблицу может только её владелец или суперпользователь:

bob@postgres=> DROP TABLE alice.t1;
ERROR:  must be owner of table t1
Time: 0,364 ms

Работа с ролью public

Алиса может выдать некоторые привилегии групповой роли public, чтобы эти привилегии появились у всех остальных:

bob@postgres=> c - alice
You are now connected to database "postgres" as user "alice".

alice@postgres=> GRANT UPDATE ON t2 TO public;
GRANT
Time: 0,746 ms

alice@postgres=> dp t2
                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t2   | table | alice=arwdDxt/alice+| n:               +|
        |      |       | =w/alice            |   bob=a/alice    +|
        |      |       |                     | m:               +|
        |      |       |                     |   bob=ar/alice    |
(1 row)

При выполнении команды dp, роль public не пишется, поэтому получается такая запись =w/alice.

Теперь Боб попробует воспользоваться привилегией UPDATE для этой таблице:

alice@postgres=> c - bob
You are now connected to database "postgres" as user "bob".

bob@postgres=> UPDATE alice.t2 SET n = n + 1;
ERROR:  permission denied for table t2
Time: 0,658 ms

Команде UPDATE для того чтобы что-то изменить нужно вначале это прочитать. А привилегии на чтение у Боба нет. Дадим с помощью Алисы ему это право и попробуем выполнить UPDATE ещё раз:

bob@postgres=> c - alice
You are now connected to database "postgres" as user "alice".

alice@postgres=> GRANT SELECT ON t2 TO bob;
GRANT
Time: 0,983 ms

alice@postgres=> dp t2
                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t2   | table | alice=arwdDxt/alice+| n:               +|
        |      |       | =w/alice           +|   bob=a/alice    +|
        |      |       | bob=r/alice         | m:               +|
        |      |       |                     |   bob=ar/alice    |
(1 row)

alice@postgres=> c - bob
You are now connected to database "postgres" as user "bob".

bob@postgres=> UPDATE alice.t2 SET n = n + 1;
UPDATE 1
Time: 1,256 ms

Выдача права передавать привилегии

Переключимся на пользователя postgres и создадим ещё одну роль “Чарли”:

bob@postgres=> c - postgres
You are now connected to database "postgres" as user "postgres".

postgres@postgres=# CREATE ROLE charlie LOGIN;
CREATE ROLE
Time: 0,653 ms

Боб имеет полный набор привилегий для таблицы t1. Но передать эти привилегии не может:

postgres@postgres=# c - bob
You are now connected to database "postgres" as user "bob".

bob@postgres=> dp alice.t1
                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=arwdDxt/alice   |                   |
(1 row)

bob@postgres=> GRANT SELECT ON alice.t1 TO charlie;
WARNING:  no privileges were granted for "t1"
GRANT
Time: 0,723 ms

Алиса может дать Бобу право передачи некоторых привилегий:

bob@postgres=> c - alice
You are now connected to database "postgres" as user "alice".

alice@postgres=> GRANT SELECT, UPDATE ON t1 TO bob WITH GRANT OPTION;
GRANT
Time: 0,873 ms

alice@postgres=> dp t1
                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=ar*w*dDxt/alice |                   |
(1 row)

В выводе выше звёздочки возле привилегий означают, что эти привилегии роль может передавать другим.

Теперь Боб может передать эти привилегии для Чарли и даже дать ему также право передавать эти привилегии другим:

alice@postgres=> c - bob
You are now connected to database "postgres" as user "bob".

bob@postgres=> GRANT SELECT ON alice.t1 TO charlie WITH GRANT OPTION;
GRANT
Time: 1,126 ms

bob@postgres=> GRANT UPDATE ON alice.t1 TO charlie;
GRANT
Time: 0,454 ms

bob@postgres=> dp alice.t1
                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=ar*w*dDxt/alice+|                   |
        |      |       | charlie=r*w/bob     |                   |
(1 row)

Если привилегию выдаёт суперпользователь, то вместо него команда dp выводит владельца:

bob@postgres=> c - postgres
You are now connected to database "postgres" as user "postgres".

postgres@postgres=# GRANT UPDATE ON alice.t1 to charlie;
GRANT
Time: 0,679 ms

postgres@postgres=# dp alice.t1
                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=ar*w*dDxt/alice+|                   |
        |      |       | charlie=r*w/bob    +|                   |
        |      |       | charlie=w/alice     |                   |
(1 row)

Роль может отнимать привилегии только те, которые она и выдавала. Поэтому информация о том, кто что выдавал сохраняется. Таким образом если Боб отнимет привилегии у Чарли, то у Чарли останутся те привилегии, которые ему дала Алиса (или суперпользователь).

А если роль не выдавала какую-то привилегию, то при удалении этой привилегии никакой ошибки не будет, просто привилегии не изменятся. Например, Алиса может попытаться отобрать у Чарли право передачи привилегий на SELECT (GRANT OPTION FOR SELECT). Но это право для Чарли выдавал Боб, а не Алиса. Запрос выполнится без ошибок, но ничего не изменится:

postgres@postgres=# c - alice
You are now connected to database "postgres" as user "alice".

alice@postgres=> REVOKE GRANT OPTION FOR SELECT ON alice.t1 FROM charlie;
REVOKE
Time: 0,976 ms

alice@postgres=> dp alice.t1
                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=ar*w*dDxt/alice+|                   |
        |      |       | charlie=r*w/bob    +|                   |
        |      |       | charlie=w/alice     |                   |
(1 row)

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

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

alice@postgres=> SELECT current_role;
 current_role
--------------
 alice
(1 row)
Time: 0,182 ms

alice@postgres=> REVOKE GRANT OPTION FOR SELECT ON alice.t1 FROM bob;
ERROR:  dependent privileges exist
HINT:  Use CASCADE to revoke them too.
Time: 0,368 ms

Алиса забрать это право может только каскадно, что мы и сделаем:

alice@postgres=> REVOKE GRANT OPTION FOR SELECT ON alice.t1 FROM bob CASCADE;
REVOKE
Time: 0,469 ms

alice@postgres=> dp alice.t1
                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=arw*dDxt/alice +|                   |
        |      |       | charlie=w/bob      +|                   |
        |      |       | charlie=w/alice     |                   |
(1 row)

В выводе можем заметить, что у Боба право передачи для SELECT исчезло, но сама привилегия SELECT осталась. А у Чарли привилегия SELECT и право её передавать исчезли.

Дополнительно под Алисой отнимем привилегию UPDATE у Боба, тоже каскадно:

alice@postgres=> REVOKE UPDATE ON alice.t1 FROM bob CASCADE;
REVOKE
Time: 0,639 ms

alice@postgres=> dp alice.t1
                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t1   | table | alice=arwdDxt/alice+|                   |
        |      |       | bob=ardDxt/alice   +|                   |
        |      |       | charlie=w/alice     |                   |
(1 row)

Работа с функциями

Теперь поработаем с функциями. Пусть Алиса создаст функцию, которая будет считать количество строк в таблице t1 и возвращать это значение (в синтаксис функции можете не вникать):

alice@postgres=> CREATE FUNCTION f() RETURNS integer AS $$ SELECT count(*)::integer FROM t2; $$ LANGUAGE SQL;
CREATE FUNCTION
Time: 1,922 ms

alice@postgres=> SELECT f();
 f
---
 1
(1 row)

Time: 0,397 ms

Теперь попробуем выполнить эту функцию под Бобом:

alice@postgres=> c - bob
You are now connected to database "postgres" as user "bob".

bob@postgres=> SELECT alice.f();
ERROR:  relation "t2" does not exist
LINE 1:  SELECT count(*)::integer FROM t2;
                                       ^
QUERY:   SELECT count(*)::integer FROM t2;
CONTEXT:  SQL function "f" during inlining
Time: 0,437 ms

Боб может выполнить эту функцию, так как по умолчанию псевдо роль public может выполнять любые функции. Но так как прав на табличку t2 у Боба нету, то на команде SELECT из функции мы получили ошибку.

Боб может в своей схеме (public) создать свою табличку t2 и выполнить функцию относительно неё. А у Алисы по умолчанию схема alice, поэтому для неё функция будет работать для другой таблицы. Вот пример:

bob@postgres=> CREATE TABLE t2 (n numeric);
CREATE TABLE
Time: 2,296 ms
bob@postgres=> SELECT alice.f();
 f
---
 0
(1 row)
Time: 0,506 ms

bob@postgres=> c - alice
You are now connected to database "postgres" as user "alice".
alice@postgres=> SELECT alice.f();
 f
---
 1
(1 row)
Time: 0,772 ms

Алиса при создании функции может указать, что при выполнении функции она будет работать от имени владельца (Алисы), а не от имени того кто эту функцию выполняет. Для этого используется опция SECURITY DEFINER при создании функции:

alice@postgres=> CREATE OR REPLACE FUNCTION f() RETURNS integer AS $$ SELECT count(*)::integer FROM t2; $$ SECURITY DEFINER LANGUAGE SQL;
CREATE FUNCTION
Time: 0,994 ms

alice@postgres=> c - bob
You are now connected to database "postgres" as user "bob".
bob@postgres=> SELECT alice.f();
 f
---
 1
(1 row)
Time: 0,751 ms

Так как функция сработала от имени Алиса, то и обратилась она к схеме alice и в ней нашла таблицу.

Такие функции обычно создаются для контролируемого доступа. Например Бобу нельзя читать таблицу, но мы хотим дать ему право посчитать количество строк в таблице. Таким образом через функции даём доступ к объектам.

Теперь отнимем у всех (public) привилегию выполнять функции в схеме alice:

bob@postgres=> c - alice
You are now connected to database "postgres" as user "alice".

alice@postgres=> REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA alice FROM public;
REVOKE
Time: 0,911 ms

alice@postgres=> c - bob
You are now connected to database "postgres" as user "bob".

bob@postgres=> SELECT alice.f();
ERROR:  permission denied for function f
Time: 0,454 ms

Команда выше удалила привилегию EXECUTE для всех существующих функций. Если Алиса создаст новую, то у public сразу появится возможность её выполнить:

bob@postgres=> c - alice
You are now connected to database "postgres" as user "alice".

alice@postgres=> CREATE FUNCTION f_new() RETURNS integer AS $$ SELECT 1; $$ LANGUAGE SQL;
CREATE FUNCTION
Time: 1,285 ms

alice@postgres=> c - bob
You are now connected to database "postgres" as user "bob".

bob@postgres=> SELECT alice.f_new();
 f_new
-------
     1
(1 row)
Time: 0,395 ms

Привилегии по умолчанию

С помощью привилегий по умолчанию можно автоматически удалять привилегии с новых объектах. Например когда Алиса будет создавать какую-нибудь функцию, то нужно чтобы сразу у роли public отнимались привилегии на её выполнение:

bob@postgres=> c - alice
You are now connected to database "postgres" as user "alice".

alice@postgres=> ALTER DEFAULT PRIVILEGES FOR ROLE alice REVOKE EXECUTE ON FUNCTIONS FROM public;
ALTER DEFAULT PRIVILEGES
Time: 1,155 ms

Команда выше делает так, что когда Алиса создает любую функцию, привилегия FUNCTIONS сразу отнимается у public.

Посмотреть такие привилегии по умолчанию можно с помощью команды ddp:

alice@postgres=> ddp
           Default access privileges
 Owner | Schema |   Type   | Access privileges
-------+--------+----------+-------------------
 alice |        | function | alice=X/alice
(1 row)

В выводе выше мы видим что для функций Алисы будут отниматься привилегии (буква X).

Проверим. Удалим функцию и заново её создадим. И попробуем её вызвать под Бобом:

alice@postgres=> DROP FUNCTION f_new();
DROP FUNCTION
Time: 1,282 ms

alice@postgres=> CREATE FUNCTION f_new() RETURNS integer AS $$ SELECT 1; $$ LANGUAGE SQL;
CREATE FUNCTION
Time: 0,657 ms
alice@postgres=> c - bob
You are now connected to database "postgres" as user "bob".
bob@postgres=> SELECT alice.f_new();
ERROR:  permission denied for function f_new
Time: 0,502 ms

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

bob@postgres=> c - alice
You are now connected to database "postgres" as user "alice".
alice@postgres=> ALTER DEFAULT PRIVILEGES FOR ROLE alice GRANT SELECT ON TABLES TO bob;
ALTER DEFAULT PRIVILEGES
Time: 0,977 ms
alice@postgres=> ddp
            Default access privileges
 Owner | Schema |   Type   |  Access privileges
-------+--------+----------+---------------------
 alice |        | function | alice=X/alice
 alice |        | table    | bob=r/alice        +
       |        |          | alice=arwdDxt/alice
(2 rows)

Теперь если Алиса создаст табличку, то у Боба сразу появится привилегия SELECT на табличку:

alice@postgres=> CREATE TABLE t3(n integer);
CREATE TABLE
Time: 2,084 ms

alice@postgres=> dp t3
                             Access privileges
 Schema | Name | Type  |  Access privileges  | Column privileges | Policies
--------+------+-------+---------------------+-------------------+----------
 alice  | t3   | table | bob=r/alice        +|                   |
        |      |       | alice=arwdDxt/alice |                   |
(1 row)

---