Packages Policies¶

Step 1: Create a Packages Policy Admin Custom Role¶

Create a custom role that allows users to create and manage packages policies. Throughout this topic, the example custom role is named policy_admin, although the role could have any appropriate name.

If the custom role already exists, continue to the next step.

Otherwise, create the policy_admin custom role.

use role useradmin;

create role policy_admin;

Step 2: Grant Privileges to the policy_admin Custom Role¶

If the policy_admin custom role does not already have the following privileges, grant these privileges as shown below:

• USAGE on the database and schema that will contain the packages policy.

• CREATE PACKAGES POLICY on the schema that will contain the packages policy.

• APPLY PACKAGES POLICY on the account.

use role securityadmin;

grant usage on database yourdb to role policy_admin;

grant usage, create packages policy on schema yourdb.yourschema to role policy_admin;

grant apply packages policy on account to role policy_admin;

Step 3: Create a New Packages Policy¶

Using the policy_admin custom role, create a new packages policy, with a language, allowlist, and blocklist specified. ALLOWLIST, BLOCKLIST, ADDITIONAL_CREATION_BLOCKLIST, and COMMENT are optional parameters. By default, the allowlist value is ('*'), and the blocklist value is ().

If a package is specified in both the allowlist and the blocklist, then the blocklist takes precedence. You must explicitly add the Python runtime version in the allowlist and you must also explicitly add all packages and underlying dependencies of a parent package to the allowlist.

You can specify a particular package version or a range of versions by using these version specifiers in the allowlist or blocklist: : ==, <=, >=, <,or >. For example, numpy>=1.2.3. You can use wildcards, such as, numpy==1.2.*, which means any micro version of numpy 1.2.

USE ROLE policy_admin;

CREATE PACKAGES POLICY yourdb.yourschema.packages_policy_prod_1
  LANGUAGE PYTHON
  ALLOWLIST = ('numpy', 'pandas==1.2.3', ...)
  BLOCKLIST = ('numpy==1.2.3', 'bad_package', ...)
  ADDITIONAL_CREATION_BLOCKLIST = ('bad_package2', 'bad_package3', ...)
  COMMENT = 'Packages policy for the prod_1 environment'
;

Where:

yourdb.yourschema.packages_policy_prod_1

The fully qualified name of the packages policy.

LANGUAGE PYTHON

The language that this packages policy will apply to.

ALLOWLIST = ('numpy', 'pandas==1.2.3', ...)

The allowlist for this packages policy. This is a comma-separated string with package specs.

BLOCKLIST = ('numpy==1.2.3', 'bad_package', ...)

The blocklist for this packages policy. This is a comma-separated string with package specs.

ADDITIONAL_CREATION_BLOCKLIST = ('bad_package2', 'bad_package3', ...)

Specifies a list of package specs that are blocked at creation time. To unset this parameter, specify an empty list. If the ADDITIONAL_CREATION_BLOCKLIST is set, it is appended to the basic BLOCKLIST at the creation time. For temporary UDFs and anonymous stored procedures, the ADDITIONAL_CREATION_BLOCKLIST is appended to the BLOCKLIST at both creation and execution time.

COMMENT = 'Packages policy for the prod_1 environment'

A comment specifying the purpose of the packages policy.

In the example above, the blocklist applied for the creation time will be the ADDITIONAL_CREATION_BLOCKLIST plus the BLOCKLIST so the blocked packages will be numpy==1.2.3, bad_package, bad_package2 and bad_package3. The blocklist applied for the execution will be: numpy==1.2.3 and bad_package. For temporary UDFs and anonymous stored procedures, the blocklist containing numpy==1.2.3, bad_package, bad_package2 and bad_package3 will be applied at both creation and execution time.

To get a list of the dependencies of a Python package, use the SHOW_PYTHON_PACKAGES_DEPENDENCIES function. The first parameter is the Python runtime version you are using and the second is a list of the packages to show dependencies for. For example, to show the dependencies of the numpy package, use this command.

USE ROLE ACCOUNTADMIN;

select SNOWFLAKE.SNOWPARK.SHOW_PYTHON_PACKAGES_DEPENDENCIES('3.8', ['numpy']);

The result is a list of the dependencies and their versions.

