KB484541: Multiple end users to access PostgreSQL through the same database user with Kerberos authentication

Use Case

1. There is a PostgreSQL technical user called postgreKrb.
2. There are lots of end users which are logged into Strategy Web.
3. Those users should be able to access the database through the postgreKrb technical user.

Intelligence Server Configuration

• Generate Kerberos 5 credentials (ticket) cache for the Kerberos user
  

• kinit krbusertd@LABS.Strategy.COM -c /tmp/krb/krb5cc

• Verify the ticket cache.
  

• Add the following lines to 

  $MSTR_HOME/env/ODBC.sh

  :
  

• export KRB5CCNAME=/tmp/krb/krb5cc

• export KRB5_CONFIG=/tmp/krb/krb5.conf

• Use mstrtodbcx program to test the connection:
  

• Restart the Intelligence Server to pick up the variables.

Content of /tmp/krb/krb5.conf

[libdefaults]default_realm = LABS.Strategy.COMdns_lookup_kdc = true #default_tkt_enctypes = RC4-HMAC ##comment out if error below#default_tgs_enctypes = RC4-HMAC ##comment out if error below#permitted_enctypes = RC4-HMAC ##comment out if error belowforwardable = trueudp_preference_limit = 1ticket_lifetime = 3hrenew_lifetime = 7ddefault_keytab_name = /root/krb/krb5.keytab[realms]LABS.Strategy.COMkdc = labs-dc3-was.labs.Strategy.com:749admin_server = labs-dc3-was.labs.Strategy.com:749}CORP.Strategy.COM = {kdc = corp-dc-hgh.corp.Strategy.com:88

admin_server = copr-dc-hgh.corp.Strategy.com:749}[domain_realm].labs.Strategy.com = LABS.Strategy.COMlabs.Strategy.com = LABS.Strategy.COM.corp.Strategy.com = CORP.Strategy.COM corp.Strategy.com = CORP.Strategy.COM

If you see the following error, you may need to comment out or choose the correct encryption types via

klist -e /tmp/krb/krb5cc

Sample DSN

[pg_krb_native]Description=PostgreSQL Unicode(x64)Driver=/opt/mstr/MicroStrategy/install/pgsql-11/lib/psqlodbcw.soDatabase=postgresServername=tec-1-013063.labs.Strategy.comPort=5432Protocol=ReadOnly=RowVersioning=ShowSystemTables=ConnSettings=SET WORK_MEM=51200DriverUnicodeType=1UseDeclareFetch=1Fetch=1000UserName=krbusertd

Connect to Strategy Web as an administrator (or any Strategy user)

• Create a database role using the DSN with the Kerberos user. The password can be a dummy one.
  

• See that connectivity is ok and SQL can be executed successfully.
  

Connect to Strategy Developer as an administrator (or any Strategy user)

• Clear any settings for Kerberos pass-through or Shared Kerberos.
• Create a database instance and add it to the Project. In the database login, input the Kerberos user; the password can be a dummy.
  

• Run the Warehouse Catalog.