Proxy Authentication

Before allowing queries through the proxy, they must first be authenticated by the proxy (in general). There are various configuration modes for this, and what is supported depends on the type of database on the back-end.

Authentication Modes

Heimdall supports four methods:

• None Authenticated (all)
• Proxy Authenticated (all)
• SQL Authenticated (all)
• Passthrough (Not MySQL)
• LDAP Authentication (Not MySQL)
• Kerberos Authentication (PostgreSQL, Greenplum, Redshift, SQLServer)

In Proxy Authentication, the username and password to be used is configured directly on the Heimdall management server. The credentials must match what is also configured on the database. All proxies support this method.

On AWS environment this configuration supports specifying a Secret Name instead of user's credentials directly. In such case these values will be fetched from the AWS Secrets Manager and cached. Proxy then will allow connection with credentials that match such username and password. This method does not currently support rotating credentials, in case of changes on AWS Secrets Manager (and on the database possibly) proxy will still use old values, this can be manually updated by commiting the VDB configuration which triggers fetching of credentials from AWS.

It is possible to define proxy users using secrets based on regular expressions (regex). In this case, the username field accepts a regex pattern, and the secret name field can be specified to match based on the group of users identified by the regex pattern, as shown in the attached screenshot. During proxy authentication, it is checked whether the authenticating user matches any of the defined regex patterns. If a match is found, AWS Secret Manager is queried for the corresponding credentials, and the authentication process proceeds.

Example:

1. In the configuration, you have the username set as "(.*)" and the secret name as "secret/{$1}", as shown in the screenshot below.

2. A login attempt is made by user "test", who provides their username and password.

3. The proxy checks the provided username against the predefined regex, and a match is found. At this point, the secret name is also determined. The placeholder {$1} will be replaced by groups 1 from the matched regex in the username field. In this case, the resulting secret name will be: "secret/test".

4. The proxy queries the Secret Manager for credentials under this secret name, and if they match the user's input, the user will be authenticated.

It is also possible to define more than one regex group in the username field and then use them in the secret name as {$1}, {$2}, etc.

For those utilizing a custom secrets manager, it is imperative to adhere to the structure outlined in AWS RDS secrets manager. For instance, the JSON format should resemble the following: {"username":"test","password":"test"}.

Next, SQL Authenticated leverages a table on the database itself that stores details in a format roughly matching the format of the Postgres pg_hba.conf file, although this format supports all supported proxies. See the details below on the creation and use of this table. SQL Authentication also supports setting various attributes for the users on a per-user basis in a way not supported by other methods, and can layer with passthrough authentication as well.

In passthrough, the client is requested to send the credentials in a way so that they can be passed through to the database server, at which point the DB performs the initial authentication. This allows single point of configuration for the password, but is not supported by MySQL.

In none authentication mode, the client is always authenticated no matter what credentials he will send. It's inheriting credentials from default datasource and then authenticate user. It can be used for test purposes, without changing the connection string on the client side.

Finally, for Postgres and SQL Server, LDAP (Active Directory) authentication is supported, which includes active directory group extraction.

Database Authentication Synchronization

"Synchronize DB Authentication" button - specifies if synchronization of users and groups should be performed. Moreover, it allows configuring how often the synchronization is allowed to perform after reconnection (Sync Interval) and how long that data will stay in Authentication Cache (Auth Cache Expiration Time).

When this feature is enabled, it allows the credentials that Heimdall has accepted to be synchronized into the database itself--there is no point to authenticate against the proxy unless the database ALSO allows the user to authenticate. In some cases, Heimdall and the DB can both authenticate against the same source. In other cases, the proxy must be able to synchronize the credentials into the database at runtime. For this to operate, credentials for a privileged user must be provided, along with a command that will allow the users to be synchronized.

Example for Postgres and Postgres derived databases such as Greenplum (excluding Redshift):

Synchronization query:

select heimdall.sync_user('${user}','${password}','${ldapgroups}');

The PL/pgSQL source of the "heimdall.sync_user" is available here.

Example for Redshift:

Synchronization query:

CALL heimdall.sync_user('${user}','${password}','${ldapgroups}');

The PL/pgSQL source (adjusted for Redshift) of the "public.sync_user" is available here.

Example for SQL Server:

Synchronization query:

EXECUTE heimdall.sync_user N'${user}', N'${password}', N'${ldapgroups}' [, TRUE | FALSE ]

[ TRUE | FALSE ] - An optional parameter, specifies whether roles should be created if missing and membership should be added to the user. When set to TRUE, roles are created and membership is added to the user. If set to FALSE, roles are not created, and membership is not added.

The example of Transact-SQL source of the "dbo.sync_user" is available here.

The source code of these functions/procedures is also stored in config/scripts in heimdall installation directory (under name create-sync-user-procedure.sql) and can be modified if needed. If any of them are missing, the manager will put default versions of them on startup.

These functions will first ensure that the user's role is created. If it already exists, it will drop other role memberships and reset the password. Next, it will add all existing roles back to the user's role.

Important: If synchronization query contain the information about '${ldapgroups}' then for LDAP Authentication and LDAP Authentication inside SQL Driven Authentication it will be required to extract at least one group to authenticate the user.

