在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
角色。