Enable PrestoSQL/Trino Password File Authentication
This article is dedicated to one of the most fundamental tasks in Trino or PrestoSQL deployment : Enable Authentication and SSL for Trino Coordinator
For this deployment we are going to use two CentOS/Rocky Linux and hostnames are node-master and node1 :
192.168.56.2 node-master node-master.example.com
192.168.56.3 node1 node1.example.com
Software Versions
Trino 355 [aka PrestoSQL 355] ( link)
Hadoop 3.1.1 (link)
MySQL 5.7.28 (link)
Hive 3.1.2 (link)
JDK 11.0.11 (link : jdk-11.0.11_linux-x64_bin.tar.gz) and JDK 8u202 (link)
Deployment Steps
Finally, as you know we want to use Trino as SQL on HDFS/Data Lake and we will use Hive Metastore(HMS) as table meta data repository for data lake.
- create two OS user : hdfs and presto
useradd hdfs
passwd hdfs
useradd presto
passwd presto
2. Install JDK 11.0.11 and JDK 8, Trino 355 works with JDK11 and Hadoop works with JDK 8 : set JAVA_HOME variable in /etc/bashrc to JDK8 installation location and login with presto user and add JAVA_HOME variable to /home/presto/.bashrc file with JDK 11 installation location value
3. Install Hadoop and start HDFS services : start-dfs.sh
4. Install MySQL in one node, in this scenario in node1 and start service : systemctl start mysqld
5. Install Hive in one node and do all installation steps and run initSchema script to create HMS tables in MySQL and finally start HMS service : hive -service metastore
5. Install Trino in node-master and node1 (brief commands)
In coordinator(node-master), coordinator is also a worker : node-scheduler.include-coordinator=true
cd /opt
tar trino-server-355.tar.gz
mkdir /opt/trino-server-355/etc
mkdir /opt/trino-server-355/etc/catalog
vim /opt/trino-server-355/etc/config.properties
vim /opt/trino-server-355/etc/node.properties
node.environment=pcluster
vim /opt/trino-server-355/etc/jvm.config
vim /opt/trino-server-355/etc/catalog/hive.properties
connector.name=hive-hadoop2
hive.metastore.uri=thrift://192.168.56.2:9083
scp -r /opt/trino-server-355 node1:/opt/
In worker (node1)
vim /opt/trino-server-355/etc/config.properties
coordinator=false
Currently we installed Trino without authentication and anyone can connect to Trino coordinator UI and also use CLI to query hive tables : start Trino in all nodes with launcher start command and check coordinator UI http://node-master:8080/ui/login.html
CLI : java -jar presto-cli-328-executable.jar -server http://node-master:8080
6. Enable Authentication — Steps
- Enable SSL/HTTPS for coordinator [mandatory]
- Create Password file with httpd-tools
- change config.properties
- create /opt/trino-server-355/etc/password-authenticator.properties file
Enable HTTPS
create truststore and keystore files
mkdir /opt/security
cd /opt/security
Create Password file with httpd-tools
yum install httpd-tools
touch password.db
create user admin with a password : htpasswd -B -C 10 password.db admin
Edit config.properties and add
http-server.authentication.type=PASSWORD
http-server.https.enabled=true
http-server.https.port=8443
http-server.https.keystore.path=/opt/security/keystore.jks
http-server.https.keystore.key=password
Create password-authenticator.properties
vim /opt/trino-server-355/etc/password-authenticator.properties
password-authenticator.name=file
file.password-file=/opt/security/password.db
file.refresh-period=1m
file.auth-token-cache.max-size=1000
Restart Coordinator
HTTP UI disabled : http://node-master:8080/
HTTPS UI is available and you can login with username and password that you have: https://node-master.example.com:8443/ui/login.html
Use CLI to connect :
Use JDBC client tools to connect, for example intelliJ IDEA data source plugin
In this step you maybe face error if you want to connect from zeppelin JDBC interpreter, it’s related to Trino JDBC and parameters that zeppelin sent to driver, you can find solution in this article: Zeppelin to Trino JDBC Connection - Driver Problem
Also I will describe how to integrate Trino with LDAP and how to handle user impersonation from Zeppelin to Trino in another article.
