Snowflake Postgres 角色

Postgres 拥有基于角色的身份验证机制，用于管理数据库连接及 Postgres 服务器上的数据库操作。这些角色与 Snowflake 角色相互独立。Postgres 角色用于访问和管理 Snowflake Postgres 实例内的数据库、表及其他对象。

创建实例时，Snowflake 会自动为您创建两个特殊管理角色，具体说明如下。

For more information about managing Postgres roles, see the Postgres documentation (https://www.postgresql.org/docs/current/user-manag.html).

Note

在这里和许多其他地方，您会看到术语“角色”和“用户”在 Postgres 用户管理上下文中交替使用。这是因为 Postgres 用户本质上就是具有 Postgres 角色 LOGIN 属性的角色。

Snowflake Postgres 托管角色

Snowflake Postgres 在创建实例的同时会自动创建两个托管角色。

The snowflake_admin role

The snowflake_admin role is a high-privilege Postgres role used to administer your Snowflake Postgres instance. It is not a full Postgres superuser; some operations remain restricted and are managed by Snowflake. However, it has elevated privileges that include:

  • 创建和管理 Postgres 角色。
  • 创建和管理数据库。
  • 管理 Snowflake Postgres 实例的复制。
  • 绕过行级安全性 (RLS) 策略（如适用）。

In addition, snowflake_admin is a member of several Postgres built-in roles that grant monitoring and operational capabilities, including:

  • pg_signal_backend
  • pg_use_reserved_connections
  • pg_create_subscription
  • pg_read_all_settings
  • pg_read_all_stats
  • pg_stat_scan_tables
  • pg_monitor
  • snowflake_admin_group

The application role

The application role is a non-superuser role that by default has permissions to create objects in the postgres database. New permissions or ownership for this role should be granted by the snowflake_admin role.

Postgres 密码安全

为 Snowflake Postgres 托管角色重新生成凭据

Credentials for the snowflake_admin and application roles are generated when you create the instance and are displayed only once. You can regenerate these credentials at any time, invalidating the existing credentials.

From the dashboard you can regenerate the credentials for your instance’s snowflake_admin role.

  1. In the navigation menu, select Postgres.
  2. 选择您的实例。
  3. In the Manage menu at the top right select Regenerate credentials.
  4. Click the Acknowledge & continue button to confirm the action.

为其他 Postgres 角色设置密码

Snowflake Postgres instances are configured for scram-sha-256 password authentication. When new passwords are set, the server generates and stores a scram-sha-256 hash, but when the Postgres log_statement (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-STATEMENT) parameter is set to any value other than none, then CREATE ROLE and ALTER ROLE DDL commands are fully logged to the Postgres server log. Therefore, you should make sure that clear-text passwords are not logged as part of those statements.

禁用 CREATEROLE 和 ALTERROLE Postgres DDL 命令的语句日志记录

The simplest way to prevent clear-text passwords used in CREATE ROLE and ALTER ROLE DDL statements from appearing in the Postgres server log is to disable the log_statement parameter for the transaction that you run them in. Do so by using SET LOCAL:

BEGIN;
SET LOCAL log_statement = 'none';
CREATE USER mynewrole PASSWORD 'mynewpassword';
COMMIT;

Using the psql Postgres client’s \password command

The Postgres psql (https://www.postgresql.org/docs/current/app-psql.html) client program has a \password (https://www.postgresql.org/docs/current/app-psql.html) meta-command that can be used to change the password for existing users. The \password meta-command precomputes the entered password’s scram-sha-256 hash and uses that in the ALTER ROLE command that is sent to the server. To use this method, first create new users without a password, and then set each user’s password with the psql \password meta-command.

postgres=# CREATE ROLE mynewrole LOGIN;
CREATE ROLE

postgres=# \password mynewrole
Enter new password for user "mynewrole":
Enter it again:

If log_statement is set to a value other than 'none', then the log entry for ALTER ROLE command sent by psql for the above \password command has the calculated scram-sha-256 hash instead of the actual clear-text password. You can combine this method with disabling log_statement completely, as described above, to prevent even that hash from appearing in the Postgres log:

postgres=# CREATE ROLE mynewrole LOGIN;
CREATE ROLE

postgres=# BEGIN;
BEGIN

postgres=# SET LOCAL log_statement = 'none';
SET

postgres=# \password mynewrole
Enter new password for user "mynewrole":
Enter it again:

postgres=# COMMIT;
COMMIT

泄露密码保护

Leaked password protection is provided for roles on Snowflake Postgres instances. Discovery and notification work as described in our main Leaked password protection. When Snowflake discovers a leaked password for one of your Snowflake Postgres roles:

  • The role is added to the special snowflake_nologin Postgres group role to prevent future logins with it.
  • 该角色的所有现有连接都将终止。
  • The email notification you receive will have “Urgent - Snowflake Postgres Role(s) Password Reset to Prevent Unauthorized Access” for its Subject.

Should you receive this email, you should immediately securely update the role’s password as described above. When regenerating credentials for managed roles they are automatically removed from the snowflake_nologin Postgres role group. For non-managed roles, after updating the role’s password they can be removed from the snowflake_nologin group role by running this Postgres SQL with the snowflake_admin role:

REVOKE snowflake_nologin FROM {rolename};

角色限制

In Snowflake Postgres, certain operations are reserved for the service itself and can’t be performed by any customer-managed role, including snowflake_admin.

受限制的操作示例包括：

  • Logging in with superuser roles such as postgres or snowflake_superuser, or assuming such roles by using SET ROLE.
  • 正在创建其他超级用户。
  • 执行 ALTER SYSTEM 命令。
  • 更改由 Snowflake 管理的受保护服务器级配置参数。
  • 修改或禁用 Snowflake 管理的核心组件或扩展。
  • 访问或更改服务使用的 Snowflake 管理的系统数据库或架构。
  • 访问或更改 Snowflake Postgres 实例文件系统。
  • 直接修改系统目录表。
  • 在实例中创建超过 64 个角色。
  • 在实例中创建超过 32 个数据库。
  • Accessing the Postgres generic file access functions (https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-GENFILE) that permit filesystem access.

The Snowflake Postgres extension may introduce further restrictions on what both snowflake_admin and application can do within an instance. These extension-specific limitations may evolve over time and will be documented with the corresponding extension behavior. If an operation is blocked, you receive an error indicating that it isn’t permitted in Snowflake Postgres.