Secure and control access to application data

MySQL | PostgreSQL | SQL Server

Parameterized secure views provide row-level data security and access control by filtering query results based on application user identity.

This tutorial describes how to set up parameterized secure views in Cloud SQL, configure database roles and privileges to restrict access to base tables, and verify data security. The examples provided in this document are for demonstration purposes only.

What are parameterized secure views?

As a general best practice, your application should run using a service account with the minimal required access to your database. For example, if your application shouldn't write to your database, it should use a role with read-only access. You should configure these access controls at the database level.

When your application needs more granular security than standard database-level access permits, you can use parameterized secure views to make sure that users view only their authorized data.

Using parameterized secure views provides two main benefits:

Dynamic row-level security: Filters queries using end-user identifiers so users access only their authorized data regardless of query phrasing.
Simplified role management: Uses a single shared database role for all application users instead of managing separate database roles for each individual.

Objectives

Create parameterized secure views with named view parameters.
Create the database role that is used by the application to connect to the database and access parameterized secure views.
Grant the new role permissions to the parameterized secure views and revoke access to the base tables.
Connect using the new role and verify that the restricted tables can't be accessed.
Run queries on the parameterized secure views using the execute_parameterized_query() function or using the QueryData API.

Costs

In this document, you use the following billable components of Google Cloud:

Cloud SQL for PostgreSQL

To generate a cost estimate based on your projected usage, use the pricing calculator.

New Google Cloud users might be eligible for a free trial.

To avoid continued billing, delete the resources you created when you finish the tasks in this document. For more information, see Clean up.

Before you begin

Before you create parameterized secure views, complete the following prerequisites.

Enable billing and required APIs

In the Google Cloud console, select a project.

Go to project selector
Make sure that billing is enabled for your Google Cloud project.
Enable the Cloud APIs necessary to create and connect to Cloud SQL.
1. Enable the API
2. In the Confirm project step, click Next to confirm the name of the project you are going to make changes to.
3. In the Enable APIs step, click Enable to enable the following:
  - Cloud SQL for PostgreSQL API
  - Knowledge Catalog API

Create and connect to a database

Prepare your environment

To prepare for running queries on a parameterized secure view, set up the database, database roles, the parameterized_views extension, and the application schema.

Enable the database flag

Enable the cloudsql.enable_parameterized_views database flag, which loads the required extension libraries. For more information, see Configure an instance's database flags.

Set up the database

Create a database called database for the application data and parameterized secure views. For more information, see Create a database.

Create database roles, the extension, and the application schema

In the Google Cloud console, go to the Cloud SQL page.

Go to Cloud SQL
Select an instance from the list.
In the navigation menu, click Cloud SQL Studio.
Sign in to Studio using postgres authentication.
Click Authenticate. The Explorer pane displays a list of the objects in your database.
Click New SQL editor tab or New tab to open a new tab.
To use parameterized views, create the parameterized_views extension in your database:
```
-- Requires parameterized_views.enabled set to true
CREATE EXTENSION parameterized_views;
```
When the extension is created, the database also creates a schema named parameterized_views so that the APIs are contained in that schema's namespace and so that those APIs don't conflict with existing APIs.

Note: You must create an extension in every database in which parameterized views are created.
Sign in as a user with superuser privileges, such as the built-in postgres user.
Create a new database role for executing queries against parameterized secure views. This is an Cloud SQL role that the application uses to connect and sign into the database to execute queries with limited access to public functions or objects to the minimal required set.
```
CREATE ROLE psv_user WITH LOGIN PASSWORD '...';
```
For more information, see CREATE USER.
Connect as the administrative user.
```
SET role TO postgres;
```
Create the schema that contains the tables.
```
CREATE SCHEMA store;
```

Create the tables and insert data.

CREATE TABLE store.checked_items(bag_id INT,timestamp TIMESTAMP, loc_code CHAR(3), scan_type CHAR(1), location TEXT, customer_id INT);

