Secure and control access to application data using parameterized secure views

This tutorial describes how to use parameterized secure views in AlloyDB for PostgreSQL to restrict user access to parameterized views using AlloyDB Studio or psql. Examples are included to illustrate the capabilities of parameterized secure views. These examples are intended for demonstration purposes only.

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.

Costs

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

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.

When you finish the tasks that are described in this document, you can avoid continued billing by deleting the resources that you created. For more information, see Clean up.

Before you begin

Request access

Before you can use parameterized secure views, you must request access to AlloyDB parameterized secure views and wait until you receive the enablement confirmation before you begin.

Enable billing and required APIs

  1. In the Google Cloud console, select a project.

    Go to project selector

  2. Make sure that billing is enabled for your Google Cloud project.

  3. Enable the Cloud APIs necessary to create and connect to AlloyDB for PostgreSQL.

    Enable the API

    1. In the Confirm project step, click Next to confirm the name of the project you are going to make changes to.

    2. In the Enable APIs step, click Enable to enable the following:

      • AlloyDB API

Create and connect to a database

  1. Create a cluster and its primary instance.
  2. Connect to your instance and create a database.

Prepare your environment

To prepare for running queries on a parameterized secure view, you must first set up parameterized views, the database and database roles, the parameterized_view extension, and the application schema.

Set up parameterized views and the database

  1. Request the AlloyDB team to enable the parameterized_views.enabled database flag, which loads the required extension libraries.

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

Create database roles, the extension, and the application schema

  1. Using AlloyDB Studio or psql, connect to the database as the postgres user or as a user with AlloyDB superuser privileges.

    psql database -U postgres

    For more information, see About database user management in AlloyDB.

  2. Create the parameterized_views extension in the database.

    -- Requires parameterized_views.enabled set to true
CREATE EXTENSION parameterized_views;

    When the extension is created, the system 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.

  3. Create the AlloyDB administrative role, which owns and manages the database.

    CREATE ROLE admin_user WITH LOGIN PASSWORD '...';
 GRANT ALL PRIVILEGES ON DATABASE database TO admin_user;

    For more information, see CREATE USER.

  4. Create a new database role for executing queries against parameterized secure views. This is an AlloyDB 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.

  5. Connect as the administrative user.

    SET role TO admin_user;

  6. Create the schema that contains the tables.

    CREATE SCHEMA schema;

  7. Create the tables and insert data.

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

INSERT INTO schema.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 secure parameterized views and set up access privileges

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

  1. Using AlloyDB Studio or psql, connect to the database as admin_user.

    psql database -U admin_user

  2. To provide limited access to the view, create a parameterized secure view:

    CREATE VIEW schema.secure_checked_items WITH (security_barrier) AS
SELECT bag_id, timestamp, location
FROM schema.checked_items t
WHERE customer_id = $@app_end_userid;

  3. Grant access to the view.

    GRANT SELECT ON schema.secure_checked_items TO psv_user;

  4. To access the view, grant access to the schema.

    GRANT USAGE ON SCHEMA schema TO psv_user;

  5. Revoke direct access to the base table.

    REVOKE ALL PRIVILEGES ON schema.checked_items FROM psv_user;

Verify data security

To verify that the parameterized secure views are restricting access to the designated views, run the following commands as psv_user. This is a AlloyDB database role that the application uses to connect and sign into the database to execute queries.

  1. Connect as a parameterized secure views user.

    psql database -U psv_user

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

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

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

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

  4. To improve security for natural language generated queries, integrate your parameterized secure views using AlloyDB AI natural language.

Clean up

To avoid incurring charges to your Google Cloud account for the resources used in this tutorial, either delete the project that contains the resources, or keep the project and delete the individual resources.

  1. In the Google Cloud console, go to the Clusters page.

    Go to Clusters

  2. Click the name of your cluster, my-cluster, in the Resource name column.

  3. Click Delete cluster.

  4. In Delete cluster my-cluster, enter my-cluster to confirm you want to delete your cluster.

  5. Click Delete.

  6. If you created a private connection when you created a cluster, go to the Google Cloud console Networking page and click Delete VPC network.

  7. Using AlloyDB Studio or psql, connect to the database as postgres.

    psql database -U postgres

  8. Drop the objects that you created.

    DROP VIEW schema.secure_checked_items;
DROP TABLE schema.checked_items;
DROP SCHEMA schema;
DROP ROLE psv_user;
DROP ROLE admin_user;
DROP EXTENSION parameterized_views;

What's next