Working with Vertica TRUST Authentication method

    In Vertica we have many types of authentication methods, one of them is the TRUST authentication methods. I am going to go and see the steps that are needed to be done to configuration such a authentication and explain in what circumstances this type of authentication method is useful.

What is TRUST Authentication method ? So like the names states this type of authentication assumes that a specified account that can connect to the server is authorized to access the database. Of course, restrictions made in the database and user columns still apply.  This method should only be used when there is adequate operating-system-level protection on connections to the server. When should i use it ?  TRUST authentication is appropriate and very convenient for local connections on a single-user workstation. It is usually not appropriate by itself on a multiuser machine. How can we setup TRUST Authentication ?
  1. First we will need to create an authentication method.
  • the base syntax is as bellow - for more details about the options follow the link.
CREATE AUTHENTICATION auth_method_name
       METHOD auth_type
       { LOCAL | HOST [ { TLS | NO TLS } ] } [ host_ip_address ] }
Example:
  • Let's first see if we have any  authentication methods enabled(we will use the CLIENT_AUTH table )
dbadmin= SELECT
auth_name,
is_auth_enabled,
auth_host_type,
auth_host_address,
auth_method
     FROM CLIENT_AUTH;
 auth_name | is_auth_enabled | auth_host_type | auth_host_address | auth_method
-----------+-----------------+----------------+-------------------+-------------

(0 rows)
- no authentication methods created yet.
  • Create the TRUST authentication method.(we will name it local_auth_method)
dbadmin= CREATE AUTHENTICATION local_auth_method
     METHOD 'trust' LOCAL;
  • Check the CLIENT_AUTH table again.
dbadmin= SELECT
auth_name,
is_auth_enabled,
auth_host_type,
auth_host_address,
auth_method
     FROM CLIENT_AUTH;
auth_name | is_auth_enabled | auth_host_type | auth_host_address | auth_method -------------------+-----------------+----------------+-------------------+------------- local_auth_method | True | LOCAL | | TRUST
  • Create a new user and grant the authentication method to a user and test it.
dbadmin= create user user1:
CREATE USER
-- grant him some privilages

dbadmin= grant usage on schema public to user1;
GRANT PRIVILEGE
-- grant him access to the authentication

dbadmin= GRANT AUTHENTICATION local_auth_method TO user1;
--connect to Vertica using the new user that will use the specified authentication method

[dbadmin@primary ~]$ vsql -Uuser1
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type: h or ? for help with vsql commands
 g or terminate with semicolon to execute query
 q to quit
-- see user name

user1= select username();
 username
----------

 user1
(1 row)
Nice so we managed to log into our Vertica database using no password and just making use of the TRUST authentication method. If you want to mange this authentication methods and get more details about them read the following:
  • How to see information about the client authentication methods that you have associated with your database users.
dbadmin= SELECT * FROM USER_CLIENT_AUTH;
     user_oid      | user_name |     auth_oid      |     auth_name     | granted_to
-------------------+-----------+-------------------+-------------------+------------

 45035996273753778 | user1     | 45035996273752412 | local_auth_method | user1
  • the user_client_auth table stores information about the related users and auth methods after using the grant authentication.
  • See the parameters related to the authentication methods.
  • in my case i have none setup.
dbadmin= SELECT * FROM CLIENT_AUTH_PARAMS;
     auth_oid      |     auth_name     | auth_parameter_name | auth_parameter_value
-------------------+-------------------+---------------------+----------------------

 45035996273752412 | local_auth_method |                     |
 45035996273752738 | localpassword     |                     |
I strongly suggest that you look closely into client authentication methods as they a main part of you core Vertica skills. I hope this was helpful.