Authentication Methods for dbadmin

Posted January 3, 2018 by Soniya Shah, Information Developer

High angle view of Beijing Guomao.

This blog post was authored by Sumeet Keswani.

In Vertica, when you create a new database, there are no configured authentication methods. In this case, Vertica assumes that all users, including the dbadmin, have an implicit password authentication. Users can use this authentication method both for authenticating over a network interface and for over a local domain socket.

Note: The example in this post uses hash and password authentication for demonstration purposes. This example can be applied to all types of authentication methods.

First, let’s create a new database named auth_test: admintools -t create_db -d auth_test -s 10.0.0.10 -p letmein Then, we’ll create a user named test: dbadmin=> create user test identified by 'lettestin'; On the newly created database, the login authentication looks like the following over the network, where the default authentication method is default:password: $ vsql -X -U dbadmin -w letmein -h 10.0.0.10 -c "select client_authentication_name,client_authentication from sessions where session_id = current_session();" client_authentication_name | client_authentication ----------------------------+----------------------- default: Password | 0 (1 row) $ vsql -X -U test -w lettestin -h 10.0.0.10 -c "select client_authentication_name,client_authentication from sessions where session_id = current_session();" client_authentication_name | client_authentication ----------------------------+----------------------- default: Password | 0 (1 row) The authentication looks like the following on the local domain sockets: $ vsql -X -U dbadmin -w letmein -c "select client_authentication_name,client_authentication from sessions where session_id = current_session();" client_authentication_name | client_authentication ----------------------------+----------------------- default: Password | 0 (1 row) $ vsql -X -U test -w lettestin -c "select client_authentication_name,client_authentication from sessions where session_id = current_session();" client_authentication_name | client_authentication ----------------------------+----------------------- default: Password | 0 (1 row)

However, when you create a single authentication method, the assumption is the user is no longer a novice user and the intent is to lock things down. Now, the policy is to allow access only to those users with explicit authorization assignments. We no longer assume the default:password, except for the dbadmin password authentication over a local domain socket.

To test this, let’s create a new authentication method:

dbadmin=> create authentication network_passwd method 'hash' host '0.0.0.0/0' ; CREATE AUTHENTICATION Now the user test cannot login using the default:password method. This will apply to any non-dbadmin user. However, the dbadmin continues to use the implicit default:password authentication method for local access only: $ vsql -X -U dbadmin -w letmein -h 10.0.0.10 -c "select client_authentication_name,client_authentication from sessions where session_id = current_session();" vsql: FATAL 2248: Authentication failed for username "dbadmin" $ vsql -X -U test -w lettestin -h 10.0.0.10 -c "select client_authentication_name,client_authentication from sessions where session_id = current_session();" vsql: FATAL 2248: Authentication failed for username "test" $ vsql -X -U test -w lettestin -c "select client_authentication_name,client_authentication from sessions where session_id = current_session();" vsql: FATAL 2248: Authentication failed for username "test" Only the password authentication method is allowed for dbadmin over local domain sockets, to prevent the dbadmin from being locked out: $ vsql -X -U dbadmin -w letmein -c "select client_authentication_name,client_authentication from sessions where session_id = current_session();" client_authentication_name | client_authentication ----------------------------+----------------------- default: Password | 0 (1 row)

Therefore, to enable any non-dbadmin user to log in, an explicit authentication method assignment is required, as shown in the following steps:

dbadmin=> CREATE AUTHENTICATION local_passwd method 'hash' local; CREATE AUTHENTICATION dbadmin=> GRANT AUTHENTICATION network_passwd to test; GRANT AUTHENTICATION dbadmin=> GRANT AUTHENTICATION local_passwd to test; GRANT AUTHENTICATION

Now, we see that the user test has access to both the network and local password authentication methods:

$ vsql -X -U test -w lettestin -h 10.0.0.10 -c "select client_authentication_name,client_authentication from sessions where session_id = current_session();" client_authentication_name | client_authentication ----------------------------+----------------------- network_passwd | 45035996273708038 (1 row) $ vsql -X -U test -w lettestin -c "select client_authentication_name,client_authentication from sessions where session_id = current_session();" client_authentication_name | client_authentication ----------------------------+----------------------- local_passwd | 45035996273708098 (1 row) The same authentication can be granted to dbadmin by granting network_passwd to dbadmin. This would not be possible without having the default:password authentication for dbadmin over local domain socket, which is required to run the GRANT command. $ vsql -X -U dbadmin -w letmein -c "grant authentication network_passwd to dbadmin;" GRANT AUTHENTICATION $ vsql -X -U dbadmin -w letmein -h 10.0.0.10 -c "select client_authentication_name,client_authentication from sessions where session_id = current_session();" client_authentication_name | client_authentication ----------------------------+----------------------- network_passwd | 45035996273708038 (1 row)

At this point, you might want to lock down the dbadmin password authentication, or use an explicitly defined password authentication, rather than the default:password. We recommend you do not do this, because an incorrect configuration can cause dbadmin to be locked out.

However, if you want to lock down dbadmin access over the local domain socket, using an explicit authentication, do the following:

$ vsql -X -U dbadmin -w letmein -c "grant authentication local_passwd to dbadmin;" GRANT AUTHENTICATION [dbadmin@dbadmin] [@:/]$ vsql -X -U dbadmin -w letmein -c "select client_authentication_name,client_authentication from sessions where session_id = current_session();" client_authentication_name | client_authentication ----------------------------+----------------------- local_passwd | 45035996273708098 (1 row)

Note: We recommend that you don’t configure dbadmin password authentication over the local domain socket to be LDAP or Kerberous authentication types. If connectivity to the LDAP server or Kerberos server is impaired, the dbadmin is locked out.

Recommendation: Verify that admintools is working over the local network after performing these tests.