LDAP Authentication

By default, each proxy use the LDAP Configuration of central manager created in Admin tab.

• Bind+Search Mode
• Simple Mode

If there's only one LDAP Configuration, it will be automatically used by default for proxy authentication. However, if multiple LDAP Configurations are established, ensure to designate the appropriate one for each specific VDB within the Proxy Configuration section found in the Virtual DB tab.

SQL Driven Authentication

Simple configuration

The simplest way to configure an authentication by SQL Driven Authentication is to turn on mentioned option in "Proxy Configuration" section of Virtual DB Configuration and provide query as "Authorization Query"

Example authorization query can be declared as below.

select password from heimdall.pg_hba where user_name = '${user}';

Used authorization query expects declared table like below example in database.

CREATE SCHEMA IF NOT EXISTS heimdall;
REVOKE ALL ON SCHEMA heimdall FROM public;

CREATE TABLE heimdall.pg_hba (
    user_name text NULL, -- username (exact)
    "password" varchar NULL -- user's password
);

Provided example will provide authentication of given user by password returned as result from Authorization Query.

SQL Query Variables

The authentication query supports the following variables, which are filled in before querying the database:

• ${user}: The username of the connecting user
• ${source}: The client IP of the connecting user, as seen at the socket level (i.e. NAT translation may hide this).

If additional variables are desired, please contact Heimdall support to submit a feature request.

SQL Authentication Caching

In SQL driven authentication credentials retrieved for a given users are cached for one minute.
There is a possibility to clear those credentials caches for a specific proxy. You have to go to into status tab, expand collapse menu button (burger button) and chose "Clear credentials properties" option. More information might be found under this link.

Advanced configuration, with connection pool properties control

To start with advanced configuration, first should be realized that whole process of authentication depends on data table containing rows with information about rules for declared situations of authorization. On side of proxy the only thing which can be configured is query which calls for result from database. That means that whole authentication process can be elastic and is only limited by database authentication table content and by authentication query.

SQL Driven Authentication provides 4 different authentication options, which can be declared in authentication table in column "auth_method".

Authorization option	Option keyword	Description
Server Authentication	scram-sha-256, trust, pam, bsd	Pass the authentication through to the server, and only allow if the server accepts. This is the default if no auth_method is provided or the value is null, or an unknown value is returned
Password authentication option	md5, password	Authentication is performed by comparing password provided by user during authorization and "password" column read from result row matching user.
Reject option	reject	Unconditionally rejects connection made to proxy by user.
LDAP authentication option	ldap	LDAP authentication option enables authentication by using ldap server. To use this authentication, required is to provide information about ldap server inside "options" column of result.

Note: The MySQL proxy only supports the password authentication when SQL authentication is used.

SQL Driven Authentication is based on values inside columns of result set. There are 4 types of columns: filtering columns, authentication columns, connection columns and override columns.

Filtering columns

Filtering columns specify if given row of result matches user connecting to proxy. These include:

Filter column label	Type	Description
connection	string	Value in this column specifies what type of connection was used by a user to connect to a proxy. If column doesn't exist in result or value is NULL or value isn't a specified keyword, then authentication rule applies to all situations.
user_name	array value	Value in this column specifies to which user name rule should be applied. If column doesn't exist in result or value is NULL or value is {all}, then authentication rule applies to all users.
database	array value	Value in this column specifies to which database connection is made. If column doesn't exist in result or value is NULL or value is {all}, then authentication rule applies to all users.
address	string	Value in this column specifies from which IP address user is connecting to proxy. If "netmask" column's value exist, then can be considered as range of IP addresses. If column doesn't exist in result or value is NULL or value is {all}, then authentication rule applies to all users. Works with IP address of IPv4 and IPv6.
netmask	string	Value in this column is only considered if value exist in column "address" of result. Value should be netmask address i.e. "255.255.255.0" or "ffff:ffff:ffff:ffff:ffff:0000:0000:0000". When specified, "address" column specifies range of IP addresses inside of specified net. If not specified, then considered as netmask length 32.

Authentication columns

Authentication columns specifies options used during authentication. There are 3 columns, which are used as authentication columns.

Authentication column label	Type	Description
auth_method	string	Value in this column declares what authentication method should be used. If column doesn't exist in result or is NULL, then default authentication option is password authentication.
password	string	Value in this column contains password which is used in password authentication inside proxy.
options	special formatted string	Values in this columns are used for providing extra options for some authentication methods. Expected value in this column in string containing pairs key="value", where pairs are separated by spaces e.g. 'key1="value1" key2="value2"'. For now, only authentication method using this is LDAP Authentication Method, which is using defined keywords to extract proper values (information about ldap keywords can be found in "LDAP Authentication inside SQL Driven Authentication" section).

Connection columns

Connection columns specify optional properties which will be set after successful authentication. There are 3 columns, which are used as connection columns:

Connection column label	Type	Description
pool:multiplex	bool	Set to override default multiplex setting for user, rules can further override.
pool:maxUserIdle	integer	Set to override default max idle setting for user, rules can further override.
pool:maxUserActive	integer	Set to override default max active setting for user, rules can further override.

