Percona Server is bundled with the PAM plugin which opens a plethora of ways to authenticate to MySQL such as restricting time when users can connect to MySQL, authenticate via a USB key, authenticate to an external authentication system such as LDAP and many, many more PAM compatible mechanisms.
If you want to use PAM authentication on the community version of MySQL, you may follow the instructions here to get it working on your system. If you want to test PAM authentication, the simplest way is to authenticate via /etc/shadow. The steps do so can be found in here or you can follow the steps below.
Here’s a primer for setting up Percona PAM on CentOS 6 to authenticate via /etc/shadow:
1. Install Percona yum repository
|
1 2 3 4 |
# rpm -Uvh https://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm Retrieving https://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm Preparing... ########################################### [100%] 1:percona-release ########################################### [100%] |
2. Install Percona Server 5.5
|
1 2 3 4 5 6 7 8 9 10 11 12 |
# yum install Percona-Server-server-55 Percona-Server-client-55 … snipped for brevity … Installed: Percona-Server-client-55.x86_64 0:5.5.32-rel31.0.549.rhel6 Percona-Server-server-55.x86_64 0:5.5.32-rel31.0.549.rhel6 Dependency Installed: Percona-Server-shared-55.x86_64 0:5.5.32-rel31.0.549.rhel6 perl.x86_64 4:5.10.1-131.el6_4 perl-Module-Pluggable.x86_64 1:3.90-131.el6_4 perl-Pod-Escapes.x86_64 1:1.04-131.el6_4 perl-Pod-Simple.x86_64 1:3.13-131.el6_4 perl-libs.x86_64 4:5.10.1-131.el6_4 perl-version.x86_64 3:0.77-131.el6_4 Complete! |
3. Start Percona Server 5.5
|
1 2 |
# service mysql start Starting MySQL (Percona Server)...... SUCCESS! |
4. From the mysql console, enable auth_pam and auth_pam_compat plugins. These PAM plugins will be discussed in detail later.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> INSTALL PLUGIN auth_pam SONAME 'auth_pam.so'; Query OK, 0 rows affected (0.00 sec) mysql> INSTALL PLUGIN auth_pam_compat SONAME 'auth_pam_compat.so'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW PLUGINS; +--------------------------------+----------+--------------------+--------------------+---------+ | Name | Status | Type | Library | License | +--------------------------------+----------+--------------------+--------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | … snipped for brevity … | auth_pam | ACTIVE | AUTHENTICATION | auth_pam.so | GPL | | auth_pam_compat | ACTIVE | AUTHENTICATION | auth_pam_compat.so | GPL | +--------------------------------+----------+--------------------+--------------------+---------+ 42 rows in set (0.01 sec) |
5. From the MySQL console, create two users that will authenticate using auth_pam and auth_pam_compat respectively. You also need to delete anonymous users:
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> CREATE USER ap_user IDENTIFIED WITH auth_pam; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER apc_user IDENTIFIED WITH auth_pam_compat; Query OK, 0 rows affected (0.00 sec) mysql> DELETE FROM mysql.user WHERE USER=''; Query OK, 2 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) |
6. Configure Percona Server to authenticate via /etc/shadow by creating a PAM config file in /etc/pam.d/mysqld with the following content:
|
1 2 3 |
auth required pam_warn.so auth required pam_unix.so audit account required pam_unix.so audit |
7. Ensure Percona Server can read /etc/shadow by changing the group ownership and permissions of it
|
1 2 |
#chgrp mysql /etc/shadow #chmod g+r /etc/shadow |
8. Create system users and respective passwords. The usernames should match the users created from the MySQL console
|
1 2 3 4 |
#useradd ap_user #passwd ap_user #useradd apc_user #passwd apc_user |
9. Test if you can connect to Percona Server using the Unix passwords of ap_user and apc_user:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
# mysql -u ap_user -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 9 Server version: 5.5.32-31.0 Percona Server (GPL), Release rel31.0, Revision 549 Copyright (c) 2009-2013 Percona Ireland Ltd. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> SELECT USER(), CURRENT_USER(); +-------------------+----------------+ | USER() | CURRENT_USER() | +-------------------+----------------+ | ap_user@localhost | ap_user@% | +-- |