Auth Modules


Introduction

SQL Relay provides three built-in auth mechanisms, configurable by the authtier attribute of the instance tag.

( NOTE: Prior to version 0.65.0, authtier="database" defaulted to the behavior of authtier="proxied" fell back to the current behavior if the proxied behavior was unsupported. There was no way to force authtier="database" behavior. As of 0.65.0 the funcationality is split into two separate options.)

Auth modules allow the SQL Relay server programs to authenticate/authorize users in a manner other than the provided by the built-in mechanisms.

The auths section of the sqlrelay.conf file indicates which auth modules to load and what parameters to use when executing them.

<?xml version="1.0"?>
<instances>
        ...
        <instance id="example" ... >
                ...
                <auths>
                        <auth module="userlist">
                                <user user="user1" password="password1"/>
                                <user user="user2" password="password2"/>
                                <user user="user3" password="password3"/>
                        <auth>
                </auths>
                ...
        </instance>
        ...
</instances>

In this example, the module attribute specifies which module to load and the user tags specify the list of valid users and passwords.

Module configurations may have attributes and/or nested tags. How these elements are interpreted is module-specific.

Auth modules can be "stacked". Multiple modules may be loaded and multiple instances of the same type of module, with different configurations, may also be loaded.

<?xml version="1.0"?>
<instances>
        ...
        <instance id="example" ... >
                ...
                <auths>
                        <auth module="userlist">
                                <user user="user1" password="password1"/>
                                <user user="user2" password="password2"/>
                                <user user="user3" password="password3"/>
                        </auth>
                        <auth module="sqlrelay" socket="/tmp/auth.socket" user="auth" password="auth"/>
                        <auth module="userlist">
                                <user user="user4" password="password4"/>
                                <user user="user5" password="password5"/>
                        </auth>
                </auths>
                ...
        </instance>
        ...
</instances>

At startup, the SQL Relay server creates instances of the specified auth modules and initializes them. When a client connects, the server passes the supplied credentials to each module, in the order that they were specified in the config file. If a module successfully authenticates/authorizes a user, then the client is allowed to continue its session. If a module fails to authenticate/authorize a user, then the credentials are passed on to the next module. If all modules fail to authenticate/authorize a user, then the client is not allowed to continue its session.

Available Modules

Currently, the following auth modules are available:

userlist

The userlist module authenticates/authorizes a user against a static list of valid user/password combinations. In fact, it has the same behavior as the default behavior of SQL Relay. The only difference is that the syntax in the config file is a little different.

When using the userlist auth module, rather than using the users tag to specify valid users...

<?xml version="1.0"?>
<instances>
        ...
        <instance id="example" ... >
                ...
                <users>
                        <user user="user1" password="password1"/>
                        <user user="user2" password="password2"/>
                        <user user="user2" password="password2"/>
                </users>
                ...
        </instance>
        ...
</instances>

...you would list the users inside of the tag for the module:

<?xml version="1.0"?>
<instances>
        ...
        <instance id="example" ... >
                ...
                <auths>
                        <auth module="userlist">
                                <user user="user1" password="password1"/>
                                <user user="user2" password="password2"/>
                                <user user="user3" password="password3"/>
                        <auth>
                </auths>
                ...
        </instance>
        ...
</instances>

Password encryption modules may also be used.

<?xml version="1.0"?>
<instances>
        ...
        <instance id="example" ... >
                ...
                <passwordencryptions>
                        <passwordencryption module="rot" id="rot13" count="13"/>
                        <passwordencryption module="rot" id="rot16" count="16"/>
                </passwordencryptions>
                ...
                <auths>
                        <auth module="userlist">
                                <user user="user1" password="cnffjbeq4" passwordencryptionid="rot13"/>
                                <user user="user2" password="cnffjbeq5" passwordencryptionid="rot13"/>
                                <user user="user3" password="fqiimeht8" passwordencryptionid="rot16"/>
                        <auth>
                </auths>
                ...
        </instance>
        ...
</instances>

The userlist module can also authorize Kerberos-authenticated or Active Directory-authenticated users, as well as users authenticated by a TLS/SSL certificate. The module automatically detects which athentication method to use, based on the credentials supplied by the client.

When configuring the module to authenticate a Kerberos/Active-Directory user, include the list of valid principals, and omit the password from each entry.

<?xml version="1.0"?>
<instances>
        ...
        <instance id="example" ... >
                ...
                <auths>
                        <auth module="userlist">
                                <user user="dmuse@AD.FIRSTWORKS.COM"/>
                                <user user="kmuse@AD.FIRSTWORKS.COM"/>
                                <user user="imuse@AD.FIRSTWORKS.COM"/>
                                <user user="smuse@AD.FIRSTWORKS.COM"/>
                                <user user="FIRSTWORKS.COM\dmuse"/>
                                <user user="FIRSTWORKS.COM\kmuse"/>
                                <user user="FIRSTWORKS.COM\imuse"/>
                                <user user="FIRSTWORKS.COM\smuse"/>
                        <auth>
                </auths>
                ...
        </instance>
        ...
</instances>

See the SQL Relay Configuration Guide for more information about configuring Kerberos and Active Directory encryption and authentication.

When configuring the module to authenticate a TLS/SSL user, include the list of valid client names, and omit the password from each entry.

<?xml version="1.0"?>
<instances>
        ...
        <instance id="example" ... >
                ...
                <auths>
                        <auth module="userlist">
                                <user user="sqlrclient1.firstworks.com"/>
                                <user user="sqlrclient2.firstworks.com"/>
                                <user user="sqlrclient3.firstworks.com"/>
                                <user user="sqlrclient4.firstworks.com"/>
                        <auth>
                </auths>
                ...
        </instance>
        ...