In this case, fields are being defined that match to the properties that the "pool" rule types inject into a query's metadata. This allows a user's multiplex, maxUserIdle, and MaxUserActive settings to be set at a default level on login without even matching a rule. Other rule behaviors can be triggered in the same way. To see the exact property that a rule injects, you would use the "debug" logging on the vdb, which will print as part of the logging the exact property name and the value injected.

Note: As these columns match to rule properties, any rule property can actually be set, these are simply examples that are connection oriented and help set the proper behavior for the connection. Use verbose debugging to inspect properties that are used after a rule match, and any such properties can be used as a column header for advanced control.

Override columns

Override columns specify values that should override connection properties after successful authentication on proxy side. There are 3 columns which are used as override columns:

Override column label	Type	Description
db_username	string	Set to override username that is used for connection after successful authentication on proxy side. If no such column exists or value is empty/null then user_name column is used.
db_password	string	Set to override password that is used for connection after successful authentication on proxy side. If no such column exists or value is empty/null then password column is used.
db_database	string	Set to override database name that is used for connection after successful authentication on proxy side. This would mean that we can redirect connection to different database than one supplied in database column. If no such column exists or value/null is empty then database column is used.

These columns can be used to create connection with different values than these used for proxy authentication. For example, we can redirect connection to other database/schema or map user-friendly name to say “000001” as the user on the DB side.

Note: Some PostgreSQL clients will display database column value instead of db_database, event though they are connected to correct one, because they get this information directly from user input and don't query to find which database got connected.

Example usage of advanced configuration

Advanced configuration can be used in below way. On start set authentication query as below in VDB configuration. SQL Server does not support boolean types, so instead of true, use 1.

select * from heimdall.pg_hba where enabled = true order by line_number asc

To create a table used to authentication for Postgres, Redshift, MySQL, and SQL Server, you can execute the appropriate script in the Data Source tab or do it manually on the database. Below is an example for Postgres. For other types of databases, you must remember that the column types may differ slightly.

CREATE SCHEMA IF NOT EXISTS heimdall;
REVOKE ALL ON SCHEMA heimdall FROM public;

CREATE SEQUENCE IF NOT EXISTS pg_hba_seq INCREMENT BY 5 START WITH 10;
CREATE TABLE IF NOT EXISTS heimdall.pg_hba (
    line_number int4 NULL,
    enabled bool NOT NULL DEFAULT true, -- controls if the row is active
    connection text NULL, -- all, host, local, hostssl, or hostnossl
    address text NULL, -- IPv4 or IPv6 subnet or address
    netmask text NULL, -- Netmask for address, defaults to /32
    "database" _text NULL, -- array value, use {any} or null for all databases, or provide the names of the database names (exact match).
    user_name _text NULL, -- array value, use {any} or null for all users, or provide the names of the users (exact match).
    "password" varchar NULL, -- The password to use for the password type, must be provided
    ldapgroups varchar NULL, -- csv group names, as if pulled from ldap for rule processing
    auth_method text NULL, -- trust, reject, ldap or password, null=password
    "options" _text NULL, -- ldap options
    "pool:multiplex" bool NULL, -- set to override default multiplex setting for user, rules can further override
    "pool:maxUserIdle" int4 NULL, -- set to override default max idle setting for user, rules can further override
    "pool:maxUserActive" int4 NULL, -- set to override default max active setting for user, rules can further override
    db_username text NULL, -- set to override username that is used for connection after successful authentication on proxy side                                                                                                             
    db_password text NULL, -- set to override password that is used for connection after successful authentication on proxy side                                                                                                               |                                                                                                                  
    db_database text NULL -- set to override database name that is used for connection after successful authentication on proxy side
);

Next (optional) you can add column comments:

COMMENT ON COLUMN heimdall.pg_hba.enabled IS 'controls if the row is active';
COMMENT ON COLUMN heimdall.pg_hba.connection IS 'all, host, local, hostssl, or hostnossl';
COMMENT ON COLUMN heimdall.pg_hba.address IS 'IPv4 or IPv6 subnet or address';
COMMENT ON COLUMN heimdall.pg_hba.netmask IS 'Netmask for address, defaults to /32';
COMMENT ON COLUMN heimdall.pg_hba."database" IS 'array value, use {any} or null for all databases, or provide the names of the database names (exact match).';
COMMENT ON COLUMN heimdall.pg_hba.user_name IS 'array value, use {any} or null for all users, or provide the names of the users (exact match).';
COMMENT ON COLUMN heimdall.pg_hba."password" IS 'The password to use for the password type, must be provided';
COMMENT ON COLUMN heimdall.pg_hba.ldapgroups IS 'csv group names, as if pulled from ldap for rule processing';
COMMENT ON COLUMN heimdall.pg_hba.auth_method IS 'trust, reject, ldap or password, null=password';
COMMENT ON COLUMN heimdall.pg_hba."options" IS 'ldap options';
COMMENT ON COLUMN heimdall.pg_hba."pool:multiplex" IS 'set to override default multiplex setting for user, rules can further override';
COMMENT ON COLUMN heimdall.pg_hba."pool:maxUserIdle" IS 'set to override default max idle setting for user, rules can further override';
COMMENT ON COLUMN heimdall.pg_hba."pool:maxUserActive" IS 'set to override default max active setting for user, rules can further override';
COMMENT ON COLUMN heimdall.pg_hba.db_username IS 'set to override username that is used for connection after successful authentication on proxy side';
COMMENT ON COLUMN heimdall.pg_hba.db_password IS 'set to override password that is used for connection after successful authentication on proxy side';
COMMENT ON COLUMN heimdall.pg_hba.db_database IS 'set to override database name that is used for connection after successful authentication on proxy side';

