Trino User Guide

Start Trino CLI

Log in to your Trino server where it is installed.
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

Run some sample queries to verify whether Trino has been properly integrated with Privacera and is ready to use.

Prerequisite

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

Create Policies

Create a policy as shown below for displaying metadata related to Redshift catalog.
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

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 failed:AccessDenied:Cannot create schema custome_schema
              
```
Permissions in Access Management.
In Privacera Portal, create a policy with Create permissions for your Trino user under privacera_trino service as shown below.

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>showschemas;Schema--------------------customer_schema

Create table

Query to create a new table.

CREATE TABLE IF NOT EXISTS customer_schema.customer_table(idint,person_namevarchar,ssnvarchar,countryvarchar,account_idvarchar);

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.

CREATE TABLE IF NOT EXISTS customer_schema.customer_table(idint,person_namevarchar,ssnvarchar,countryvarchar,account_idvarchar);

Query failed:AccessDenied:Cannot create table customer_table

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

Rerun the query to create a new table.

CREATE TABLE IF NOT EXISTS customer_schema.customer_table(idint,person_namevarchar,ssnvarchar,countryvarchar,account_idvarchar);

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

CREATE TABLE IF NOT EXISTS customer_schema.customer_table(idint,person_namevarchar,ssnvarchar,countryvarchar,account_idvarchar);

Insert Data in Table

Query to insert data in the table.

insert into customer_schema.customer_tablevalues(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_tablevalues(1,'Nancy','208-95-5535','UK','856-232-9702');

Query failed:AccessDenied:Cannotinsertintotablecustomer_table

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

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');insertintocustomer_schema.customer_table values(2,'Genne','242-92-5772','US','661-338-6787');insertintocustomer_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_tablevalues(1,'Nancy','208-95-5535','UK','856-232-9702');

Select data in table

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 failed:AccessDenied:Cannot select from columns[country,account_id,person_name,id,ssn]in table or view customer_table

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

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:>select* from customer_schema.customer_table;

id|person_name|ssn|country|account_id----+-------------+-------------+---------+--------------1
Nancy|208-95-5535|UK|856-232-97022|
Genne|242-92-5772|US|661-338-67873|
Edward|251-91-5542|US|231-338-57894|
Pearlene|217-97-5522|UK|708-471-68105|
James|263-95-5530|US|209-626-90416|
Nancy|281-98-5531|US|303-239-4282

(6rows)Query,FINISHED,1nodeSplits:17total,17done(100.00%)0.40[6rows,0B][15rows/s,0B/s]

Row-level filtering

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

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

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-67873|
Edward|251-91-5542|US|231-338-57895|
James|263-95-5530|US|209-626-90416|
Nancy|281-98-5531|US|303-239-4282
(4rows)

Column-level masking

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

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

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-97022|Genne|XXXXXXX5772|US|661-338-67873|
Edward|XXXXXXX5542|US|231-338-57894|Pearlene|XXXXXXX5522|UK|708-471-68105|
James|XXXXXXX5530|US|209-626-90416|Nancy|XXXXXXX5531|US|303-239-4282
(6rows)Query,FINISHED

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.

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
              
```
Restart the Trino server.
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.
(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.propertiesranger.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.

Ownership Permission

Note

Some Trino connectors do not support COMMENT and SET PROPERTIES commands.

See Trino SQL command permissions or Trino SQL command permissions - Iceberg connector for a complete list of supported operations and their required permissions.Trino SQL command permissions

Table level access control provides ownership of tables to users to have permissions to perform the below queries. Without setting the correct permissions, the following output is displayed.
```
Query failed: Access Denied: Cannot set properties to postgresql.customer_schema.customer_table
```
To verify, you can check the Privacera Portal Audits page.
To create these permissions in Privercera Portal, create a policy with ownership permissions for your Trino user under privacera_trino service as follows:
- Policy Type: Access
- Policy Name: Enter a policy name, such as "Ownership for Trino Tables"
- catalog: postgresql
- schema: customer_schema
- table: customer_table
- column: *
Under Allow Conditions -> Select User, enter the user name(s) with Permissions Ownership.

Note

The following queries use the following structure for the table value: catalog.schema.table

Query to set table properties

ALTER TABLE postgresql.customer_schema.customer_table SET PROPERTIES sorted_by = array['id'];

Query to set table comment

COMMENT ON TABLE postgresql.customer_schema.customer_table IS 'PostgreSQL Table';

Query to set column comment

COMMENT ON COLUMN postgresql.customer_schema.customer_table.person_name IS 'Person Name';

Query to add column

ALTER TABLE postgresql.customer_schema.customer_table ADD COLUMN name2 varchar;

Query to rename column

ALTER TABLE postgresql.customer_schema.customer_table RENAME COLUMN name2 to name3;

Query to rename table

ALTER TABLE postgresql.customer_schema.customer_table RENAME TO postgresql.customer_schema.customer_table_2;

Query to drop table

DROP TABLE postgresql.customer_schema.customer_table;

Use case with Hive connector

Hive policy authorization

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

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

Run the following command:

vi etc/catalog/hive.properties

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

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:

Go to the Ranger Trino config folder.
Run the following command:
```
vi install.properties           
```
Add/Edit the following property. By default, the value is set to true.
```
HIVE_POLICY_AUTHZ_ENABLED=true         
```
Run the following command:
```
./enable-trino-plugin.sh
```
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

Go to the Ranger Trino config folder.
Run the following command:
```
vi install.properties            
```
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
Run the following command:
```
./enable-trino-plugin.sh           
```
Restart the Trino server.

Setup Varada connector in Trino with Privacera Plugin

Installation

Create EC2 instance. See Varada requirements documentation to learn more.
SSH into EC2 instance and switch to root user.
```
sudo su - root
```

Install Java 11.

   sudo amazon-linux-extras install java-openjdk11

Install trino-server.

cd /opt   

mkdir -p /opt/trino   
mkdir -p /opt/trino/data   

cd /opt/trino/   

wget https://repo1.maven.org/maven2/io/trino/trino-server/370/trino-server-370.tar.gz   
tar -xzf trino-server-370.tar.gz   
ln -s trino-server-370  trino-server   

wget https://repo1.maven.org/maven2/io/trino/trino-cli/370/trino-cli-370-executable.jar   
chmod +x trino-cli-370-executable.jar   
ln -s trino-cli-370-executable.jar trino-cli

Create the necessary configuration files in the /opt/trino/trino-server/etc directory. See the Trino documentation to learn more.

Start trino-server.

 cd /opt/trino/trino-server   
./bin/launcher restart

Connect trino-cli to verify trino service.

./trino-cli --catalog hive 
select * from customer.customer_data;

Install Privacera Plugin.

Deploy Varada Connector

To learn more see: Varada Trino Connector: Community Edition Setup

Download varada-trino connector, create the varada-install directory and unpack the tarball.

   mkdir /tmp/varada-install   
tar -zxf /tmp/varada-trino-connector.tar.gz -C /tmp/varada-install

Then install.

sudo python3 /tmp/varada-install/varada-connector-*-varada-con/varada/installer.py -e /tmp/varada-install/varada-connector-*-varada-con/ -o hive -c varada -w r5d.4xlarge -m s3://privacera-dev-siva -p /opt/trino/trino-server/plugin/ -d /opt/trino/trino-server/etc/ -b 8088 -u my-cluster

Create workerDB directory.

mkdir -p /var/lib/presto/varada/workerDB/

Restart the trino-server.

cd /opt/trino/trino-server   
./bin/launcher restart

You can now test Privacera access control through trino-cli. To test the varada catalog you will need to add policies in privacera_trino.

Privacera Platform

Table of ContentsTable of Contents

Trino User Guide

Start Trino CLI

Verify Trino Integration

Prerequisite

Create Policies

Run queries

Create schema

Create table

Insert Data in Table

Select data in table

Row-level filtering

Column-level masking

Access views in AWS Athena

Ownership Permission

Note

Note

Use case with Hive connector

Hive policy authorization

Configure Hive policy authentication

Configure multiple Hive catalogs

Setup Varada connector in Trino with Privacera Plugin

Deploy Varada Connector