Parameterized secure views overview

Parameterized secure views in AlloyDB for PostgreSQL let you enforce application-level row access control so that users can view only the data that they're authorized to access. Built as an extension of PostgreSQL secure views, these views let you include application-specific named parameters in view definitions and pass parameter values that apply throughout query execution.

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 by using the execute_parameterized_query function, 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 that the query returns only 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 helps protect application data, 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 AI natural language-to-SQL generation. This enforcement prevents queries from escaping the security envelope provided by parameterized secure views and helps manage resource usage. For more information about query restrictions, 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.