Secure and control access to application data using parameterized secure views

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