Skip to content

Trino User Guide

Start Trino CLI

  1. Log in to your Trino server where it is installed.

  2. Start Trino.

    ./trino --user $user --catalog $catalog_name
    

    For Hive catalogs in Trino server, by default, the privacera_hive service is used for access management. For all other catalogs, the privacera_trino service is used.

Verify Trino Integration

By running some sample queries. you can verify whether Trino has been properly integrated with Privacera and ready to use.

Prerequisite

It is assumed that Trino server is configured with Privacera Trino plugin and Redshift catalog.

Create Policies

  1. Create a policy as shown below for displaying metadata related to Redshift catalog.

  2. For a few catalogs such as PostgreSQL, Redshift, Hive and so on, metadata is stored in an information_schema table. So, create a policy to select on the schema as shown below.

Run Queries

Create Schema

  1. Query to create a new schema.

    CREATE SCHEMA customer_schema;
    

    The following output is displayed. You will be denied to create a schema since the Trino service you just installed does not have the necessary permissions. To verify, you can check Privacera Portal Audits page.

    trino> create schema custome_schema;
    Query 20210618_070819_00008_7pje8 failed: Access Denied: Cannot create schema custome_schema
    
  2. Permissions in Access Management.

    In Privacera Portal, create a policy with Create permissions for your Trino user under privacera_trino service as shown below.

  3. Rerun the query to create a new schema.

    CREATE SCHEMA customer_schema;
    

    The following output is displayed. Now, you will be able to create the schema.

    trino> create schema custome_schema;
    CREATE SCHEMA
    

    To verify if the schema has been created, run the following command.

    trino> show schemas;
       Schema       
    --------------------
    customer_schema
    

Create Table

  1. Query to create a new table.

        CREATE TABLE IF NOT EXISTS customer_schema.customer_table ( id int, person_name varchar ,ssn varchar, country varchar, account_id varchar);
    

    The following output is displayed. You will be denied to create a table since the Trino service you just installed does not have the necessary permissions. To verify, you can check Privacera Portal Audits page.

    trino> CREATE TABLE IF NOT EXISTS customer_schema.customer_table ( id int, person_name varchar ,ssn varchar, country varchar, account_id varchar);
    Query 20210618_071950_00011_7pje8 failed: Access Denied: Cannot create table customer_table
    
  2. Permissions in Access Management.

    In Privacera Portal, create a policy with Create permissions for your Trino user under privacera_trino service as shown below.

  3. Rerun the query to create a new table.

    CREATE TABLE IF NOT EXISTS customer_schema.customer_table ( id int, person_name varchar ,ssn varchar, country varchar, account_id varchar);
    

    The following output is displayed. Now, you will be able to create the table.

    trino> CREATE TABLE IF NOT EXISTS customer_schema.customer_table ( id int, person_name varchar ,ssn varchar, country varchar, account_id varchar);
    CREATE TABLE
    

Insert Data in Table

  1. Query to insert data in the table.

    insert into customer_schema.customer_table values (1, 'Nancy','208-95-5535','UK','856-232-9702');
    

    The following output is displayed. You will be denied to insert data since the Trino service you just installed does not have the necessary permissions. To verify, you can check Privacera Portal Audits page.

    trino> insert into customer_schema.customer_table values (1, 'Nancy','208-95-5535','UK','856-232-9702');
    Query 20210618_080707_00033_7pje8 failed: Access Denied: Cannot insert into table customer_table
    
  2. Permissions in Access Management.

    In Privacera Portal, create a policy with Insert permissions for your Trino user under privacera_trino service as shown below.

  3. Rerun the query to insert multiple data in the table.

    insert into customer_schema.customer_table values (1, 'Nancy','208-95-5535','UK','856-232-9702');
    insert into customer_schema.customer_table values (2, 'Genne','242-92-5772','US','661-338-6787');
    insert into customer_schema.customer_table values (3, 'Edward','251-91-5542','US','231-338-5789');
    insert into customer_schema.customer_table values (4, 'Pearlene','217-97-5522','UK','708-471-6810');
    insert into customer_schema.customer_table values (5, 'James','263-95-5530','US','209-626-9041');
    insert into customer_schema.customer_table values (6, 'Nancy','281-98-5531','US','303-239-4282');
    

    The following output is displayed. Now, you will be able to insert data in the table.

    trino> insert into customer_schema_t.customer_table values (1, 'Nancy','208-95-5535','UK','856-232-9702');
    INSERT: 1 row
    Query 20210618_080500_00031_7pje8, FINISHED, 1 node
    Splits: 35 total, 35 done (100.00%)
    4.01 [0 rows, 0B] [0 rows/s, 0B/s] 
    

