Secure and control access to application data using parameterized secure views

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.

Enable the required extension

Enable the parameterized_views.enabled 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 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 .