GCP SQL Postgres权限问题：使用生成的Symfony数据库无法以postgres用户运行查询。

Question

我正在努力解决使用Google Cloud Platform的Cloud SQL组件时遇到的问题。我的技术栈包括在Google Kubernetes Engine（GKE）部署中托管我的应用程序，使用Cloud SQL代理sidecar连接Pod内的数据库。后端是Symfony项目。

我按照以下步骤创建和填充数据库（但没有成功）：

1. 创建Cloud SQL Postgres实例 2. 添加代理到k8s容器以使用所有凭据连接到Cloud SQL实例，如GCP文档所述 3. 进入我的Symfony（phpfpm）pod并运行命令“php bin/console doctrine:schema:update --force”以更新架构。查询在数据库中执行，因此创建了架构等。 4. 我尝试从GCP内的SQL控制台连接打开数据库，并尝试使用postgres用户执行简单的“select * from foo;”查询。响应为“权限不足：7 ERROR: permission denied for relation”

如何使用postgres用户查询数据库中的数据？

编辑：

关于用户，我的情况是：

     Role name     |                         Attributes                         |           Member of           
-------------------+------------------------------------------------------------+-------------------------------
 acando14          | Create role, Create DB                                     | {cloudsqlsuperuser,proxyuser}
 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,acando14}
 proxyuser         | Create role, Create DB                                     | {cloudsqlsuperuser}

我在表格中遇到了这种情况：

              List of relations
 Schema |      Name       | Type  |  Owner   
--------+-----------------+-------+----------
 public | article         | table | acando14

如果我使用postgres用户登录我的数据库，symfony就可以工作：

symfony => select * from article;
 id | model_id | code | size 
----+----------+------+------
(0 rows)

但如果我使用服务器来执行代码，响应是：

SQLSTATE [42501]：权限不足：7 ERROR：拒绝对关系员工的许可 对于PDOException（code：42501）：SQLSTATE [42501]： 权限不足：7 ERROR：拒绝对关系..的许可

另一个问题是我没有使用命令生成所有表格，但我必须通过执行所有查询来生成它，所以很奇怪...

谢谢，问候

- Alessandro Candon

---

在 k8s 中托管的服务使用哪个用户连接数据库？ - Cosmic Ossifrage

使用代理用户... - Alessandro Candon

Answer 1

在Google Cloud Platform（GCP）的Cloud SQL（PostgreSQL）中，默认的postgres用户不是实例的超级用户（详见GCP文档链接）。当您创建新的Cloud SQL for PostgreSQL实例时，postgres用户属于cloudsqlsuperuser角色，并具有以下属性（特权）：CREATEROLE、CREATEDB和LOGIN，但不具备SUPERUSER或REPLICATION属性。

超级用户特权授予用户绕过所有权限检查的能力，因此可以在数据库实例中任意访问数据（详见PostgreSQL文档链接）。

在GCP之外的典型PostgreSQL部署中，postgres用户确实是超级用户。这种偏离正常约定的行为在Cloud SQL中破坏了Postgres的预期，即postgres用户始终具有超级用户权限，从而可以访问和修改集群中的任何对象。

如果没有这个权限，则需要在创建数据库对象时更加谨慎地使用角色，在尝试交互式访问Postgres shell或在应用程序中进行编程时使用角色。

我假设您正在使用单独的、专用的用户来连接数据库进行k8s部署，而不是postgres用户。默认情况下，该用户的角色将拥有模式填充操作创建的对象。根据GRANT文档，该所有者将默认具有该对象的全部权限。

• (Recommended) Create a dedicated role which can be shared between the postgres user and whatever other user(s) you log into the database with to populate its schema.

  Configure the operation which populates the database objects in the schema creation operation to set its role prior to creating the objects to this shared role, such that all users have the ability to access, manage and view these objects. By default, new roles have the INHERIT attribute set, which means attempts to access the objects created by the role will succeed for members of the role in future.

  For example, you could use the cloudsqlsuperuser role for this purpose, of which all users created in the console and the postgres built-in are automatically members. However, I would recommend creating a custom role for the purpose:

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

  Later, when creating database objects, ensure you assume the symfonyapp role before doing so. At the console, run:

  SET ROLE symfonyapp;
  

  when logged in as a user to which the symfonyapp role has been granted. You should review the documentation of the libraries you are using to ascertain how to set the role when connecting to the database programmatically.

• Create a role as above, and assign it to the postgres user. Additionally, give the role the LOGIN attribute and set a password, which permits you to log into the database instance using the role name and a password directly. In this case, the postgres user inherits privileges of the role (such as objects it owns), and the ability to log in directly obviates the need to call SET ROLE on first connection.

• For objects already created, you can adjust their ownership to your custom role using the command ALTER <TYPE> <name> OWNER TO symfonyapp; for example:

  ALTER TABLE mytable OWNER TO symfonyapp;
  

直接将 SUPERUSER 角色属性授予 postgres 用户是不可能的，因为您没有访问具有 SUPERUSER 特权的用户来执行此操作！（只有超级用户可以使其他用户成为超级用户。）Google Cloud SQL for 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}

此外，在Cloud Console中使用“数据库”选项卡创建的新数据库默认会分配所有权给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角色。

- Cosmic Ossifrage

---

非常感谢！我会尝试您的解决方案... 如果我使用您的第一个解决方案，我会收到这个错误：ERROR: 必须是超级用户才能更改超级用户... 我该如何解决？ - Alessandro Candon

当然，你无法这样做，因为postgres本身不是超级用户，而且你也没有访问任何超级用户的权限。（只有超级用户才能分配超级用户权限，在Cloud SQL中唯一具备此类权限的用户是cloudsqladmin，最好不要重置其密码。） - Cosmic Ossifrage

我也尝试了你的第二个方案，但它不起作用...代理用户没有权限... - Alessandro Candon

你是否尝试创建新的角色？您要将该角色分配给哪个用户？ - Cosmic Ossifrage