Является ли программное обеспечение бессмертным? И можно ли скрыть столбцы в Postgres?

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

Postgres уже более 30 лет, он не намного моложе меня. Что стало неожиданностью! Представьте себе, как эти вещи будут выглядеть, когда им исполнится 80 лет. Будут ли они увядать и разрушаться, как наши тела? Останутся ли они бессмертными, их кусочки будут вечно парить в космосе? Или они будут чем-то средним, как организации, как цивилизации или культуры – со сроком жизни в тысячелетия, но постоянно меняющиеся и всегда на грани исчезновения.

Но почему я на сайте Postgres? Правильно, я хочу создать пользователя, который не сможет видеть некоторые столбцы таблицы.

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

Postgres родился в Беркли в 1986 году. Изначально у него был свой собственный язык запросов, но по мере роста он научился говорить на SQL, и этот брак заставил его сменить название на PostgreSQL. Но мы пока будем называть ее Postgres, как старого друга.

За 30 лет своего существования он вырос до 1,5 млн строк языка C (что в среднем составляет около 140 строк в день). Что более важно для нашей сегодняшней цели, он обзавелся надежной системой контроля доступа и безопасностью на уровне столбцов (и даже строк).

Однако учетные записи пользователей в Postgres тоже немного запутаны. Мой ответ на Stack Overflow, получивший наибольшее количество голосов, посвящен именам пользователей Postgres, просто чтобы дать понять, что это не только у меня.

Часть путаницы возникает потому, что Postgres создает и учетную запись пользователя (Postgres), и базу данных с тем же именем, что и имя учетной записи пользователя ОС, которая запустила сервер. И по умолчанию различные инструменты Postgres CLI (например, psql) будут пытаться подключиться, используя имя пользователя (Postgres) и имя базы данных, совпадающее с именем учетной записи пользователя ОС, которая их запускает. Это удобно, когда человек понимает, что происходит, но поначалу это также сбивает с толку.

Справедливости ради следует отметить, что концептуально это довольно красиво. В Postgres есть единое понятие “роль” для управления контролем доступа (вместо отдельных пользователей и групп). Объекты базы данных, такие как таблицы, принадлежат ролям, и владелец может предоставлять привилегии (например, SELECT, UPDATE) на эти объекты другим ролям. Кроме того, роль может быть включена в другую роль, что транзитивно наделяет ее привилегиями другой роли. Таким образом, это все роли.

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

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

$ psql -q -c 'CREATE ROLE myrole WITH LOGIN CREATEDB CREATEROLE;'

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

$ psql --username myrole -q -c 'CREATE DATABASE mydb;'

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

$ psql --username myrole --dbname mydb

Давайте создадим таблицу с двумя столбцами и вставим пару строк.

mydb=> CREATE TABLE mytable (myint integer, mytext text);
CREATE TABLE
mydb=> INSERT INTO mytable VALUES (1, 'a');
INSERT 0 1
mydb=> INSERT INTO mytable VALUES (2, 'b');
INSERT 0 1

Теперь давайте создадим новую роль. Попробуем ограничить привилегии этой роли.

mydb=> CREATE ROLE mylimitedrole WITH LOGIN;
CREATE ROLE

Не перезапуская psql, давайте изменим соединение для использования новой роли.

mydb=> c
You are now connected to database "mydb" as user "myrole".
mydb=> c mydb mylimitedrole
You are now connected to database "mydb" as user "mylimitedrole".

Попробуем сделать запрос к mytable.

mydb=> SELECT * FROM mytable;
ERROR:  permission denied for table mytable

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

mydb=> c mydb myrole
You are now connected to database "mydb" as user "myrole".
mydb=> GRANT SELECT ON mytable TO mylimitedrole;
GRANT
mydb=> c mydb mylimitedrole
You are now connected to database "mydb" as user "mylimitedrole".
mydb=> SELECT * FROM mytable;
 myint | mytext
-------+--------
     1 | a
     2 | b
(2 rows)

Круто. Обратите внимание, что мы предоставили SELECT только привилегию mylimitedrole. Аналогично, мы можем предоставить ему INSERT, UPDATE, DELETE и т.д., если потребуется.

Однако мы хотели ограничить доступ к столбцу mytext. Для этого нам нужно назначить более детальную привилегию SELECT, называя конкретные столбцы. Глядя на документацию, мы можем придумать что-то вроде REVOKE SELECT (mytext) ON mytable FROM mylimitedrole;, однако это не сработает (и в документации это указано [6]).

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

Раздражает то, что документация не говорит нам, что мы должны делать вместо этого.

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

mydb=> c mydb myrole
You are now connected to database "mydb" as user "myrole".
mydb=> REVOKE SELECT ON mytable FROM mylimitedrole;
REVOKE
mydb=> GRANT SELECT (myint) ON mytable TO mylimitedrole;
GRANT
mydb=> c mydb mylimitedrole
You are now connected to database "mydb" as user "mylimitedrole".
mydb=> SELECT * FROM mytable;
ERROR:  permission denied for table mytable
mydb=> SELECT myint FROM mytable;
 myint
-------
     1
     2
(2 rows)

Похоже, это делает свою работу.

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

До тех пор, счастливого SQueaLing! 🧑🔬

Если вы хотите узнать больше о нашем опыте работы с Postgres, следите за нами в Twitter.

А если вы хотите потусоваться с группой инженеров, создающих сервис хранения фотографий e2ee, заходите поздороваться на Discord.

Ссылки:

1. https://www.postgresql.org/about/
2. https://www.postgresql.org/docs/12/tutorial-createdb.html
3. https://www.postgresql.org/docs/12/user-manag.html
4. https://www.postgresql.org/docs/12/sql-createrole.html
5. https://www.postgresql.org/docs/12/ddl-priv.html
6. https://www.postgresql.org/docs/12/sql-grant.html

Изображение с обложки: https://twitter.com/PostgreSQL/header_photo