alpacaaa/README.markdown

## README.markdown

      
    Raw
  

              README.markdown
            
          
    Authenticating users against mysql

I couldn't find any relevant information for getting this to work on ubuntu precise, so I'm writing my founds here.

It's actually pretty simple, this approach doesn't involve PAM but just NSS. The implications are clearly explained in the FAQ of the nss-mysql module:

NSS stands for NameService Switch.  NSS allows you to implement access to
various data using any number of modules.  This means that when the
operating system wants to look up the user "cinergi", it doesn't have
to know how - it calls upon the NSS system to perform the task.  In turn,
we can now configure NSS to look for users in traditional places like
/etc/passwd, NIS, LDAP, and now (using this module), MySQL.  The NSS
API is the backend for traditional UNIX user lookup routines like
'getpwnam' - providing details such as username, uid, gid, gecos, shell,
homedirectory, password, etc.  It does NOT provide for changing user
details.  This is where PAM comes in handy.
PAM stands for Pluggable Authentication Modules.  Like the name suggests,
PAM allows you to implement authentication (and data manipulation) using
any number of modules.  Note that this differs from NSS in that it ONLY
provides authentication.  It does not allow you to do such things as
"finger username", or create files owned by "username".  Unlike NSS,
however, it can enable users to change their passwords using traditional
methods like the 'passwd' command.
The libnss_mysql library, like the name suggests, provides an NSS-based
solution.  Whether you also need PAM depends upon whether you need
to enable users to change their password using traditional methods (you
could always script a passwd-like utility that performs MySQL commands).

So any change to users must be done against the database as regular shell commands won't work. Fair enough.

Regular users (stored in /etc/passwd) will still work (having root inside the db looks like a bad idea), so the system will first look up for a regular user as always and eventually look it up in mysql if not found.
Installing packages

There's only one package needed.
sudo apt-get install libnss-mysql-bg

Choosing passwords

You need to choose two passwords that will be assigned to the two mysql users running queries (find user, find group...).

From here on, I'll refer to $user-pass and $root-pass inside configuration files. Change it appropriately with the two passwords choosen.
Editing files


Edit /etc/libnss-mysql.cfg replacing its content with this:
 getpwnam    SELECT username,'x',uid,gid,gecos,homedir,shell \
         FROM users \
         WHERE username='%1$s' \
         LIMIT 1
 getpwuid    SELECT username,'x',uid,gid,gecos,homedir,shell \
             FROM users \
             WHERE uid='%1$u' \
             LIMIT 1
 getspnam    SELECT username,password,lstchg,min,max,warn,inact,expire,flag \
             FROM users \
             WHERE username='%1$s' \
             LIMIT 1
 getpwent    SELECT username,'x',uid,gid,gecos,homedir,shell \
             FROM users
 getspent    SELECT username,password,lstchg,min,max,warn,inact,expire,flag \
             FROM users
 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    auth
 username    nss-user
 password    $user-pass
 #socket      /var/run/mysqld/mysqld.sock
 #port        3306


Edit /etc/libnss-mysql-root.cfg replacing its content with this:
 username    nss-root
 password    $root-pass


Edit /etc/nsswitch.conf replacing the following lines:
 passwd:         compat
 group:          compat
 shadow:         compat

with these:
 passwd:         compat mysql
 group:          compat mysql
 shadow:         compat mysql


Importing database

Run the following queries against your mysql database:
create database auth;
use auth;

# The tables ...
CREATE TABLE groups (
  name varchar(16) NOT NULL default '',
  password varchar(34) NOT NULL default 'x',
  gid int(11) NOT NULL auto_increment,
  PRIMARY KEY  (gid)
) AUTO_INCREMENT=5000;

CREATE TABLE grouplist (
  rowid int(11) NOT NULL auto_increment,
  gid int(11) NOT NULL default '0',
  username char(16) NOT NULL default '',
  PRIMARY KEY  (rowid)
);

CREATE TABLE users (
  username varchar(16) NOT NULL default '',
  uid int(11) NOT NULL auto_increment,
  gid int(11) NOT NULL default '5000',
  gecos varchar(128) NOT NULL default '',
  homedir varchar(255) NOT NULL default '',
  shell varchar(64) NOT NULL default '/bin/bash',
  password varchar(34) NOT NULL default 'x',
  lstchg bigint(20) NOT NULL default '1',
  min bigint(20) NOT NULL default '0',
  max bigint(20) NOT NULL default '99999',
  warn bigint(20) NOT NULL default '0',
  inact bigint(20) NOT NULL default '0',
  expire bigint(20) NOT NULL default '-1',
  flag bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (uid),
  UNIQUE KEY username (username),
  KEY uid (uid)
) AUTO_INCREMENT=5000;


# The permissions ...
GRANT USAGE ON *.* TO `nss-root`@`localhost` IDENTIFIED BY '$root-pass';
GRANT USAGE ON *.* TO `nss-user`@`localhost` IDENTIFIED BY '$user-pass';

GRANT Select (`username`, `uid`, `gid`, `gecos`, `homedir`, `shell`, `password`,
              `lstchg`, `min`, `max`, `warn`, `inact`, `expire`, `flag`)
             ON `auth`.`users`
             TO 'nss-root'@'localhost';
GRANT Select (`name`, `password`, `gid`)
             ON `auth`.`groups`
             TO 'nss-root'@'localhost';

GRANT Select (`username`, `uid`, `gid`, `gecos`, `homedir`, `shell`)
             ON `auth`.`users`
             TO 'nss-user'@'localhost';
GRANT Select (`name`, `password`, `gid`)
             ON `auth`.`groups`
             TO 'nss-user'@'localhost';

GRANT Select (`username`, `gid`)
             ON `auth`.`grouplist`
             TO 'nss-user'@'localhost';
GRANT Select (`username`, `gid`)
             ON `auth`.`grouplist`
             TO 'nss-root'@'localhost';

Remember to replace $user-pass and $root-pass.
This will create the database used for authentication. Now, to create a user, just use something like the following:
INSERT INTO users (username,gecos,homedir,password)
    VALUES ('cinergi', 'Ben Goodwin', '/home/cinergi', ENCRYPT('cinergi'));
INSERT INTO groups (name)
    VALUES ('foobaz');
INSERT INTO grouplist (gid,username)
    VALUES (5000,'cinergi');

The end

That should be all there is to it. I haven't tested this deeply, but it seems to work fine. Any improvement is more than welcome.