INSERT INTO store.checked_items (bag_id, timestamp, loc_code, scan_type, location, customer_id) VALUES
(101, '2023-10-26 10:00:00', 'ABC', 'I', 'Warehouse A', 123),
(102, '2023-10-26 10:15:30', 'DEF', 'O', 'Loading Dock B', 456),
(103, '2023-10-26 10:30:45', 'GHI', 'I', 'Conveyor Belt 1', 789),
(104, '2023-10-26 11:00:00', 'JKL', 'O', 'Shipping Area C', 101),
(105, '2023-10-26 11:45:15', 'MNO', 'I', 'Sorting Station D', 202),
(106, '2023-10-26 12:00:00', 'PQR', 'O', 'Truck Bay E', 303);

Create parameterized secure views and set up access privileges

To create parameterized secure views and to set up appropriate access privileges for the base table and views, follow these steps:

In the Google Cloud console, go to the Cloud SQL page.

Go to Cloud SQL
Select an instance from the list.
In the navigation menu, click Cloud SQL Studio.
Sign in to Studio and connect to the database as the postgres.
Click Authenticate. The Explorer pane displays a list of the objects in your database.
Click New SQL editor tab or New tab to open a new tab.
To provide limited access to the view, create a parameterized view:
```
CREATE VIEW store.secure_checked_items WITH (security_barrier) AS
SELECT bag_id, timestamp, location
FROM store.checked_items t
WHERE customer_id = $@app_end_userid;
```
Note: Make sure that you use unique parameter names, such as $@app_end_userid, when creating parameterized secure views.

Grant access to the view.

GRANT SELECT ON store.secure_checked_items TO psv_user;

To access the view, grant access to the schema.

GRANT USAGE ON SCHEMA store TO psv_user;

Revoke direct access to the base table.
```
REVOKE ALL PRIVILEGES ON store.checked_items FROM psv_user;
```
Note: The owner of the parameterized view must have SELECT privileges on the base tables. In addition, the user of the parameterized view must have USAGE on the schema of the view and SELECT on the view. For example, the postgres user owns the parameterized view, so it has SELECT privileges on the base tables while the psv_user must have USAGE on the store schema and SELECT on the view.
Sign in as the postgres administrator, and grant the psv_user role to an IAM-authenticated user:
```
GRANT psv_user TO "IAM_USER_EMAIL";
```
Replace IAM_USER_EMAIL with your IAM user email address.

Verify data security

To verify that the parameterized secure views are restricting access to the designated views, sign into the database as the IAM-authenticated user. In Cloud SQL, IAM users inherit the permissions of the database roles assigned to them.

Sign into the database as the IAM-authenticated user.

Verify that the base table can't be accessed.

SELECT * FROM store.checked_items;
ERROR:  permission denied for table checked_items

Access the parameterized secure views using the execute_parameterized_query function:

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

Query the parameterized secure views using SQL syntax and the QueryData request with PSV parameters.

  curl -X POST \
    "https://geminidataanalytics.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION:queryData" \
    -H "Authorization: Bearer $(gcloud auth print-access-token)" \
    -H "Content-Type: application/json; charset=utf-8" \
    -d '{
      "prompt": "Show me the checked items.",
      "context": {
        "datasource_references": {
          "cloud_sql_reference": {
            "database_reference": {
              "engine": "POSTGRESQL",
              "project_id": "PROJECT_ID",
              "region": "REGION",
              "instance_id": "INSTANCE_ID",
              "database_id": "DATABASE_ID"
            }
          }
        },
        "parameterized_secure_view_parameters": {
          "parameters": {
            "app_end_userid": "303"
          }
        }
      },
      "generation_options": {
        "generate_query_result": true,
        "generate_natural_language_answer": true,
        "generate_explanation": true
      }
    }'

Replace the following values:

PROJECT_ID: Your Google Cloud project ID.
REGION: The region where your Cloud SQL for PostgreSQL instance is located.
INSTANCE_ID: The ID of your Cloud SQL for PostgreSQL instance.
DATABASE_ID: The ID of your Cloud SQL for PostgreSQL database.

Clean up

Delete the cluster

When you delete the cluster that you created in the before you begin section, you also delete all of the objects you created.

In the Google Cloud console, go to the Cloud SQL page.

Go to Cloud SQL
Select an instance from the list.
Click Delete.
Confirm that you want to delete the instance by entering the instance name and clicking Delete.