使用MYSQL作为Linux认证

背景

系统认证体系虽然LDAP/Active Directory在企业中更为常见，但MySQL作为最为流行的关系型数据库，认证方案因其简单性和与现有Web基础设施的良好集成，仍在特定场景中被广泛使用。

本文作为一种集中化用户管理的解决储备方案。

环境

• OS: Kylin V10
• 软件: pam_mysql

实现步骤

关联数据库

1. 创建数据库

mysql> CREATE DATABASE IF NOT EXISTS pam_mysql CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. 创建用户数据表

mysql> CREATE TABLE pam_mysql.users(name varchar(32) NOT NULL,
                    username varchar(50) NOT NULL,
                    password char(160) NOT NULL,
                    email varchar(50) NULL,
                    active tinyint NOT NULL DEFAULT 1,
                    created_at TIMESTAMP NULL,
                    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
                    remark text,
                    primary key(username)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. 创建日志表[可选]

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+08:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;


DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
  `time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `user` varchar(50) NOT NULL,
  `pid` mediumint(9) NOT NULL,
  `host` varchar(100) NOT NULL,
  `rhost` tinytext NOT NULL,
  `message` tinytext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `log`
  ADD KEY `time` (`time`);
COMMIT;

3. 插入用户范例数据

mysql> INSERT INTO pam_mysql.users (name, username, password, email, created_at)
VALUES 
  ('张三', 'zhang3', password('zhang3@8848'), 'zhang3@vqiu.cn', CURRENT_TIMESTAMP),
  ('李四', 'li4', password('li4@8848'), 'li4@vqiu.cn', CURRENT_TIMESTAMP),
  ('王五', 'wang5', password('wang5@8848'), 'wang5@vqiu.cn', CURRENT_TIMESTAMP);

4. 查询数据

mysql> select name,username,password from users;
+--------+----------+-------------------------------------------+
| name   | username | password                                  |
+--------+----------+-------------------------------------------+
| 李四   | li4      | *7CF17ED0A16E0300F17F56B50F34C93DC9783083 |
| 王五   | wang5    | *30E0A84AEFBCB1B75D0334C9E16491B9F271E858 |
| 张三   | zhang3   | *BE54221EA2F2B74AE7321BB75F9F5515A17A8952 |
+--------+----------+-------------------------------------------+
3 rows in set (0.00 sec)

5. 创建专属用户并分配权限

-- 创建专有用户
CREATE USER 'pam_conn'@'%' IDENTIFIED BY '^password$';

-- 赋予select权限
GRANT SELECT, INSERT, UPDATE ON pam_mysql.* TO 'pam_conn'@'%';

安装配置软件包

1. 安装 pam_mysql 软件

 # yum localinstall http://repo.iotti.biz/CentOS/8/x86_64/pam_mysql-1.0.0~beta1-4.el8.lux.x86_64.rpm

2. 创建系统pam配置文件

export $DB_USER
export $DB_PASS
export $DB_HOST

# cat > /etc/pam.d/pam_mysql << EOF
auth sufficient pam_mysql.so user=$DB_USER passwd=$DB_PASS host=$DB_HOST db=pam_mysql table=users usercolumn=username passwdcolumn=password [where=user.active=1] sqllog=0 crypt=2
account required pam_mysql.so user=$DB_USER passwd=$DB_PASS host=$DB_HOST db=pam_mysql table=users usercolumn=username passwdcolumn=password [where=user.active=1] sqllog=0 crypt=2
EOF

若需要日志功能

export $DB_USER
export $DB_PASS
export $DB_HOST

# cat > /etc/pam.d/pam_mysql << EOF
auth sufficient pam_mysql.so user=$DB_USER passwd=$DB_PASS host=$DB_HOST db=pam_mysql table=users usercolumn=username passwdcolumn=password [where=user.active=1] sqllog=0 crypt=2 sqllog=true logtable=log logmsgcolumn=message logusercolumn=user logpidcolumn=pid loghostcolumn=host logrhostcolumn=rhost logtimecolumn=time
account required pam_mysql.so user=$DB_USER passwd=$DB_PASS host=$DB_HOST db=pam_mysql table=users usercolumn=username passwdcolumn=password [where=user.active=1] sqllog=0 crypt=2 sqllog=true logtable=log logmsgcolumn=message logusercolumn=user logpidcolumn=pid loghostcolumn=host logrhostcolumn=rhost logtimecolumn=time
EOF

测试测试

1. 安装cyrus-sasl

# yum -y install cyrus-sasl

2. 启动服务

 systemctl enable saslauthd --now

3. 用户验证

# testsaslauthd -u zhang3 -p zhang3@8848 -s pam_mysql
0: OK "Success."
# testsaslauthd -u li4 -p li4@8848 -s pam_mysql
0: OK "Success."
# testsaslauthd -u wang5 -p wang5@8848 -s pam_mysql
0: OK "Success."

当提示"authentication failed"时可以使用以下方式来排查：

# journalctl -u saslauthd

参考引用

• [1] https://github.com/NigelCunningham/pam-MySQL
• [2] https://wiki.eryajf.net/pages/3809.html