New Feature for PAM Plugin in MariaDB

A Pluggable Authentication Module (PAM) is a framework that allows any software that relies on authentication to behave independently of the underlying authentication scheme. It is not supported by Windows, but can be used on most other Operating Systems (Linux, AIX, FreeBSD, Solaris, Max OS X, etc). MariaDB PAM plugin has been introduced in version 5.2.10 and enables MariaDB to use a standard access to the server for different types of authentication (standard password, LDAP, ssh passphrases, etc) but also combinations of different authentication modules. The PAM plugin is a simple and easy way to allow users defined outside the MariaDB user table to connect the database in a proper way without affecting the MariaDB grant tables. It tells MariaDB to delegate to the PAM the authentication, according to its configuration. The plugin receives user name, host name, password and authentication string from MariaDB server and checks the credentials against the PAM returning an access success or fail message. The typical use case of the PAM is to allow external authentication (MariaDB accepts connections from users that are not defined in the grant tables) or proxy user (user name mapping). In this last case the connecting user will be treated as a different user mainly for privileges management. MariaDB is increasingly becoming part of big organisations’ infrastructures. System architects are really sensitive to enforce the corporate complexity rules and to endorse the password policy management in their companies.

Typical use case example

If we consider a typical situation where LDAP is configured on each server that is deployed, it’s really a simple process to place a PAM config file and then load the plugin inside MariaDB. Here an example:

(..)
auth sufficient pam_unix.so nullok try_first_pass
auth requisite pam_succeed_if.so uid >= 500 quiet
auth sufficient pam_ldap.so use_first_pass
auth required pam_deny.so

account required pam_unix.so broken_shadow
account sufficient pam_localuser.so
account sufficient pam_succeed_if.so uid 

This simple procedure brings simplicity (one password for every system: change it once and it changes everywhere) and allows automation of user access.

Unfortunately there are some limitations for MariaDB and MySQL:  data structure inside both MySQL and MariaDB (up to MySQL 5-5) is limited to 16 UTF-8 characters (48 bytes buffer) and the same limit applies to the PAM plugin. Many, if not all, of organisations that need to integrate access across  different authentication systems (such as OLAP) are likely to find the 16 character maximum too limiting.

To solve this limitation, starting from MariaDB 5.5.31, MariaDB supports 80 UTF-8 character for the  and MariaDB PAM plugin has been modified to support the same number of characters as well.

However, in order to enable this feature, the following schema changes must be made:

alter table mysql.user modify User char(80) binary not null default ''; alter table mysql.db modify User char(80) binary not null default ''; alter table mysql.tables_priv modify User char(80) binary not null default ''; alter table mysql.columns_priv modify User char(80) binary not null default ''; alter table mysql.procs_priv modify User char(80) binary not null default ''; alter table mysql.proc modify definer char(141) collate utf8_bin not null default ''; alter table mysql.event modify definer char(141) collate utf8_bin not null default ''; alter table mysql.proxies_priv modify User char(80) COLLATE utf8_bin not null default ''; alter table mysql.proxies_priv modify Proxied_user char(80) COLLATE utf8_bin not null default ''; alter table mysql.proxies_priv modify Grantor char(141) COLLATE utf8_bin not null default ''; alter table mysql.servers modify Username char(80) not null default ''; alter table mysql.procs_priv modify Grantor char(141) COLLATE utf8_bin not null default ''; alter table mysql.tables_priv modify Grantor char(141) COLLATE utf8_bin not null default ''; flush privileges;