Note: the way this is being done, the row numbers do not need to be unique, but will be used to order precedence for authentication.

Helper command to import into public.pg_hba (defined above) from the Postgres pg_hba_files_rules (PG 10+):

insert into heimdall.pg_hba (select nextval('pg_hba_seq'), true, "type", address, netmask, "database", user_name, null, null, auth_method from pg_hba_file_rules);

If you want to add a new entry, you can do so as follows (including enabling multiplexing for this user). Note, the user field is an array, in the event multiple accounts should use the same configuration:

insert into heimdall.pg_hba values (nextval('pg_hba_seq'), true, 'host', null, null, null, array ['user1'], 'newpassword', array ['group1','group2','group3'], 'password', null, true, null, null, null, null, null);

Remember that only Postgres supports column types as arrays. For other databases, you can provide multiple values as a string within {}, separating the values with commas, e.g., '{user1, user2}'.

Filter columns keywords

Filter columns have specified keywords, which can be used to change behavior of rule declared in the given row of result set.

Connection column keywords

Inside connection column can be user below keywords to change use of set rule.

Keyword	Description
all, host, local	Rule declared in this row will be used for all situations.
hostssl	Rule declared in this row will be used only when connection is made to a proxy with using SSL encryption.
hostnossl	Rule declared in this row will be used only when connection is made to a proxy without using SSL encryption.

User_name column keywords

Inside user_name column can be user below keywords to change use of set rule.

Keyword	Description
all	Rule declared in this row will be used for all users.
+<role_name>	Rule declared in this row will be used for users that are directly or indirectly members of given role

Database column keywords

Inside database column can be user below keywords to change use of set rule.

Keyword	Description
all	Rule declared in this row will be used for all database connections.
sameuser	Rule declared in this row will be used for database connections, which requested database name is the same as requested user name.
samerole/samegroup	Rule declared in this row will be used for database connenctions, which requested user are directly or indirectly member of role with name same as database name.

Address column keywords

Inside address column can be user below keywords to change use of set rule.

