Getting PAM to authenticate against MySQL(sshd一个可以把用户名密码存在mysql数据库的方法）

Because OpenSSH makes use of PAM for authentication, it may be desirable to back users with a pre-existing MySQL database. The following steps will show how to do this. This is yet another authentication option for integrations.

The role of PAM and NSS

There is some confusion as to the role of PAM and NSS. In a nutshell, PAM and NSS perform two related functions:

• PAM: Authentication of users
• NSS: Name Services

The important item to note is that authentication is a question of, "Is this who they purport to be?" while name services focus on answering, "What do we know this about this entity?" The confusion comes about because most authentication databases capture both sets of information in one place. To get MySQL to work correctly, both PAM and NSS will need to be configured in slightly different ways.

Pre-requisites

This document does not cover MySQL installation or configuration, and will assume that the DB is local to the systemconfiguring PAM/NSS. The exact table layout is variable and is not constrained by the PAM/NSS modules, and any pre-existing database can be used. This example will assume a single table is used, defined a little later, for all queries, but a join of multiple tables could be used.

Also, these examples will show the use of aspshellfor the user shell. This assumes P2P or Enterprise Server are installed.

To simplify setup, the following table is used in our example:

create database user_db;

use user_db;
create table user (
user_id int(6) not null auto_increment,
login_name varchar(16) not null,
 password varchar(16) not null,
primary key (user_id),
key user_id (user_id) );

insert
into user (login_name,password)
values ('admin', encrypt('test'));
insert
into user (login_name,password)
values ('luke', encrypt('woof'));

• login_name: the username
• password: is the password
• user_id: is an integer value that is unique to the user

Installing the pertinent Files

The following steps detail the use of yum tools becuase these steps were done on Fedora Code 11. Please make adjustments for systems that are not RPM based, or do not make use ot the toolset.

To install pam_mysql and libnss-mysql:

# yum install pam_mysql # yum install libnss-mysql Configuring NSS

Because improper configuration of PAM can result in denied access to the host system, it is best to start with setting up NSS. The main configuration of NSS is done in two files in/etc. They are /etc/libnss-mysql.cfg and/etc/libnss-mysql-root.cfg. The file defines mappings for mysql queries to NSS functions; the file is pretty self explanatory since all it does is define how to access the database.

Here is an example :

getpwnam SELECT login_name,'x',user_id+'10000',user_id+'10000','Joe User',CONCAT('/home/',login_name),'/bin/aspshell' \
FROM user \
WHERE login_name='%1$s' \
LIMIT 1
getpwuid SELECT login_name,'x',user_id+'10000',user_id+'10000','Joe User',CONCAT('/home/',login_name),'/bin/aspshell' \
FROM user \
WHERE user_id='%1$u'-10000 \
LIMIT 1
getspnam SELECT login_name,password,'','','','','','','' \
FROM user \
WHERE login_name='%1$s' \
LIMIT 1
getpwent SELECT login_name,'x',user_id+'10000',user_id+'10000','Joe User',CONCAT('/home/',login_name),'/bin/aspshell' \
FROM user
getspent SELECT login_name,password,'','','','','','','' \
FROM user
getgrnam SELECT name,password,gid \
FROM groups \
WHERE name='%1$s' \
LIMIT 1
getgrgid SELECT name,password,gid \
FROM groups \
 WHERE gid='%1$u' \
LIMIT 1
getgrent SELECT name,password,gid \
FROM groups
memsbygid SELECT username \
FROM grouplist \
WHERE gid='%1$u'
gidsbymem SELECT gid \
FROM grouplist \
WHERE username='%1$s'

host localhost
database user_db
username root
password change-me-please
socket /opt/aspera/common/mysql/var/run/mysql.sock
port 4406

At the lowest level, the NSS get* set of functions return structs of database records. The SQL statements help define how the records are returned to NSS. In this example, the following is done per column:

1. The "username" as taken from "login_name"
2. The "x" is used to denote passage to /etc/shadowfrom legacy /etc/passwd
3. The UID is set to the "user_id" + 10,000
4. The GID is set to the "user_id" + 10,000. This can be from a join, or set statically
5. Statically set to 'Joe User', but it could be a concatenation of the first name and last name columns if they existed (for example)
6. A concat of the string '/home/' and the "login_name" will set the users home directory
7. We statically set /bin/aspshell to the users execution environment to sandbox the user to only Aspera transfers

Note that getpwuid is a reverse mapping for username to UID.

Also note, none of the group related functions were defined. They do not need to be defined, as will be evident when/etc/nsswitch.conf is configured. Since the users are not going into a complex group scheme, at least in these examples, there is no need to defer to the DB for this information.

Altering is not sufficient to getting NSS to work. NSS needs to know where to look for certain types of information. To do this, NSS looks to for guidance. In particular, the string "mysql" is added to the passwd and shadow section of the file, but not group:

passwd: files mysql
shadow: files mysql
group: files

Since we did not alter group, the group NSS functions will not be used.

The unix id tool can now be used to test the user account:

# grep admin /etc/passwd # id admin uid=10001(some_user) gid=10001 groups=10001

It is now time to configure PAM.

Setting up PAM

While PAM con be configured from one file, it is easier for upkeep if multiple files are used. Because Fedora uses a seperate sshd file to isolate PAM by service, the two files that will be setup are /etc/pam_mysql.conf and/etc/pam.d/sshd.

Here is the content of :

users.host=/opt/aspera/common/mysql/var/run/mysql.sock
users.db_user=root
users.db_passwd=change-me-please
users.database=user_db
users.table=user
users.user_column=login_name
users.password_column=password
users.password_crypt=1
verbose=0

The heart of what this file does is configure how connections are made, and also define the mappings for reqiured information from the database columns. For example, "users.password_column=password" tell pam_mysql.so that the column "password" in the table "user" of database "user_db" (defined earlier) is the password column. This should be self evident.

The "users.password_crypt" specifies the type of hash function was used on the user database password entries. "1" is the old-style crypt().

Once this is place, PAM needs to be instructed how to authneticate users. The sshd specific configuration for this is . The output is below, and the details of what is does follows:

#%PAM-1.0

account sufficient pam_unix.so
account required pam_mysql.so config_file=/etc/pam_mysql.conf

auth sufficient pam_unix.so
auth required pam_mysql.so config_file=/etc/pam_mysql.conf
auth required pam_env.so

password include system-auth
session include system-auth

1. The account lines tell PAM to be satisfied is the user is on the Unix system (i.e. ) or otherwise require mysql auth.
2. The auth lines are similar to account, but this is where the actual auth occurs. Theconfig_file=/etc/pam_mysql.conf is how the pam_mysql module is configured.

To test, do the two following:

1. Test that a local user can still log in:
   # ssh root@localhost

   The user should be able to log in without a problem

2. test a user in the DB
   # ssh test1@localhost

   The user should not exist on the system, but a correct password from the database will allow access via aspshell.