Manage application data security using AlloyDB parameterized secure views

Stay organized with collections Save and categorize content based on your preferences.

This document describes how to use parameterized secure views in AlloyDB 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 an AlloyDB cluster and instance. For more information, see Create a database.

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

1. Request access to parameterized secure views and wait until you receive the enablement confirmation before you begin.

2. Wait for the AlloyDB team to enable the parameterized_views.enabled database flag, which loads the required extension libraries. This database flag must be enabled before you can begin.

   After the AlloyDB team enables the parameterized_views.enabled database flag, your database restarts to make these changes take effect.

3. Use AlloyDB Studio or psql to create the parameterized_views extension in any database where a parameterized view is created:

   -- Requires parameterized_views.enabled set to true
   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:

1. Run the CREATE VIEW DDL command, as shown in the following example:

   CREATE VIEW secure_checked_items WITH (security_barrier) AS
   SELECT bag_id, timestamp, location
   FROM checked_items t
   WHERE customer_id = $@app_end_userid;
   

   In the preceding example, the parameterized secure view allows access to three columns from a table named /users/checked_items/. The view limits the results to rows where /users.id/checked_items.customer_id/ matches a required parameter.

   Use the following attributes:

   • Create the view using the security_barrier option.
   • 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).
     • You can use letters with diacritical marks and non-Latin letters, and you can use an underscore (_).
     • Subsequent characters can be letters, underscores, or digits (0-9).
     • Named view parameters can't contain $.
     • Named view parameters are case sensitive. For example, $@PARAMETER_NAME is interpreted differently than $@parameter_name.

2. Grant SELECT on the view to any database user that is allowed to query the view.

3. 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.

Configure security for your application

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

1. Create the secure parameterized views as an administrative user. This user is an AlloyDB database user that performs administrative operations for the application, including database setup and security administration.

2. Create a new database role for executing queries against parameterized secure views. This is an AlloyDB database role that the application uses to connect and sign into the database, and to execute queries against parameterized views.

   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 a database user.

Query a parameterized secure view

To query a parameterized secure view, use one of the following options that best supports your use case:

• 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. The execute_parameterized_query function provided by the parameterized_views extension accepts a cursor name.
• PREPARE EXECUTE statement: Use this for prepared statements that can be executed multiple times with different parameters values.

To query parameterized secure views, you use the execute_parameterized_query() function provided by the parameterized_views extension.

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

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => 'SELECT * FROM secure_checked_items',
    param_names => ARRAY ['app_end_userid'],
    param_values => ARRAY ['40']
)

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

  -- start a transaction as the that is the default lifetime of a CURSOR
  BEGIN;
  -- create a cursor called 'mycursor'
  SELECT * FROM parameterized_views.execute_parameterized_query(
   query => 'SELECT * FROM secure_checked_items',
   cursor_name => 'mycursor'
   param_names => ARRAY ['app_end_userid'],
   param_values => ARRAY ['40']
  );

  -- then, to actually fetch the results
  FETCH ALL FROM mycursor;
  -- end the transaction, which will clean up the cursor
  END;

PREPARE pquery (/POSITIONAL_PARAM_TYPES/)
        AS RESTRICTED query % a query that may refer to parameterized views
EXECUTE pquery (/POSITIONAL_PARAM_VALUES/)
      WITH VIEW PARAMETERS (VIEW_PARAM_NAME1 = VIEW_PARAM_VALUE1[, ...]);

PREPARE pquery (timestamp) AS RESTRICTED SELECT * FROM secure_checked_items WHERE timestamp > $1;

EXECUTE pquery (current_date - 1) WITH VIEW PARAMETERS (app_end_userid = 40);
EXECUTE pquery (current_date - 30) WITH VIEW PARAMETERS (app_end_userid = 40);

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:

• Any recursive invocation of any APIs—execute_parameterized_query, or by using EXECUTE .. WITH VIEW PARAMETERS— is prohibited, so that only the values specified by the application are used. This restriction also prevents the query from being used to circumvent the security envelope of the given set of parameter values.
• Some extensions that start a new background session are disallowed, including the dblink, pg_cron and pg_background extensions.
• The following lists the set of query constructs permitted that are restricted:
  • Read-only SELECT statements are allowed.
  • Read-only SHOW statements, CALL statements, and DO statements are allowed.
  • DML statements such as INSERT, UPDATE, and DELETE aren't allowed.
  • DDL statements such as CREATE TABLE and ALTER TABLE aren't allowed.
  • Other statement types such as LOAD, SET, CLUSTER, LOCK, CHECKPOINT, and EXPLAIN aren't allowed.
• EXPLAIN statements are disallowed to avoid the possibility of covert channel attacks using query plans. For more information, see Covert channel.

List all parameterized views

Use the parameterized_views extension to list all the parameterized views in the database by using the all_parameterized_views view. The output of this view is the same as the pg_views catalog view, but all_parameterized_views only lists views with named view parameters.

To list parameterized views, use the following example:

postgres=# select * from parameterized_views.all_parameterized_views ;
schemaname |      viewname      | viewowner |                       definition
-----------+--------------------+-----------+---------------------------------------------------------
public     | checked_items_view | postgres  |  SELECT checked_items.bag_id,                          +
           |                    |           |     checked_items."timestamp",                         +
           |                    |           |     checked_items.location                             +
           |                    |           |    FROM checked_items                                  +
           |                    |           |   WHERE (checked_items.customer_id = $@app_end_userid);

To list a parameterized view in all_parameterized_views, make sure that the parameterized view contains at least one named view parameter in its definition.

What's next

• Learn about parameterized secure views.
• Learn how to secure and control access to application data using parameterized secure views.