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

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