Use parameterized secure views

MySQL | PostgreSQL | SQL Server

This document describes how to use parameterized secure views in Cloud SQL for PostgreSQL, which let you limit data access based on application-specific named parameters, like application user credentials. Parameterized secure views improve security and access control by extending the functionality of PostgreSQL views. These views also mitigate the risks of running untrusted queries from applications by enforcing a number of restrictions automatically on any query that is executed.

For more information, see the parameterized secure views overview and the parameterized secure views tutorial.

Before you begin

This document assumes that you created a Cloud SQL for PostgreSQL instance.

Before you can use parameterized secure views, you must do the following:

Enable the cloudsql.enable_parameterized_views database flag for your Cloud SQL instance. This flag change requires a database restart. For more information, see Configure database flags.
Use Cloud SQL Studio or psql to create the parameterized_views extension in any database where a parameterized view is created:
```
-- Requires cloudsql.enable_parameterized_views set to on
CREATE EXTENSION parameterized_views;
```
When the extension is created, a schema named parameterized_views is also created by the system so that the APIs are contained in that schema's namespace, and so that the APIs don't conflict with existing APIs.

Create a parameterized secure view

To create a parameterized secure view, follow these steps:

Run the CREATE VIEW DDL command using the security_barrier option:
```
CREATE VIEW VIEW_NAME WITH (security_barrier) AS
SELECT COLUMN_NAME, COLUMN_NAME_N
FROM TABLE_NAME ALIAS
WHERE CONDITION;
```
Replace the following:
- VIEW_NAME: the name of the parameterized secure view
- TABLE_NAME: the name of the table to use in the parameterized secure view
- ALIAS: the alias for the table name to use in the parameterized secure view
- COLUMNNAME or COLUMN_NAMEN: the name of the table column or columns to use in the parameterized secure view
- CONDITION: the condition statement used to restrict application users so that they can only view the rows they're allowed to access. Add required parameters using the $@PARAMETER_NAME syntax in the WHERE clause. A common use case is checking the value of a column using WHERE COLUMN = $@PARAMETER_NAME.
  
  $@PARAMETER_NAME indicates a named view parameter. Its value is provided when you use the execute_parameterized_query API. Named view parameters have the following requirements:
  - Named view parameters must begin with a letter (a-z) or an underscore (_).
  - Subsequent characters can be letters, underscores, or digits (0-9).
  - Named view parameters are case sensitive. For example, $@PARAMETER_NAME is interpreted differently than $@parameter_name.
  The following is an example of creating a parameterized secure view that uses a named view parameter:
```
CREATE VIEW user_specific_items WITH (security_barrier) AS
SELECT item_id, item_name, description, owner_id
FROM items t
WHERE owner_id = $@app_user_id;
```
Grant SELECT on the view to any database user that is allowed to query the view.
Grant USAGE on the schema that contains the tables defined in the view to any database user that is allowed to query the view.

For more information, see Secure and control access to application data using parameterized secure views (Tutorial).

Configure security for your application

To configure security for your applications using parameterized secure views, follow these steps:

Create the parameterized secure view as an administrative user. This is a Cloud SQL database user that performs administrative operations for the application.
Create a new database role for executing queries against parameterized secure views. This is a Cloud SQL database role that the application uses to connect and sign into the database.
1. Grant the new role permissions to the secure views, which typically includes SELECT privileges to the views and USAGE on the schemas.
2. Limit the objects that this role can access to the minimum required set of public functions and objects that the application needs. Avoid providing access to schemas and tables that aren't public.
When you query the views, the application provides the values of the required view parameters, which are tied to the application user identity.

For more information, see Create and manage users.

Query a parameterized secure view

To query a parameterized secure view, use one of the following options:

JSON-based: Use this API to run the query in one-shot and return JSON rows.
CURSOR-based: Use this API when you have longer running queries or when you have large queries and you want to fetch the result in batches.
PREPARE .. AS RESTRICTED statement: Use PREPARE .. AS RESTRICTED to define the query plan and then run EXECUTE ... WITH VIEW PARAMETERS (...) to execute it with specific parameters for the view.

JSON API

