Secure and control access to application data using parameterized secure views Stay organized with collections Save and categorize content based on your preferences.

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

This tutorial describes how to use parameterized secure views in Cloud SQL for PostgreSQL to restrict user access to parameterized views using Cloud SQL Studio or psql.

Objectives

• Create secure parameterized 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 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 view using the execute_parameterized_query function.

Prepare your environment

1. Enable the cloudsql.enable_parameterized_views database flag for your Cloud SQL instance. This flag change requires a database restart.

2. Connect to your database as the postgres user.

   psql -U postgres
   

3. Create the parameterized_views extension in the database.

   CREATE EXTENSION parameterized_views;
   

4. Create a new database role for executing queries.

   CREATE ROLE psv_user WITH LOGIN PASSWORD 'PASSWORD';
   

5. Create a schema and a table for the application data.

   CREATE SCHEMA app_schema;
   CREATE TABLE app_schema.items(item_id INT, item_name TEXT, description TEXT, owner_id INT);
   
   INSERT INTO app_schema.items (item_id, item_name, description, owner_id) VALUES
   (1, 'Book', 'A great read', 123),
   (2, 'Laptop', 'Work machine', 456),
   (3, 'Pencil', 'For writing', 123);
   

Create secure parameterized views and set up access privileges

1. Create a parameterized secure view:

   CREATE VIEW app_schema.user_items_view WITH (security_barrier) AS
   SELECT item_id, item_name, description
   FROM app_schema.items
   WHERE owner_id = $@current_user_id;
   

2. Grant access to the view and schema to the application role.

   GRANT USAGE ON SCHEMA app_schema TO psv_user;
   GRANT SELECT ON app_schema.user_items_view TO psv_user;
   

3. Revoke direct access to the base table.

   REVOKE ALL PRIVILEGES ON app_schema.items FROM psv_user;
   

Verify data security

1. Connect as the psv_user.

   psql -U psv_user -d postgres
   

2. Verify that the base table can't be accessed.

   SELECT * FROM app_schema.items;
   -- ERROR:  permission denied for table items
   

3. Access the parameterized secure view using the execute_parameterized_query function:

   SELECT * FROM parameterized_views.execute_parameterized_query(
     query => 'SELECT * from app_schema.user_items_view',
     param_names => ARRAY ['current_user_id'],
     param_values => ARRAY ['123']
   );
   

   The result should only include items where owner_id is 123.

What's next

• Learn about parameterized secure views overview.
• Learn how to manage application data security using parameterized secure views.