Select Data in Table

  1. Query to select data in the table.

    select * from customer_schema.customer_table;
    

    The following output is displayed. You will be denied to select data since the Trino service you just installed does not have the necessary permissions. To verify, you can check Privacera Portal Audits page.

    trino> select * from customer_schema.customer_table;
    Query 20210618_082114_00034_7pje8 failed: Access Denied: Cannot select from columns [country, account_id, person_name, id, ssn] in table or view customer_table
    
  2. Permissions in Access Management.

    In Privacera Portal, create a policy with Select permissions for your Trino user under privacera_trino service as shown below.

  3. Rerun the query to select data in the table.

    SELECT * FROM customer_schema.customer_data;
    

    The following output is displayed. Now, you will be able to insert data in the table.

    trino:customer_schema> select * from customer_schema.customer_table;
    id | person_name |     ssn     | country |  account_id  
    ----+-------------+-------------+---------+--------------
    1 | Nancy       | 208-95-5535 | UK      | 856-232-9702 
    2 | Genne       | 242-92-5772 | US      | 661-338-6787 
    3 | Edward      | 251-91-5542 | US      | 231-338-5789 
    4 | Pearlene    | 217-97-5522 | UK      | 708-471-6810 
    5 | James       | 263-95-5530 | US      | 209-626-9041 
    6 | Nancy       | 281-98-5531 | US      | 303-239-4282 
    (6 rows)
    
    Query 20210615_083840_00045_4sqq5, FINISHED, 1 node
    Splits: 17 total, 17 done (100.00%)
    0.40 [6 rows, 0B] [15 rows/s, 0B/s]
    

Row-level Filtering

Trino supports row-level filtering at a table-level.

  1. Create a row-level policy on the table created above to display rows only with US country.

  2. Query the table to display rows only with the country US.

    trino:customer_schema> select * from customer_table;
    

    The following output will be displayed.

    trino:customer_schema> select * from customer_table;
    id | person_name |     ssn     | country |  account_id  
    ----+-------------+-------------+---------+--------------
    2 | Genne       | 242-92-5772 | US      | 661-338-6787 
    3 | Edward      | 251-91-5542 | US      | 231-338-5789 
    5 | James       | 263-95-5530 | US      | 209-626-9041 
    6 | Nancy       | 281-98-5531 | US      | 303-239-4282 
    (4 rows)
    

Column-level Masking

Trino supports column-level masking at a table-level.

  1. Create a column-masking policy on the table created above to show only the last 4 digits of the Social Security Number (SSN).

  2. Query the table to show only the last 4 digits of the Social Security Number (SSN).

    trino:customer_schema> select * from customer_table;
    

    The following output will be displayed.

    trino:customer_schema> select * from customer_table;
    id | person_name |     ssn     | country |  account_id  
    ----+-------------+-------------+---------+--------------
    1 | Nancy       | XXXXXXX5535 | UK      | 856-232-9702 
    2 | Genne       | XXXXXXX5772 | US      | 661-338-6787 
    3 | Edward      | XXXXXXX5542 | US      | 231-338-5789 
    4 | Pearlene    | XXXXXXX5522 | UK      | 708-471-6810 
    5 | James       | XXXXXXX5530 | US      | 209-626-9041 
    6 | Nancy       | XXXXXXX5531 | US      | 303-239-4282 
    (6 rows)
    
    Query 20210615_085728_00055_4sqq5, FINISHED, 1 node
    Splits: 17 total, 17 done (100.00%)
    0.77 [6 rows, 0B] [7 rows/s, 0B/s]
    

