Parameterized secure views in Cloud SQL

Parameterized secure views in Cloud SQL for PostgreSQL provide data security and row access control to applications 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 user_specific_items WITH (security_barrier) AS
       SELECT item_id, item_name, description
       FROM items t
       WHERE owner_id = $@app_user_id;

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

Use cases

Parameterized secure views provide data security at the database level against ad hoc queries from untrusted sources, such as queries translated from natural language queries. For example, you can use parameterized secure views to provide data security for applications where users can issue queries in natural language, like, "Show my orders".

You can use parameterized secure views to apply the following requirements to how Cloud SQL for PostgreSQL executes this query:

  • The query reads only the database objects and columns that you listed in your database parameterized secure views.
  • The query reads 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.

For more information about configuring security and access control, see Manage application data security 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 Natural Language to SQL (NL2SQL) model 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.

Data security

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

  • Views created using the WITH (security barrier) clause 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.
  • Named view parameters allows a restricted view of the database parameterized by values. The values are provided by the application based on application-level security such as end user authentication.
  • 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

  • To use parameterized secure views, you must enable the cloudsql.enable_parameterized_views database flag for your Cloud SQL instance, which requires a database restart.

  • The parameterized_views extension must be created in each database where you intend to create parameterized secure views.

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

What's next