Run the execute_parameterized_query() function, which has the following syntax:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
);

The function returns a table of JSON objects. Each row in the table is equivalent to the row_to_json() value of the original query result row.

Using this API limits the size of the result set by size (in KB) and by the number of rows. You can configure these limits by using parameterized_views.json_results_max_size and parameterized_views.json_results_max_rows.

CURSOR API

Run the execute_parameterized_query() function with a cursor name, which creates and returns a transaction-scoped CURSOR:

-- Must be in a transaction block
BEGIN;

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    cursor_name => CURSOR_NAME,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
);

FETCH ALL FROM CURSOR_NAME;

END;

Prepared statements

The prepared statements method lets you prepare a query plan once and then execute it multiple times with different values for both the query's positional parameters and the view's named parameters.

To use a prepared statement, do the following:

Create the prepared statement.
```
-- Prepare the statement
PREPARE PREPARED_STATEMENT_NAME (QUERY_PARAM_TYPE_1, QUERY_PARAM_TYPE_N)
AS RESTRICTED SQL_QUERY;
```
Replace the following:
- PREPARED_STATEMENT_NAME: the name of the prepared statement
- QUERY_PARAM_TYPE_N: the data type of the query parameter, such as TEXT
- SQL_QUERY: the SQL query to execute as part of the prepared statement with the given value or values
Execute the prepared statement.
```
-- Execute the statement with query parameters and view parameters
EXECUTE PREPARED_STATEMENT_NAME (QUERY_VALUE_1, QUERY_VALUE_N)
WITH VIEW PARAMETERS (PARAMETER_NAME_1 := 'PARAMETER_VALUE_1', PARAMETER_NAME_N := 'PARAMETER_VALUE_N');
```
Replace the following:
- PREPARED_STATEMENT_NAME: the name of the prepared statement.
- QUERY_VALUE_N: the value or values to provide as a parameter or parameters to the SQL query
- PARAMETER_NAME_N: the name of the named view parameter or parameters that you defined when you created the parameterized secure view. You create the named view parameter from the column of the table.
- PARAMETER_VALUE_N: the value or values for the named view parameter which restricts the parameterized secure view to the rows associated with the value.

Clean up the prepared statement.

-- Cleanup
DEALLOCATE PREPARED_STATEMENT_NAME>;

The following example uses a parameterized secure view named user_specific_items which requires the named view parameter $@app_user_id.

-- Prepare a query with a positional parameter $1 for the item_name pattern
PREPARE get_items_by_name (TEXT) AS RESTRICTED
SELECT item_id, item_name FROM user_specific_items
WHERE item_name LIKE $1;

After you create the prepared statement, you can execute the prepared statement multiple times with and assign different values for both the query and the named view parameter.

For example, the first query execution:

-- Execute for user 123, looking for items like '%Laptop%'
EXECUTE get_items_by_name ('%Laptop%')
WITH VIEW PARAMETERS (app_user_id := '123');

The second query execution:

-- Execute for user 456, looking for items like '%Book%'
EXECUTE get_items_by_name ('%Book%')
WITH VIEW PARAMETERS (app_user_id := '456');

The WITH VIEW PARAMETERS clause is where the named view parameters (123, 456) for the parameterized secure view are supplied, separate from the positional parameters for the prepared query (%Laptop%, %Book%).

Finally, clean up the prepared statement.

-- Clean up the get_item_by_name prepared statement
DEALLOCATE get_items_by_name;

Enforced restrictions on queries

The following lists the set of restricted operations for queries that you run using the options described in Query a parameterized secure view:

Read-only: Only read-only SELECT statements are allowed. DML (INSERT, UPDATE, DELETE) and DDL (CREATE, ALTER) are prohibited.
No Nesting: Recursive calls to execute_parameterized_query are prohibited.
Extension Limits: Certain extensions that start new background sessions (e.g., dblink, pg_cron) are disallowed.
EXPLAIN statements are disallowed to prevent potential information leakage using query plans.

List all parameterized views

Use the parameterized_views.all_parameterized_views view to list all parameterized views (those containing at least one named parameter $@...).

SELECT * FROM parameterized_views.all_parameterized_views;