Parameterized secure views overview

This document describes parameterized secure views in AlloyDB for PostgreSQL, which give application data security and row access control while supporting SQL. These views support data value extraction—the process of retrieving specific data pieces from columns—and they help protect against prompt injection attacks. Parameterized secure views help ensure that end users can view only the data that they are supposed to access.

Parameterized views are an extension of PostgreSQL views, which let you use application-specific named view parameters in view definitions. This capability provides an interface that takes a query and values for the named parameters. The views execute the query with those values, which are used throughout the execution of that query.

The following is an example of a parameterized secure view:

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

You can query the views using the execute_parameterized_query stored procedure, or by running the EXECUTE .. WITH VIEW PARAMETERS statement.

Use cases

Parameterized secure views are well suited for data security administration at the database level against ad hoc queries from untrusted sources, such as queries translated from natural language queries. For example, consider an application whose database tracks the checked-in luggage of traveling customers. These customers can issue queries to the application. For example, a customer with the application user ID 12345 can enter a query into the application like, "Where is my bag?"

You can use parameterized secure views to apply the following requirements to how AlloyDB executes this query:

  • The query can read only the database objects and columns that you listed in your database parameterized secure views.
  • The query can read only the database rows that are associated with the user who submitted the query. The returned rows have a data relationship with the user's table row whose ID column value is 12345.

For more information about configuring security and access control, see Secure and control access to application data using parameterized secure views.

Parameterized secure views help to mitigate security risks that occur when end users are allowed to run untrusted queries, like natural language queries, on the database table. Security risks include the following:

  • Users can submit prompt injection attacks and try to manipulate the underlying model to reveal all the data that the application has access to.
  • The LLM might generate SQL queries that are broader in scope than is appropriate for data security reasons. This security risk can expose sensitive data in response to even well-intentioned user queries.

Using parameterized secure views, you can define the tables and columns that untrusted queries can pull data from. These views let you restrict the range of rows available to an individual application user. These restrictions also let you tightly control the data that application users can view through natural language queries, regardless of how users phrase those queries.

Security mechanism

Parameterized secure views give application developers data security and row access control using the following methods:

  • Views created using the WITH (security barrier) option provide row-level security by preventing maliciously-chosen functions and operators from being passed values from rows until after the view has done its work. For more information about the WITH (security barrier) clause, see Rules and Privileges.
  • Parameterization using named view parameters allows a restricted view of the database parameterized by values provided by the application based on application-level security such as end user authentication. For more information, see
  • Enforcement of additional restrictions on queries accessing parameterized views that prevents attacks against escaping the checks in the views based on the given parameter values. For more information, see Enforced restrictions on queries.

Limitations

  • If a parameterized view is referenced in a user-defined function that is called using any of the APIs used in parameterized secure views, an error occurs. You must directly reference the parameterized view in the parent query.

  • You must enable the parameterized view flag separately on every instance of AlloyDB. Parameterized view objects created on the primary instance are propagated to read-only replicas and cross-region replicas. However, the parameterized_views.enabled flag setting isn't replicated automatically and must be replicated manually on each instance. For more information, see Before you begin. You can't query parameterized views on the replica before you enable the parameterized_views.enabled flag on each replica instance. This limitation doesn't apply to the standby instance.

What's next