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.

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 public.pg_hba where user_name = '${user}';

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

CREATE TABLE public.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 public.pg_hba where enabled = true order by line_number asc

Next declare table used to authentication as below:

CREATE TABLE public.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
);

After creating table, you can add index as below:

CREATE INDEX pg_hba_user_name_idx ON public.pg_hba USING btree (user_name, database, type, address, netmask);

Next you can add column comments as below:

COMMENT ON COLUMN public.pg_hba.enabled IS 'controls if the row is active';
COMMENT ON COLUMN public.pg_hba."type" IS 'trust,reject,ldap or password';
COMMENT ON COLUMN public.pg_hba.address IS 'IPv4 or IPv6 subnet or address';
COMMENT ON COLUMN public.pg_hba.netmask IS 'Netmask for address, defaults to /32';
COMMENT ON COLUMN public.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 public.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 public.pg_hba."password" IS 'The password to use for the password type, must be provided';
COMMENT ON COLUMN public.pg_hba.ldapgroups IS 'csv group names, as if pulled from ldap for rule processing';
COMMENT ON COLUMN public.pg_hba.auth_method IS 'trust, reject, ldap or password, null=password';
COMMENT ON COLUMN public.pg_hba."options" IS 'ldap options';
COMMENT ON COLUMN public.pg_hba."pool:multiplex" IS 'set to override default multiplex setting for user, rules can further override';
COMMENT ON COLUMN public.pg_hba."pool:maxUserIdle" IS 'set to override default max idle setting for user, rules can further override';
COMMENT ON COLUMN public.pg_hba."pool:maxUserActive" IS 'set to override default max active setting for user, rules can further override';

We also want to create a sequence to help space row numbers and allow us to reorganize them easily:

create sequence if not exists pg_hba_seq increment by 5 start with 10;

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 public.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:

insert into public.pg_hba values (nextval('pg_hba_seq'), true, 'host', null, null, null, array ['postgres'], 'newpassword', null, '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: