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 ?- 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 ] }- 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)- 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)- 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 | |