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:

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
            REVOKE rolname FROM username;
        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 Search Domain yes Specifies LDAP (AD) search domain DC=example,DC=com
LDAP Search User yes Specifies name of user used to search for authenticated user ro-admin
LDAP Search User Password yes Specifies password for user used to search for authenticated user examplepassword123
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)
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.

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 md5, 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 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
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 array value Values in this columns are used for providing extra options for some authentication methods. For now, only authentication method using this is LDAP Authentication Method, which reads from this column three values: ldap url, ldap prefix and ldap suffix i.e. "{"ldap://server.com:389","CN=,"," CN=Users, dc=server, dc=com"}".

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.

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 server url, LDAP prefix and LDAP suffix.

Let's assume that we want to authenticate user named user using LDAP server with url ldap://server.example.com:389 and call binding like CN=user,CN=Users, DC=example, DC=com. Then ldap options we should set are as below: * LDAP server url - ldap://server.example.com:389 * LDAP prefix - CN= * LDAP suffix - ,CN=Users, DC=example, DC=com

After realizing what values for each option we want to set, next write three of them as array value inside options column. The set value should match the below pattern:

{"<ldap_url>","<ldap_prefix>","<ldap_suffix>"}

For our example value would look like:

{"ldap://server.example.com:389","CN=",",CN=Users, DC=example, DC=com"}

Please notice the quotes, which are protecting commas inside from being processed as separators of array value. That's important, because prefix and suffix often would contain commas important for working of LDAP Authentication.

LDAP authentication in SQL Driven Authentication is using Simple Mode of LDAP (AD) Authentication, so for more information about meaning of parameters inside options columns, please read Simple Mode subsection in LDAP (AD) Authentication section.

Extra information about advanced configuration

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