</instances>

If the client's certificate contains Subject Alternative Names then each SAN will be compared to each entry in the list. If a match is found then the client is authorized.

If the client's certificate does not contain Subject Alternative Names, then the Common Name is compared to each entry in the list. If a match is found then the client is authorized.

Note that this module only works if:

See the SQL Relay Configuration Guide for more information about configuring TLS/SSL encryption and authentication.

(Note, in previous versions of SQL Relay, there were separate krb_userlist and tls_userlist modules. As of version 0.66.0, these have been merged with the userlist module.)

Since the userlist auth module appears to provide the exact same functionality as the built-in users tag, why would you want to use it instead of just using the built-in users tag?

The users tag is evaluated prior to evaluating any modules. So, you must use the userlist module if you are stacking auth modules, and want to use a different auth module first, falling back to a static list of users if it fails.

database

The database module authenticates/authorizes a user against the database itself. SQL Relay does this by checking the provided credentials against the credentials that are currently in use. If they differ, then it logs out of the database and logs back in using the provided credentials.

NOTE: Database auth should not be used in an instance where dbase="router". It's OK for the instances that the router routes to to use it but the router instance itself should not. If database auth is used for that instance, then auth will fail.
NOTE: Prior to version 0.65.0, the "database" auth module defaulted to the behavior of the "proxied" auth module and fell back to this behavior if the proxied behavior was unsupported. There was no way to force this behavior. As of 0.65.0 the funcationality is split into two separate modules.

Using the database module has the same behavior as setting authtier="database" in the config file. The only difference is that the syntax in the config file is different.

When using the database auth module, rather than using authtier="database"...

<?xml version="1.0"?>
<instances>
        ...
        <instance id="example" ... authtier="database" ... >
                ...
        </instance>
        ...
</instances>

...you would use the module instead:

<?xml version="1.0"?>
<instances>
        ...
        <instance id="example" ... >
                ...
                <auths>
                        <auth module="database"/>
                </auths>
                ...
        </instance>
        ...
</instances>

Since the userlist auth module appears to provide the exact same functionality as the built-in authtier attribute, why would you want to use it instead of just using the built-in authtier attribute?

There is no specific advantage to using the module instead of the attribute, other than consistency. If you're using other auth modules in other instances, then you might also want to use the database auth module in this instance.

Note also, that the database auth module should not be used in a stacked configuration. Or, at least, using it in a stacked configuration could lead to unexpected results. When using database auth, each connection is left logged in as the most recently authenticated/authorized user. If an SQL Relay user is authenticated/authorized by some other method, then there is no guarantee what user it will access the database as.

proxied

The proxied module authenticates/authorizes a user against the database itself. SQL Relay logs into the database as a user with permissions to proxy other users. For each client session, SQL Relay checks the provided credentials against the credentials that are currently in use. If they differ, then it asks the proxy user to switch the user it's proxying to the provided user.

This is currently only supported with Oracle 8i or higher and requires database configuration. See this document for more information including instructions for configuring Oracle.

NOTE: Proxied auth should not be used in an instance where dbase="router". It's OK for the instances that the router routes to to use it but the router instance itself should not. If proxied auth is used for that instance, then auth will fail.
NOTE: Prior to version 0.65.0, the "database" auth module defaulted to the behavior of the "proxied" auth module and fell back to the current behavior if the proxied behavior was unsupported. There was no way to force the database auth module's behavior. As of 0.65.0 the funcationality is split into two separate modules.

Using the proxied module has the same behavior as setting authtier="proxied" in the config file. The only difference is that the syntax in the config file is different.

When using the proxied auth module, rather than using authtier="proxied"...

<?xml version="1.0"?>
<instances>
        ...
        <instance id="example" ... authtier="proxied" ... >
                ...
        </instance>
        ...
</instances>

...you would use the module instead:

<?xml version="1.0"?>
<instances>
        ...
        <instance id="example" ... >
                ...
                <auths>
                        <auth module="proxied"/>
                </auths>
                ...
        </instance>
        ...
</instances>

Since the userlist auth module appears to provide the exact same functionality as the built-in authtier attribute, why would you want to use it instead of just using the built-in authtier attribute?

There is no specific advantage to using the module instead of the attribute, other than consistency. If you're using other auth modules in other instances, then you might also want to use the proxied auth module in this instance.

Note also, that the proxied auth module should not be used in a stacked configuration. Or, at least, using it in a stacked configuration could lead to unexpected results. When using proxied auth, each connection is left logged in as the most recently authenticated/authorized user. If an SQL Relay user is authenticated/authorized by some other method, then there is no guarantee what user it will access the database as.

sqlrelay

The sqlrelay module authenticates/authorizes a user against a table in a database, using another instance of SQL Relay to access that database.

The module's host, port, socket, user and password paramters define which instance of SQL Relay to talk to. The table, usercolumn and passwordcolumn parameters define which table and which columns within the table to authenticate/authorize against. If passwords aren't stored in plain text, then an optional passwordfunction parameter may specify the stored procedure used to encrypt the password.

<?xml version="1.0"?>
<instances>
        ...
        <instance id="example" ... >
                ...
                <auths>
                        <auth module="sqlrelay" socket="/tmp/auth.socket"
                                user="auth" password="auth"
                                table="user" usercolumn="User" passwordcolumn="Password"
                                passwordfunction="password"/>
                </auths>
                ...
        </instance>
        ...
</instances>

Custom Modules

Custom modules may also be developed.

For more information, please contact David Muse at david.muse@firstworks.com.