Access Views in AWS Athena

Use the following steps to provide access for views created in AWS Athena. As a result, you will be able to query the views.

  1. Copy the Hive catalog properties (or create a symlink) as awsdatacatalog.properties in /etc/catalog folder.

    ln -s etc/catalog/hive.properties etc/catalog/awsdatacatalog.properties
    
  2. Restart the Trino server.

  3. In Access Management > Resource Policies, update the privacera_hive default policy.

    1. Edit all - database, table policy.

    2. In Select User, add 'Trino' from the dropdown as the default view owner, and save.

  4. (Optional) To change the default view owner from 'Trino' to any other owner such as 'Hadoop', do the following:

    1. In the access-control.properties file, add the owner to the ranger.policy.authorization.viewowner.default variable.

      vi etc/access-control.properties
      ranger.policy.authorization.viewowner.default=<view-owner>
      
    2. Restart the Trino server.

      Accordingly, update the owner in the all - database, table policy of the privacera_hive service.

Use Case with Hive Connector

Hive Policy Authorization

You can configure access control on the Hive connector by setting the Hive catalog properties file.

  1. On your Trino server, navigate to the etc folder containing the Hive configuration files.

  2. Run the following command:

    vi etc/catalog/hive.properties
    
  3. Add the following properties in the Hive catalog to grant permission:

    Property Description Example
    hive.allow-drop-table Set the property to drop the table in the Hive catalog. hive.allow-drop-table=true
    hive.allow-add-column Set the property to add the column in the table in the Hive catalog. hive.allow-add-column=true
    hive.allow-rename-column Set the property to rename the column in the table in the Hive catalog. hive.allow-rename-column=true
    hive.allow-drop-column Set the property to drop the column in the table in the Hive catalog. hive.allow-drop-column=true
    hive.allow-rename-table Set the property to rename the table in the Hive catalog. hive.allow-rename-table=true

  4. Restart the Trino server.

Configure Hive Policy Authentication

When the Privacera Plugin is deployed in your Trino server, the TRINO_HIVE_POLICY_AUTHZ_ENABLED is set to true by default, allowing you to configure Hive policy authorization.

You can enable/disable the authorization in your Trino server. To configure, do the following:

  1. Go to the Ranger Trino config folder.

  2. Run the following command:

    vi install.properties
    
  3. Add/Edit the following property. By default, the value is set to true.

    HIVE_POLICY_AUTHZ_ENABLED=true
    
  4. Run the following command:

    ./enable-trino-plugin.sh
    
  5. Restart the Trino server.

Configure Multiple Hive Catalogs

When the Privacera Plugin is deployed in your Trino server, the TRINO_HIVE_POLICY_REPO_CATALOG_MAPPING property allows you to map multiple Hive catalogs.

You can configure Hive catalogs in your Trino server. To configure, do the following

  1. Go to the Ranger Trino config folder.

  2. Run the following command:

    vi install.properties
    
  3. Add/Edit the following property:

    HIVE_POLICY_REPO_CATALOG_MAPPING=privacera_hive:hive,hive2
    

    Enter the value for the property in the following format:

    {hive_policy_repo-1}:{comma_separated_hive_catalogs};{hive_policy_repo-2}:{comma_separated_hive_catalogs}

    The format indicates Hive policy repository and Hive catalog mapping. See example below:

    Example: privacera_hive:hivecatalog1,hivecatalog2;privacera_hive_1:hive3,hive4,hive5

  4. Run the following command:

    ./enable-trino-plugin.sh
    
  5. Restart the Trino server.