Snowflake Postgres Roles

Postgres has its own role-based authentication for managing connections to databases and using databases on a Postgres server. These roles are separate from Snowflake roles. Postgres roles are used for accessing and managing databases, tables, and other objects within Snowflake Postgres instances.

When you create an instance, Snowflake automatically creates two special managed roles for you to use, which are described below.

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

Note

Here and in many other places you will see the terms “role” and “user” used interchangeably in the context of Postgres user management. This is because a Postgres user is simply a role that has the postgres role LOGIN attribute.

Snowflake Postgres managed roles

Snowflake Postgres provides two managed roles that are automatically created at instance creation time.

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:

  • Creating and managing Postgres roles.

  • Creating and managing databases.

  • Managing replication for your Snowflake Postgres instance.

  • Bypassing row-level security (RLS) policies where applicable.

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 password security

Regenerating credentials for Snowflake Postgres managed roles

Credentials for the snowflake_admin and application roles are generated when you create the instance and are displayed only once. These credentials can be regenerated 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. Select your instance.

  3. In the Manage menu at the top right select Regenerate credentials.

  4. Click the Acknowledge & continue button to confirm the action.

Setting passwords for other Postgres roles

Snowflake Postgres instances are configured for scram-sha-256 password authentication. When new passwords are set a scram-sha-258 hash is generated and stored by the server, 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 clear-text passwords are not logged as part of those statements.

Disabling statement logging for CREATE ROLE and ALTER ROLE Postgres DDL commands

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 you run them in using SET LOCAL:

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

Using the psql Postgres client’s \password command

Postgres’s psql (https://www.postgresql.org/docs/current/app-psql.html) 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 that precomputes the entered password’s scram-sha-256 hash and used that in the ALTER ROLE command sent to the server. To use this method you should first create new users without a password and then set each user’s password with the psql’s \password meta-command.

postgres=# CREATE ROLE mynewrole LOGIN;
CREATE ROLE

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

If log_statement is set to value other than ‘none’ then the log entry for ALTER ROLE command sent by psql for the above \password command will have the calculated scram-sha-256 hash rather than the actual clear text password. This method could be combined with disabling log_statement completely for 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
Copy

Leaked Password Protection

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 email notification 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 password for the noted role(s) as described above.

Role limitations

In Snowflake Postgres, certain operations are reserved for the service itself and cannot be performed by any customer-managed role, including snowflake_admin. Examples include:

  • Changing protected server-level configuration parameters that are managed by Snowflake

  • Modifying or disabling core Snowflake-managed components or extensions

  • Accessing or altering Snowflake-managed system databases or schemas used by the service

  • Accessing or altering the Snowflake Postgres instance filesystem

  • Direct modification of system catalog tables

  • Creation of other superusers

  • Creation of more than 64 roles in the instance

  • Creation of more than 32 databases in the instance

  • Execution of the ALTER SYSTEM command

  • Access to 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 will receive an error indicating that it is not permitted in Snowflake Postgres.

Language: English