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:

  • Passthrough (Postgres & SQL Server)
  • Proxy Authenticated (all)
  • SQL Authenticated (all)
  • LDAP (AD) Authentication (Postgres & SQL Server)

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, but is not supported by MySQL.

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.

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.

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

Database Authentication Synchronization

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:

synchronization query:

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

The PGPLSQL source for the "heimdall.sync_user":

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

CREATE OR REPLACE FUNCTION heimdall.sync_user(username text, password text, groups text)
    RETURNS void
    LANGUAGE plpgsql
AS $function$
declare 
    command text;
    grouplist text[];
    g text;
    row RECORD;
begin
    -- do not synchronize any user that is flagged as superuser
    if exists ( select usesuper from pg_user where user = username) then
        raise notice 'User is superuser, not synchronizing';
        return;
    end if;

    -- first create the role with login access and set the password if it does not exist
    IF NOT EXISTS ( select FROM pg_catalog.pg_roles WHERE rolname = username ) then
        command := 'create user '||username||' password '' '||password||' '' ';
        EXECUTE command;
    ELSE -- existing users, remove role membership from user and set password
        command := 'alter role '||username||' with password '' '||password||' '' ';
        EXECUTE command;
        FOR row IN SELECT rolname FROM pg_roles WHERE pg_has_role( username, oid, 'member') and rolname != username
        LOOP
              sql := 'revoke '||rolname||' from '''||username||'''';
                EXECUTE sql;
        END LOOP;
    END IF;

    -- make sure groups are created
    IF (groups = '') IS NOT true then
        raise notice 'adding groups: %', groups;
        grouplist := string_to_array(groups, ',');
        for i in array_lower(grouplist,1) .. array_upper(grouplist,1) loop
            IF NOT EXISTS ( select FROM pg_catalog.pg_roles WHERE rolname = replace(grouplist[i],'"','') ) then
                command := 'create role '||grouplist[i];
                -- raise notice 'command: %', command;
                begin
                    execute command;
                exception when others then
                    raise notice 'group already exists';
                end;
            end if;
            -- now grant membership of the role to the user
            command := 'grant '||grouplist[i]||' to '|| username;
            -- raise notice 'command: %', command;
            execute command;
        end loop;
    END IF;
    return;
END;
$function$
;

This function 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.

LDAP (AD) Authentication

LDAP (AD) Authentication provides authentication inside proxy with using of LDAP (AD) server. LDAP (AD) Authentication provides two modes of authentication: * Bind + Search Mode - authentication is made by binding to server as admin and searching information about authenticated user; * Simple Mode - authentication is made by binding to server as authenticated user.

Bind + Search Mode

Bind + Search Mode can be turned on by choosing Active Directory (LDAP) Auth Enabled checkbox in Proxy Configuration section of Virtual Database Configuration.

To configure Bind + Search Mode, below options can be set.

Ldap option Required? Description Example value
LDAP(S) URL yes Specifies url of LDAP (AD) server ldap://server.example.com:389
LDAP Security Principal yes Specifies name of user used to search for authenticated user CN=ro-admin,CN=Users,DC=example,DC=com
LDAP Search User Password yes Specifies password for user used to search for authenticated user examplepassword123
LDAP Search Domain yes Specifies LDAP (AD) search domain, the domain in which authenticated user's groups will be searched. DC=example,DC=com
LDAP User Search Base yes Specified LDAP (AD) user search base in which authenticated user will be searched. CN=Users,DC=example,DC=com
LDAP Name Attribute yes Specifies name attribute by which authenticated user sAMAccountName
LDAP Group Name Attribute no Optional, specifies group's name attribute which should be read during extracting authenticated user's groups. If not provided, LDAP Name Attribute will be used instead. CN
LDAP Group Filter no Optional, option used during searching for authenticated user. Setting this option limits the number of groups to search user into them, only to particular group inside server. (DistinguishedName=CN=group1,CN=Users,DC=example,DC=com)
Use nested groups filter - Specifies if parent groups should be included, when searching for user's groups. -
Ignore LDAP Cert - Specifies if TLS validation of LDAP server certificate should be performed -
Synchronize DB Authentication - Specifies if synchronization of user and groups should be performed -

By choosing option Simple Ldap Mode, you can switch to Simple Mode.

Advanced group filter

LDAP group filter is used to limit number of groups for look up for authenticated user. Example value describes filter limiting number of groups to only one particular group, but this option value is added as written, what enables writing more complex filters. Please look on below example.

Let's assume that we want our user be from groups group1 or group2. By knowing the syntax of search filter used in LDAP server we can set LDAP Group Filter as:

(|(DistinguishedName=CN=group1,CN=Users,DC=example,DC=com)(DistinguishedName=CN=group2,CN=Users,DC=example,DC=com))

Simple Mode

Simple Mode is simpler mode than Bind + Search mode, because uses only one request (which is binding request) to LDAP (AD) server.
Simple Mode can be turned on by choosing Active Directory (LDAP) Auth Enabled checkbox, and then choosing Simple LDAP Mode checkbox. Simple Mode requires to set below options to work properly.

Ldap option Required? Description Example value
LDAP(S) URL yes Specifies url of LDAP (AD) server ldap://server.example.com:389
LDAP Prefix yes Specifies prefix used in during bind authenticated user to LDAP (AD) server CN=
LDAP Suffix yes Specifies suffix used in during bind authenticated user to LDAP (AD) server ,CN=Users, DC=example, DC=com

To understand working of this mode, should be known that send request is binding composed of three parts: prefix + username + suffix. For given example values and user exampleuser, binding request would look like CN=exampleuser,CN=Users, DC=example, DC=com.

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.

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 3 types of columns: filtering columns, authentication columns and connection 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 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 specifies 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.

Example usage of advanced configuration

Advanced configuration can be used in below way. On start set authentication query as below in VDB configuration.

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

Next declare table used to authentication as below:

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
    "type" text NULL, -- trust,reject,ldap or password
    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
);

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."type" IS 'trust,reject,ldap or password';
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';

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);

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.
+ 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.
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).

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"'.
ldapbindpasswd Value assigned to this keyword should specify password for user to bind to directory to perform the search during Search+Bind Mode e.g. 'ldapbindpasswd="password"'.
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"'.
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.
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.

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 (AD) 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 (AD) 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