['_libgcc_mutex==0.1', '_openmp_mutex==5.1', 'blas==1.0', 'ca-certificates==2023.05.30', 'intel-openmp==2021.4.0',
'ld_impl_linux-64==2.38', 'ld_impl_linux-aarch64==2.38', 'libffi==3.4.4', 'libgcc-ng==11.2.0', 'libgfortran-ng==11.2.0',
'libgfortran5==11.2.0', 'libgomp==11.2.0', 'libopenblas==0.3.21', 'libstdcxx-ng==11.2.0', 'mkl-service==2.4.0',
'mkl==2021.4.0', 'mkl_fft==1.3.1', 'mkl_random==1.2.2', 'ncurses==6.4', 'numpy-base==1.24.3', 'numpy==1.24.3',
'openssl==3.0.10', 'python==3.8.16', 'readline==8.2', 'six==1.16.0', 'sqlite==3.41.2', 'tk==8.6.12', 'xz==5.4.2', 'zlib==1.2.13']

To show the dependencies of Python 3.8 within Snowpark environment, call the function without specifying any packages.

select SNOWFLAKE.SNOWPARK.SHOW_PYTHON_PACKAGES_DEPENDENCIES('3.8', []);

If you want to know which packages a function is using, you can use DESCRIBE FUNCTION to print them out. This is an alternative way to identify all of the dependencies of a package. To do this, create a function and in the package specification, provide the top level packages. Next, use DESCRIBE FUNCTION to get a list of all of the packages and their dependencies. You can copy and paste this list into the package allowlist. Note that the packages policy must be temporarily unset or some packages might be blocked. The following example shows how to find the dependencies for the ‘snowflake-snowpark-python’ package.

create or replace function my_udf()
returns string
language python
packages=('snowflake-snowpark-python')
runtime_version=3.10
handler='echo'
as $$
def echo():
  return 'hi'
$$;

describe function my_udf();

If you want to show all of the packages and versions that are available, query the INFORMATION_SCHEMA.PACKAGES view.

select * from information_schema.packages;

If you want to see the current set of packages you are using, you can use this SQL statement.

-- at the database level

CREATE OR REPLACE VIEW USED_ANACONDA_PACKAGES
ASSELECT FUNCTION_NAME, VALUE PACKAGE_NAME
FROM (SELECT FUNCTION_NAME,PARSE_JSON(PACKAGES)
PACKAGES FROM INFORMATION_SCHEMA.FUNCTIONS
WHERE FUNCTION_LANGUAGE='PYTHON') USED_PACKAGES,LATERAL FLATTEN(USED_PACKAGES.PACKAGES);

-- at the account level

CREATE OR REPLACE VIEW ACCOUNT_USED_ANACONDA_PACKAGES
AS SELECT FUNCTION_CATALOG, FUNCTION_SCHEMA, FUNCTION_NAME, VALUE PACKAGE_NAME
FROM (SELECT FUNCTION_CATALOG, FUNCTION_SCHEMA, FUNCTION_NAME,PARSE_JSON(PACKAGES)
PACKAGES FROM SNOWFLAKE.ACCOUNT_USAGE.FUNCTIONS
WHERE FUNCTION_LANGUAGE='PYTHON') USED_PACKAGES,LATERAL FLATTEN(USED_PACKAGES.PACKAGES);

To get a list of third-party packages that are available from Anaconda, use the GET_ANACONDA_PACKAGES_REPODATA function. The parameter is the architecture, which can be: linux-64, linux-aarch64, osx-64, osx-arm64, win-64, or noarch.

For example, to show the list of third-party packages from Anaconda for the linux-64 archtecture, use this command.

USE ROLE ACCOUNTADMIN;

select SNOWFLAKE.SNOWPARK.GET_ANACONDA_PACKAGES_REPODATA('linux-64');

Step 4: Set the Packages Policy on an Account¶

Using the policy_admin custom role, set the policy on an account with the ALTER ACCOUNT command.

use role policy_admin;

alter account set packages policy yourdb.yourschema.packages_policy_prod_1;

alter account set packages policy yourdb.yourschema.packages_policy_prod_2 force;

If you want to see which policy is active on the account, you can use this SQL statement.

select * from table(information_schema.policy_references(ref_entity_domain=>'ACCOUNT', ref_entity_name=>'<your_account_name>'))

The result of this query will display a column with the name POLICY_STATUS.

Later, if you want to unset the package policy on your account, use this SQL statement.

alter account unset packages policy;

Privileges Required to Execute DDL Commands¶

The following table summarizes the relationship between the packages policy DDL operations and their necessary privileges.