GCP SQL Postgres проблема с привилегиями: не может выполнить запрос с пользователем postgres с сгенерированной базой данных symfony

По умолчанию postgres пользователь в облачном SQL (GCP) Google Cloud Platform (PostgreSQL) не является суперпользователем экземпляра (Ссылка на документы GCP):

Когда вы создаете новый экземпляр Cloud SQL для PostgreSQL … postgres пользователь является частью cloudsqlsuperuser роль и имеет следующие атрибуты (привилегии): CREATEROLE, CREATEDB, а также LOGIN, Не имеет SUPERUSER или же REPLICATION атрибутов.

Атрибут superuser дает пользователю возможность обойти все проверки разрешений и, соответственно, он может перейти в любое место в экземпляре базы данных для чтения данных из любой базы данных (Ссылка на документацию PostgreSQL):

Суперпользователь базы данных обходит все проверки разрешений, кроме права на вход.

В типичном развертывании PostgreSQL вне GCP postgres пользователь действительно суперпользователь. Это отклонение в облачном SQL нарушает обычное соглашение, ожидаемое от Postgres, что postgres пользователь всегда может получить доступ и изменить любой объект в кластере в силу этих прав суперпользователя.

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

Я предполагаю, что вы используете отдельного выделенного пользователя для своего развертывания k8s для подключения к базе данных, а не postgres пользователь. По умолчанию роль этого пользователя будет принадлежать объектам, созданным операцией заполнения схемы. В соответствии с GRANT документация, этот владелец по умолчанию будет иметь полный набор привилегий для объекта:

Если столбец «Права доступа» для данного объекта пуст, это означает, что объект имеет привилегии по умолчанию (т. Е. Его столбец привилегий равен нулю). Привилегии по умолчанию всегда включают все привилегии для владельца …

• (Рекомендуется) Создать отдельную роль, которая может быть разделена между postgres пользователь и любые другие пользователи, с которыми вы входите в базу данных, чтобы заполнить ее схему.

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

  Например, вы можете использовать cloudsqlsuperuser роль для этой цели, из которой все пользователи созданы в консоли и postgres встроенные автоматически участники. Однако я бы порекомендовал создать пользовательскую роль для этой цели:

  CREATE ROLE symfonyapp;
  GRANT symfonyapp TO postgres;
  GRANT symfonyapp TO <your_k8s_application_user>;
  

  Позже, при создании объектов базы данных, убедитесь, что вы принимаете symfonyapp роль, прежде чем сделать это. В консоли запустите:

  SET ROLE symfonyapp;
  

  при входе в систему как пользователь, которому symfonyapp роль была предоставлена. Вам следует просмотреть документацию библиотек, которые вы используете, чтобы выяснить, как установить роль при программном подключении к базе данных.

• Создайте роль, как указано выше, и назначьте ее postgres пользователь. Кроме того, дать роль LOGIN атрибут и установить пароль, который позволяет вам войти в экземпляр базы данных, используя имя роли и пароль напрямую. В этом случае postgres пользователь наследует привилегии роли (например, принадлежащие ему объекты), а возможность входа в систему напрямую устраняет необходимость вызова SET ROLE при первом подключении

• Для объектов, уже созданных, вы можете настроить их владельца в соответствии с пользовательской ролью, используя команду ALTER <TYPE> <name> OWNER TO symfonyapp; например:

  ALTER TABLE mytable OWNER TO symfonyapp;
  

Невозможно напрямую предоставить SUPERUSER атрибут роли к postgres пользователь, так как у вас нет доступа к пользователю с SUPERUSER привилегии для этого! (Только суперпользователи могут делать других пользователей суперпользователями.) Google Cloud SQL для Postgres документация отмечает конкретное исключение поддержки любых функций, которые требуют привилегий суперпользователя, поэтому этот маршрут вам недоступен. Единственным суперпользователем является cloudsqladmin пользователь, созданный по умолчанию и используемый Google для выполнения административных операций на уровне экземпляра от вашего имени; вы мог сбросьте пароль и войдите как эта учетная запись, чтобы предоставить привилегии суперпользователя, но я не рекомендую делать это, поскольку это может нарушить другие управляемые функции.

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

                                        List of roles
Role name     |                         Attributes                         |      Member of
-------------------+------------------------------------------------------------+---------------------
cloudsqladmin     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
cloudsqlagent     | Create role, Create DB                                     | {cloudsqlsuperuser}
cloudsqlreplica   | Replication                                                | {}
cloudsqlsuperuser | Create role, Create DB                                     | {}
postgres          | Create role, Create DB                                     | {cloudsqlsuperuser}

Кроме того, новым базам данных, созданным с помощью вкладки «Базы данных» в облачной консоли, по умолчанию назначается право собственности на cloudsqlsuperuser роль. (Как показано в приведенном выше списке ролей, cloudsqlsuperuser Роль наследуется postgres пользователь.)

                                  List of databases
Name  |       Owner       | Encoding |  Collate   |   Ctype    | Access privileges
--------+-------------------+----------+------------+------------+-------------------
testdb | cloudsqlsuperuser | UTF8     | en_US.UTF8 | en_US.UTF8 |

Соответственно, члены cloudsqlsuperuser роль будет иметь права на создание объектов в базе данных по умолчанию. Однако при этом им по умолчанию будет назначен владелец, который их создал, а не родительская роль:

testdb=> CREATE TABLE sometable (id SERIAL NOT NULL);
CREATE TABLE
testdb=> \dt sometable
List of relations
Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
public | sometable | table | testuser

Если мы позвоним SET ROLE cloudsqlsuperuser перед созданием нашей таблицы владелец по умолчанию будет cloudsqlsuperuser роль, которая позволит postgres и другим членам роли разрешения по умолчанию, назначенные роли по умолчанию:

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

testdb=> SET ROLE cloudsqlsuperuser;
SET
testdb=> CREATE TABLE anothertable (id SERIAL NOT NULL);
CREATE TABLE
testdb=> \dt anothertable;
List of relations
Schema |     Name     | Type  |       Owner
--------+--------------+-------+-------------------
public | anothertable | table | cloudsqlsuperuser
(1 row)

Для производственного использования, как отмечено в разделе «Альтернативы», я рекомендую использовать выделенную роль, а не встроенную cloudsqlsuperuser роль.