Parameterized secure views overview

This page describes parameterized secure views in AlloyDB for PostgreSQL, which provide application data security and row access control using SQL views and help ensure that application users can view only the data that they're supposed to access.

Parameterized secure views are an extension of PostgreSQL secure 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 interface executes the query with those values, which are used throughout the execution of the 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 parameterized secure views using the execute_parameterized_query stored procedure, or by running the EXECUTE .. WITH VIEW PARAMETERS statement. For more information, see Manage application data security using AlloyDB parameterized secure views.

Benefits of parameterized secure views

Parameterized secure views are well suited for managing data security at the database level, especially when you're dealing with ad hoc queries from untrusted sources, such as those translated from natural language. These views offer a flexible way to implement fine-grained access control.

For example, consider an application that tracks customers' checked-in luggage. A customer with user ID 12345 asks, "Where is my bag?" In this scenario, parameterized secure views ensure the following:

The query only returns rows accessible to the user who submitted the query—for example, rows linked to user ID 12345.

Security risk mitigation

Parameterized secure views help mitigate security risks when end users run untrusted queries—like natural language queries—on your database. These risks include the following:

Malicious prompts: Users might try to manipulate the underlying model to access all application data.
Broadly-scoped SQL queries: Large language models (LLMs) might generate SQL queries that expose sensitive data, even from well-intentioned user queries.

By using parameterized secure views, you can restrict the range of rows available to individual application users. This control ensures data security, regardless of how users phrase their queries.

Data access management

Parameterized secure views address common challenges in managing data access for a large and growing number of users.

Simplified user management: With parameterized secure views, you can use a single database role to serve all end users, instead of using methods that might require you to create a separate database user or role for each end user. Parameterized secure views help simplify user and connection management for applications where each end user needs access only to their data.

For example, in an airline application where customers should only see their own bookings, you can define a single parameterized secure view that is parameterized by the end user identifier. This view allows a single database role –with access to the view, not the underlying table– to serve all users, which simplifies user management and database connections.
Streamlined security enforcement: Parameterized secure views inherently incorporate access controls. When a view is queried, the defined security parameters are consistently enforced, regardless of the user accessing the view. This approach contrasts with situations where underlying security policies on base tables might not automatically apply to views without extra configuration.

For more information about existing security mechanisms in PostgreSQL, such as Row Level Security (RLS) Policies, see Row Security Policies.

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.
Enforcement of additional restrictions on queries accessing parameterized views is useful for applications that execute untrusted queries from end users—like those generated by an AI natural language-to-SQL generation. This prevents escaping the security envelope provided by parameterized secure views and managing resource usage. For more information, see Enforced restrictions on queries.

Limitations

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 pool instances and cross-region replicas. However, the parameterized_views.enabled flag setting isn't applied automatically and must be set manually on each instance. For more information, see Before you begin.

You can't query parameterized views on a read pool instance or on a cross-region replica before you enable the parameterized_views.enabled flag on each instance.