How to have many users added to the pg_hba.conf without having line for each for Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

You are using kerberos authentication and you want to grant database access to the database. You could create a line for each user in the pg_hba.conf, but if you have 100's of users that can be confusing. So you want a better way to grant database access to many users without having to enter a line for each. 

Environment

Product Version: 6.18

Resolution

There are 2 ways to do this:

Option 1: Create a role in the database and grant all of the users access to that role 

a. Create 1 ROLE in the Database called "eastna_users"

CREATE ROLE eastna_users; 

b. Then add all the eastna users to the "eastna_users".

GRANT eastna_users to userx; 

c. add the following lines in the pg_hba.conf file:

host     all    +eastna_users     0.0.0.0/0   gss include_realm=0 krb_realm=######.#####.COM
host     all    all               0.0.0.0/0   gss include_realm=0 krb_realm=######.#####.COM  

 

Option 2: Create a list of users in a file and put the file name in the pg_hba.conf
 
a. Create a text file:

$MASTER_DATA_DIRECTORY/eastna_users.txt 

b. List the new eastna users IDs in the file. One userid on each line

c. Then the pg_hba.conf file should have the lines:

host     all    @eastna_users.txt   0.0.0.0/0   gss include_realm=0 krb_realm=#####.#####.COM
host     all    all                 0.0.0.0/0   gss include_realm=0 krb_realm=####.######.COM  

d. You need to restart the Kerberos daemons for it to take affect.

/sbin/service kadmin stop
/sbin/service kadmin start