Keyword	Description
all	Rule declared in this row will be used for all addresses.
samehost	Rule declared in this row will be used for addresses that matches the server's IP addresses.
samenet	Rule declared in this row will be used for addresses that matches any subnet, which server is directly connected to.
<host_name>	Rule declared in this row will be used for addresses matching given host name. Check of this keyword is made in two steps: first is made reverse name resolution of client's IP address and checked if is the same as given host name. In secodn step is performed forward name resolution of given host name and comparison of IP addresses is done. If given host name starts with a dot (i.e. ".server.com") then only reverse name resolution of client's IP address and comparison is done (i.e. for set host name ".server.com", if client IP returns as host name "heimdalldata.server.com", then it matches, if client's host name is "server.com", then comparison returns false).

Override columns example

Let's say we have defined authorization query like this.

select * from heimdall.pg_hba

And filled our authentication table with following values:

user_name	password	database	db_username	db_password	db_database
user1	password1	db1	root	root	master
user2	password2	db2	<empty or null>	<empty or null>	<empty or null>

For given credentials like:

user: user1
password: password1
database: db1

After filtering results of query with filter columns we take the first row resulting in successful authentication on proxy side and then the connection to native database will be established with these properties:

user: root 
password: root
database: master

For given credentials like:

user: user2
password: password2
database: db2

Matched row here will be the second one but credentials won't be overridden because override columns are empty or null.

LDAP Authentication inside SQL Driven Authentication

SQL Driven Authentication enable authentication by using LDAP server. To use it properly two options should be given:

• value in column auth_method should be set as ldap;
• value in column options should contain information about LDAP defined by proper keywords.

General keywords

Keyword	Description
ldapserver	Value assigned to this keyword should be names or IP addresses of LDAP servers, which should be used for authentication. Multiple servers can be specified by separating them with spaces e.g. 'ldapserver="server1.com server2.com"'. If multiple servers are specified then only one of them have to authenticate user.
ldapport	Value assigned to this keyword should be port number on LDAP server, which should be used to make connection. If keyword isn't specified then port 389 is used. E.g. 'ldapport=636'.
ldapscheme	Value assigned to this keyword can define if TLS should be used during connection to LDAP server. TLS can be set by setting value "ldaps" e.g. 'ldapscheme="ldaps"'.
ldaptls	Value assigned to this keyword can define if TLS should be used during connection to LDAP server. TLS can be set by setting value "1" e.g. 'ldaptls="1"'.

Simple Bind Mode keywords

Keyword	Description
ldapprefix	Value assigned to this keyword is string to prepend to the user name when forming the DN to being used for bind in Simple Bind Mode e.g. 'ldapprefix="CN="'.
ldapsuffix	Value assigned to this keyword is string to append to the user name when forming the DN to being used for bind in Simple Bind Mode e.g. 'ldapsuffix=",CN=Users, DC=example, DC=com"'.

Search+Bind Mode keywords

Keyword	Description
ldapbasedn	Value assigned to this keyword should be root DN to start searching for user during Search+Bind Mode e.g. 'ldapbasedn="CN=Users,DC=example,DC=com".
ldapbinddn	Value assigned to this keyword should specify user to bind to directory to perform the search during Search+Bind Mode e.g. 'ldapbinddn="admin"' or 'ldapbinddn="CN=admin,CN=Users,DC=example,DC=com"'.
ldapsecbinddn	Value assigned to this keyword should specify user to bind to directory to perform the search if the search with ldapbinddn fails.
ldapsearchdn	Value assigned to this keyword should specify the domain in which authenticated user's groups will be searched.
ldapbindpasswd	Value assigned to this keyword should specify password for ldapbinddn user to bind to directory to perform the search during Search+Bind Mode e.g. 'ldapbindpasswd="password"'.
ldapsecbindpasswd	Value assigned to this keyword should specify password for ldapsecbinddn user to bind to directory to perform the search during Seach+Bind Mode.
ldapsearchattribute	Value assigned to this keyword should be attribute to match against user name in the search during Search+Bind mode e.g. 'ldapsearchattribute="sAMAccountName"'.
ldapgroupsearchattribute	Value assigned to this keyword should be an attribute to match against group identifying attribute value, in the Search+Bind mode e.g. 'ldapgroupsearchattribute="cn"'. Setting this keyword makes it required to extract at least one group to be authenticated.
ldapusenestedgroupsfilter	Value assigned to this keyword should describe if nested groups filter should be used during a search for names of groups for an user. Accepted values are "true" and "false". If this value isn't set, by default it is set to "false".
ldapsearchfilter	Value assigned to this keyword should be search filter used instead "ldapsearchattribute". All occurrences of "$username" will be replaced by user name. E.g. 'ldapsearchfilter="(sAMAccountName=$username)"'.
ldapurl	Value assigned to this keyword allows to declare some of other LDAP options in more compact form. For information about proper using of this keyword, check LdapUrl Keyword section.
ldapservertype	Value assigned to this keyword must be one of the following: ACTIVE_DIRECTORY, REDHAT_IDM, OPENLDAP, JUMPCLOUD, OKTA, OTHER.

LdapUrl Keyword

Keyword "ldapurl" is used to declare multiple LDAP options in single declaration. However, it requires special value format to declare options properly. Format is:

ldap[s]://host[:port]/basedn[?[attribute][?[scope][?[filter]]]]

The possible options specified in the format are:

• ldap/ldaps - have to be ldap or ldaps, set ldap to make connection to LDAP server without TLS, ldaps for connection with TLS;
• host - required value, works similar to ldapserver, but only one server can be declared
• port - optional value, works as ldapport
• basedn - required value, works as ldapbasedn
• attribute - required if filter isn't declared, works as ldapsearchattribute
• scope - optional value, declare to specify scope used during searching for user, possible values are: base for baseObject scope, one for singleLevel scope, sub for wholeSubtree scope. If not defined, then default is base scope.
• filter - required if attribute isn't declared, works as ldapsearchfilter.

Examples

Let's assume that we want to Simple Bind authenticate to LDAP server with below options: * LDAP server url - ldap://server.example.com:389 * LDAP prefix - CN= * LDAP suffix - ,CN=Users, DC=example, DC=com

Then value in options column should look like:

ldapserver="server.example.com" ldapprefix="CN=" ldapsuffix=",CN=Users, DC=example, DC=com"

Let's assume that we want to Search+Bind authenticate to LDAP server with below options:

• LDAP server url - ldap://server.example.com:456
• LDAP base DN - CN=Users,DC=example,DC=com
• LDAP search attribute - sAMAccountName
• LDAP bind DN - binduser
• LDAP bind password - examplepassword

A value in options column should look like:

ldapserver="server.example.com" ldapport=456 ldapbasedn="CN=Users,DC=example,DC=com",ldapsearchattribute="sAMAccountName" ldapbinddn="binduser" ldapbindpasswd="examplepassword"

If we would want to specify LDAP bind DN as CN=binduser,CN=Users,DC=example,DC=com then value in options columns could look like:

ldapserver="server.example.com" ldapport=456 ldapbasedn="CN=Users,DC=example,DC=com",ldapsearchattribute="sAMAccountName" ldapbinddn="CN=binduser,CN=Users,DC=example,DC=com" ldapbindpasswd="examplepassword"

If we would want to use ldapurl keyword instead, then value in options column could look like:

ldapurl="ldap://server.example.com:456/CN=Users,DC=example,DC=com?sAMAccountName?sub" ldapbinddn="CN=binduser,CN=Users,DC=example,DC=com" ldapbindpasswd="examplepassword"

Let's assume that we want to Search+Bind authenticate to LDAP server with below options: * LDAP server url - ldaps://server.example.com:678 * LDAP base DN - CN=Users,DC=example,DC=com * LDAP search filter - (|(sAMAccountName=$username)(uid=$username)) * LDAP bind DN - binduser * LDAP bind password - examplepassword

A value in options column should look like:

ldapserver="server.example.com" ldapport=678 ldapscheme="ldaps" ldapbasedn="CN=Users,DC=example,DC=com" ldapsearchfilter="(|(sAMAccountName=$username)(uid=$username))" ldapbinddn="binduser" ldapbindpasswd="examplepassword"

or:

ldapserver="server.example.com" ldapport=678 ldaptls=1 ldapbasedn="CN=Users,DC=example,DC=com" ldapsearchfilter="(|(sAMAccountName=$username)(uid=$username))" ldapbinddn="binduser" ldapbindpasswd="examplepassword"

If we would want to use ldapurl keyword instead, then value in options column could look like:

ldapurl="ldaps://server.example.com:678/CN=Users,DC=example,DC=com??sub?(|(sAMAccountName=$username)(uid=$username))" ldapbinddn="binduser" ldapbindpasswd="examplepassword"

If we would want to change or scope to base, then value in options column should look like:

ldapurl="ldaps://server.example.com:678/CN=Users,DC=example,DC=com??base?(|(sAMAccountName=$username)(uid=$username))" ldapbinddn="binduser" ldapbindpasswd="examplepassword"

or:

ldapurl="ldaps://server.example.com:678/CN=Users,DC=example,DC=com???(|(sAMAccountName=$username)(uid=$username))" ldapbinddn="binduser" ldapbindpasswd="examplepassword"

If we would want to change or scope to one, then value in options column should look like:

ldapurl="ldaps://server.example.com:678/CN=Users,DC=example,DC=com??one?(|(sAMAccountName=$username)(uid=$username))" ldapbinddn="binduser" ldapbindpasswd="examplepassword"

Extra information about advanced configuration

There are some information which may be helpful before preparing authentication table and authentication query:

• if there is more than one row which matching to user properties, then first is taken from result set (that's why sorting of result may change authentication process);
• if there is no row matching to user properties, then authentication is rejected;
• there are three characters which can cause a problem if they are in value inside array: '{' and '}' as they are removed; '"' as quotes are used to protect commas in value (required for providing ldap options);
• if you don't want to use or can't use array type of column then can be used text column, but value inside should match pattern of array value i.e. {"user1", "user2"};
• if using text column instead of array column, recommended is to use array pattern like '{"user1","user2"}', but possible is to use without curly brackets like '"user1","user2"' and even without quotes like 'user1, user2';
• keywords inside array value columns can be joined i.e. in column user_name can be declared array value like '{"user1", "+role1"}', what means, that this rule will be used for requested user user1 or requested users who are members of role role1.

Kerberos/GSSAPI Authentication

Currently, Heimdall supports Kerberos authentication for PostgreSQL and SQLServer.

Once we set the authentication mode to Kerberos/GSSAPI, we will only need to fill in one field, which is the Keytab location. We will use that Keytab to identify as a service in the Kerberos realm. The service principal is as follows:

For PostgreSQL -> postgres/hostname
For SQLServer -> MSSQLSvc/hostname & MSSQLSvc/hostname:port

Heimdall proxy will automatically retrieve the hostname from the system. Please ensure that the "hostname" command returns the fully qualified domain name of the host.

In case of using multiple proxies, the Keytab location will be common for all proxies.

Instructions on how to add a Heimdall proxy instance to Active Directory:

One way to do it is:

1. Add our instance to the DNS server.
2. Join the instance to Active Directory.
3. Instructions for Amazon Linux: https://docs.aws.amazon.com/directoryservice/latest/admin-guide/join_linux_instance.html
4. Set the service principal for the instance.
5. Go to Active Directory Users and Computers.
6. Find your instance in the Computers folder.
7. Right-click on it and go to properties.
8. Go to the Attribute Editor and find the attribute servicePrincipalName, and edit it.
9. Add 2 values: e.g.
   • MSSQLSvc/heimdall-sqlserver-kerberos.ad.heimdalldata.com:1433
   • MSSQLSvc/heimdall-sqlserver-kerberos.ad.heimdalldata.com
10. Apply the changes.
11. In order to update the keytab file, we need to leave our instance from the realm and join it again via "realm leave domainName" and "realm join domainName"
12. After rejoining our instance into the realm, we should have our MSSQLSvc service in our keytab file. We can verify it using the command "klist -k keytab path". By default, the keytab is placed in /etc/krb5.keytab.
13. After that, we can authenticate using Kerberos or Windows authentication.

You can also enable synchronization if desired.

Load Balancing

Windows Active Directory

To demonstrate the correct setup for load balancing in an Active Directory (AD) environment with Kerberos authentication, let's use the following scenario as an example.

In this setup, we'll be operating with two proxy servers. The first proxy is hosted on hd-lb-proxy3.ad.heimdalldata.com, and the second on hd-lb-proxy4.ad.heimdalldata.com. These proxies are managed by an active load balancer designed to direct connection attempts to hd-lb-win.ad.heimdalldata.com towards one of these proxies, based on the current load balancing algorithm.

Configuring this environment to support Kerberos authentication involves a sequence of five essential steps:

1. Creation of an AD User for Proxy Service
2. Assignment of a Service Principal Name (SPN) to the New User
3. Generation of a Keytab for the SPN
4. Distribution of the Keytab Across Hosts
5. Configuration of SPN and Keytab in Heimdall

IMPORTANT !!!

• In every command shown, the realm should be in uppercase, e.g., AD.HEIMDALLDATA.COM
• If you have a CNAME for a load balancer, never use the alias name for the service principal name; use the canonical name instead. Connections using the alias will still work.

Creation of an AD User for Proxy Service

Initiate by establishing an AD user account to symbolize our proxy service. This can be achieved either manually through the Active Directory Users and Computers interface or via the PowerShell command New-ADUser. Ensure the user’s password is set to never expire and Kerberos encryption type AES256 is supported.

Command: New-ADUser -Name "user_name" -UserPrincipalName "user_principal_name" -AccountPassword (ConvertTo-SecureString "user_password" -AsPlainText -Force) -Enabled $true -PasswordNeverExpires $true -KerberosEncryptionType AES256

Example: New-ADUser -Name "hdlb" -UserPrincipalName "hdlb@AD.HEIMDALLDATA.COM" -AccountPassword (ConvertTo-SecureString "user_password" -AsPlainText -Force) -Enabled $true -PasswordNeverExpires $true -KerberosEncryptionType AES256

Assignment of a Service Principal Name (SPN) to the New User

The user created is hdlb@ad.heimdalldata.com. The next step involves assigning a SPN to this user: MSSQLSvc/hd-lb-win.ad.heimdalldata.com:1433, indicating the service name, load-balanced domain, and target port, respectively. SPNs can be added manually via the "Attribute Editor" in the user's settings or through the setspn command.

Generation of a Keytab for the SPN

After the user and SPN have been established, generate a keytab file containing this SPN using the ktpass command.

Command ktpass -princ service/host:port@realm +rndPass -crypto ALL -ptype KRB5_NT_PRINCIPAL -mapuser mapped_user_principal -out keytab_path

Example ktpass -princ MSSQLSvc/hd-lb-win.ad.heimdalldata.com:1433@AD.HEIMDALLDATA.COM +rndPass -crypto ALL -ptype KRB5_NT_PRINCIPAL -mapuser hdlb@AD.HEIMDALLDATA.COM -out hdlb.keytab

Distribution of the Keytab Across Hosts

The generated keytab file must be distributed to every host that will run the proxy service to ensure seamless Kerberos authentication. The distribution method depends on the system's architecture and requirements. A simple method, for example, is to transfer the keytab using the SCP command. However, this approach may be challenging in an autoscaling environment. In such cases, for instance, within an AWS environment, the file can be stored in AWS Secrets and downloaded to a specified path when a new instance is created. This scenario is merely illustrative. It is crucial to ensure that the keytab file includes our SPN and is placed in a location specified in the Heimdall configuration (Virtual Database/Our VDB/Proxy Configuration/Keytab Location).

Configuration of SPN and Keytab in Heimdall

Once the keytab file is distributed, it's crucial to update the Heimdall configuration. This includes specifying the Service Principal Name (SPN) we're using and the location of the keytab file. In our case, we've placed the keytab files at /etc/hdlb.keytab, and the SPN designated for our setup is MSSQLSvc/hd-lb-win.ad.heimdalldata.com:1433. Below is a screenshot of the Heimdall configuration reflecting these settings:

Auto Scaling

Windows Active Directory

In an auto-scaling environment, new hosts must be automatically enrolled into the realm. To facilitate this, there is a base script available that can be customized for various purposes. You can download it here: auto-enroll-kerberos-ad.sh

Dual Authentication

The feature, available only in SQLServer Proxy, facilitates the use of Kerberos Authentication alongside passthrough authentication. However, there's a condition: users must adhere to a single authentication method and cannot switch between them interchangeably.

Dual Authentication can be enabled in VDB -> Proxy Configuration -> Dual Authentication Mode

Authentication Testing

Variety of authentication options ofter requires confirmation that actual settings are working as expected. For checking if set authorization option would work correctly, with expected as correct credentials, should be used Authentication Test tab, which is located below settings of proxy authentication.

Authentication Test tab provides all required credentials inputs to test if configured proxy authentication would work as expected. Worth mentioning is that configured authentication doesn't have to be committed to perform an authentication test. Warning: VDB configuration doesn't have to be committed, but all data sources, used by VDB, have to be committed. Any change in Data Source configuration should be followed by committing the change and refreshing page, before using Authentication Test. Only after these actions, Authentication Test results will be reliable.

Authentication testing is an option for checking if only proxy authentication confirms users credentials. Results returned by authentication testing doesn't confirm if given credentials match credentials in a database behind the proxy. If authentication testing returns failure as a result, then proper alert will be shown with description about reason of rejecting credentials.

Basic testing

Basic routine of authentication mainly expects two crucial inputs:

Input	Input name	Type	Description
username	Test User	String	Input for a name of the user
password	Test Password	String	Input for a password of the user

Authentication methods, which only based on these credentials, are: - Passthrough - Proxy Authenticated - LDAP Authentication

For testing with these methods only credentials inputs: Test User and Test Password have to be set. Other test data inputs can be ignored.

Examples

Passthrough examples

Passthrough by default passes all credentials, so every credential combination should return successful result.

Proxy Authenticated examples

Let's assume that we have two users declared for Proxy Authenticated method: user1 with password password1, and user2 with password password2. In this situation below inputs in Authentication Test should be returned as successful.

Test User: user1
Test Password: password1
Test Address IP: <empty or random>
Test Database: <empty or random>
UseSSL: <empty or random>

Test User: user2
Test Password: password2
Test Address IP: <empty or random>
Test Database: <empty or random>
UseSSL: <empty or random>

Other combinations of credentials will be returned as rejected by the proxy. Returned alert will contain information that provided credentials didn't match any of declared users, or given user password didn't match to declared password of the user.

LDAP examples

Let's assume that our proxy configuration has been set properly (see section about LDAP Authentication for information about correct configuring) and configuration is pointing to a ldap server to read users only from GROUP1, which contains user1 with password password1 and user2 with password password2. However, in the ldap server we have declared GROUP2, where we have user3 with password password3, but that user shouldn't be used for authentication by the proxy. In this situation below inputs in Authentication Test should be returned as successful.

Test User: user1
Test Password: password1
Test Address IP: <empty or random>
Test Database: <empty or random>
UseSSL: <empty or random>

Test User: user2
Test Password: password2
Test Address IP: <empty or random>
Test Database: <empty or random>
UseSSL: <empty or random>

Below credentials and others should be returned as rejected for the assumed situation.

Test User: user1
Test Password: password1
Test Address IP: <empty or random>
Test Database: <empty or random>
UseSSL: <empty or random>

Extended testing

Some proxy authentication methods requires more data than only username and password to authenticate a user. For actual authentication methods were added below data inputs:

Input	Input name	Type	Description
address	Test IP Address	String	Input for an address from which testing user is connecting to the proxy. Used for testing with SQL Driven Authentication for rules with specified address range.
database	Test Database	String	Input for a name of a database/schema name to which testing user wants to connect. Used for testing with SQL Driven Authentication for rules with specified database name.
ssl	UseSSL	Boolean	Input for a set if authentication process is made in a connection using SSL encryption. Used for testing with SQL Driven Authentication for rules with set connection type as "hostssl" or "hostnossl".

Proxy authentication methods, which uses more than basic data as credentials to authenticate users, are : - SQL Driven Authentication

SQL Driven Authentication Examples

Let's assume that we have defined a single rule for SQL Driven Authentication with below values:

connection type: hostssl
auth_method: trust

For assumed rule only below input for Authentication Test will pass. As can be seen, rule requires using SSL so all credentials with set using SSL as true will pass through proxy authentication.

Test User: <empty or random>
Test Password: <empty or random>
Test Address IP: <empty or random>
Test Database: <empty or random>
UseSSL: true

Let's assume that we have defined a single rule like:

address: 123.123.123.123
auth_method:trust

For assumed rule only below input for Authentication Test will pass. As can be seen, rule requires connection from address 123.123.123.123 so all credentials with set IP address as 123.123.123.123 will pass through proxy authentication.

Test User: <empty or random>
Test Password: <empty or random>
Test Address IP: 123.123.123.123
Test Database: <empty or random>
UseSSL: <empty or random>

Let's assume that we have defined a single rule like:

address: 123.123.123.0
netmask: 255.255.255.0
auth_method:trust

For assumed rule only below input for Authentication Test will pass. As can be seen, rule requires connection from address from network 123.123.123.0/24 so all credentials with set IP address from that network will pass through proxy authentication.

Test User: <empty or random>
Test Password: <empty or random>
Test Address IP: <any address from range 123.123.123.0-255>
Test Database: <empty or random>
UseSSL: <empty or random>

Let's assume that we have defined a single rule like:

database: {testdb1}
auth_method:trust

For assumed rule only below input for Authentication Test will pass. As can be seen, rule requires connection to database named testdb1 so all credentials with set connection to database testdb1 will pass through proxy authentication.

Test User: <empty or random>
Test Password: <empty or random>
Test Address IP: <empty or random>
Test Database: testdb1
UseSSL: <empty or random>

Let's assume that we have defined a single rule like:

connection type: hostssl
user_name: ldapuser1
database: testdb1
address: 127.0.0.0
netmask: 255.255.255.254
auth_method: ldap
options: ldapserver="ldap.example.com" ldapport=389 ldapprefix="CN=" ldapsuffix=",CN=Users, DC=example, DC=com"

Additionally, let's assume that we have declared ldapuser1 with password ldappassword1 in CN=Users, DC=example, DC=com on LDAP server ldap.example.com:389. For assumed rule only below input for Authentication Test will pass. Only below credentials will pass, because of strict rule definition and password stored on LDAP server.

Test User: ldapuser1
Test Password: ldappassword1
Test Address IP: <127.0.0.0 or 127.0.0.1>
Test Database: testdb1
UseSSL: true