Overview
You can design the database to be relatively simple, or extremely
complex. For the most part, the stucture of the database is up to you;
The groups piece is more complex and less flexible, however. Take
a look at the sample configuration. There are MySQL statements to
execute in order to retrieve the
data needed for various system calls. The query names mostly match
the system calls themselves. The system call, getpwnam(),
asks the system to supply various information for the username
provided - uid, gid, gecos, home directory, shell, etc.
getpwuid() does the same thing, except it looks the user
up via uid instead of username. You can use the man pages to see
what each one does. You won't find memsbygid or
gidsbymem, however. I'll cover that in more detail shortly.
Query Requirements - passwd/shadow
Each SQL query must return data in the right order - that's it. You can design
your database pretty much however you want - just make sure you can
create a query that will return the necessary data in the right order. You can
gather from the sample getpwnam query that the data must be
returned as follows (Linux): username, password, uid, gid, gecos, homedir,
shell. You'll note the example has password set to 'x'. This
is to protect user passwords (even though they're returned encrypted)
from non-root users. The shadow database (getspnam, getspent) will
return the user's password. Programs understand that systems with a
shadow database (Linux and Solaris both have it) need to call
getspnam if they need to retrieve a user's password.
You'll also note the WHERE clause contains a '%1$s'. This '%1$s' is where
the system will insert the username being searched for. For
getpwuid, this is a '%1$u' because we're searching based on a
number (%1$u) rather than a username/string (%s). It's important to
keep these correct, else things will start crashing.
Here's an example of a much more complex getpwnam query:
|
SELECT LOWER(services.username), 'x', uid, gid, CONCAT_WS(' ', first_name,
middle_initial, last_name), service_defs.homedir, service_defs.shell FROM
services LEFT JOIN customer USING(cust_num) LEFT JOIN service_defs ON
services.product=service_defs.name WHERE services.username='%1$s' AND expire
> CURDATE() AND suspended='N' AND
services.hostname='server.domain.com' LIMIT 1
|
Query Requirements - groups
The design for groups is a little more limited. Since MySQL is a
relational DB, it's not possible to directly implement a many-to-many
relationship, which "groups" needs. We need to be able to get all
the members of a group by the gid, and we need to get all
gid's a user is a member of. To do this, we need two tables: one
to hold group information (group name, group password, group id),
and the other to hold a list of username-gid mappings. A group
lookup requires two queries - one to fetch the group itself
(getgrnam, getgrgid, getgrent), and another to fetch the group's
members (memsbygid). Traditionally, systems would have to scan the
entire group database to figure out all the groups a user was a
member of. Now, we have the initgroups function. This
directly asks the subsystem for a list of gid's a user is a member
of. This is why we need the 'gidsbymem' SQL query.
Connection Persistence
libnss-mysql maintains a persistent connection for any process
that performs a lookup. This is necessary for performance and
resource reasons. You should take note that if you have 100 concurrent
processes that perform user lookups, you'll have at least 100 concurrent
connections to your MySQL server. Sometimes these processes stick
around for a while, and only perform a lookup at the start (IE sshd) -
so you may ask how to time out these unnecessary lingering connections.
You need to instruct your MySQL server to close idle connections after,
say, 60 seconds (instead of the default 28,800). To do so, edit/create
the file '/etc/my.cnf' and add:
[mysqld]
set-variable=wait_timeout=60
And restart your MySQL server.
Connection Flavors
Connectivity comes in two forms: UNIX sockets or TCP/IP. Sockets
is preferred as it's faster, uses fewer resources, and you're not
likely to run out of available TCP/IP sockets on a heavily (and I
mean heavily) loaded system. You also won't have to worry about
encrypting your MySQL connections. If you have a multiple-server
environment, you could replicate your database (optionally over
an encrypted session) to each client, and point the client at the
local replica. Or point all your clients at one MySQL server.
You could use a tunnel for encryption, or use MySQL 4.x to use
native SSL.
Multiple Servers/Failover
libnss-mysql no longer (as of v0.9) supports multiple servers
or failover. The code was cumbersome and buggy - I'd just as
soon let a load balancer (whether it's a piece of hardware or a
specially-configured linux box) do the job.
Overview
There are two libnss-mysql configuration files: /etc/libnss-mysql.cfg and
/etc/libnss-mysql-root.cfg. These are read once for each process
that makes a lookup for a user in MySQL. If you have a daemon that
looks up usernames (IE radius), it will not see changes to this file
until you restart it. This is especially important to note if you're
running NSCD. Please see the NSCD section below
The Files
Both files are necessary
if you want to properly implement shadow support. The main file
(/etc/libnss-mysql.cfg) generally specifies all the queries and
non-root MySQL server information.
The root file (/etc/libnss-mysql-root.cfg) can contain any subset of
the above file, although generally it only contains the username and
password to connect with for 'root' access.
File Format
Comments begin with a "#" at the beginning of the line. Lines may
be continued by using a "\" as the last character of the line.
Each line contains a key/value pair like so:
key = value
The key is always a single word. The value may contain anything, and
should not be quoted. Whitespace between key/= and =/value is allowed
as spaces or tabs - as many as you need.
Lines may be continued, but it's strictly
required that the "\" character is the very last character in the line
to be continued. Whitespace after the "\" will break it.
Here's an example:
key = some really long \
value goes here
Main vs. Root
The root config is read after the main config and
overrides any settings in the main config for operations that are done as
root. This is used to connect to your MySQL server as an "unpriviledged"
user when the program requesting the user information isn't running as
root, and connect to your MySQL server as a "priviledged" user when the
program requesting the user information is running as root. This
allows you to set up different MySQL access levels - one that can read a
user's password (the "priviledged" user), and one that cannot (the
"unpriviledged" user). Since the main config is readable by anyone,
you'll want to make sure that the "unpriviledged" user doesn't have access
to any information that it doesn't absolutely need. Remember, any user
can see the MySQL username/password to connect to your database with as an
"unprivilidged" user. Along the same lines, it's important to make sure
your root config is owned by root and mode 0600 so only root can read the
"priviledged" connection information.
Queries
This section defines the SQL queries to execute in order to implement
various lookups. Some of the queries take an argument (such as a
username or userid) in the form of symbols such as '%1$s' and '%1$u'.
You may use these symbols as many times as you like.
There are 10 queries:
| Name | Purpose |
|---|
| getpwnam | Fetch 'password' record for a specific username |
| getpwuid | Fetch 'password' record for a specific uid |
| getspnam | Fetch 'shadow' record for a specific username |
| getpwent | Fetch all 'password' records on the system |
| getspent | Fetch all 'shadow' records on the system |
| getgrnam | Fetch 'group' record for a particular group |
| getgrgid | Fetch 'group' record for a particular gid |
| getgrent | Fetch all 'group' records on the system |
| gidsbymem | Fetch a list of gids that a specific username is a member of |
| memsbygid | Fetch a list of usernames that a gid has |
MySQL Server Setup
Settings in the 'root' configuration will
take precendence when the current process is running as 'root'. This is
used to give the process access to priviledged data such as the user's
password. Generally speaking the 'root' config will only contain the
alternate username and password to connect with.
Note that settings in your /etc/my.cnf's [client] section will
influence libnss-mysql! This is why many of the settings below are
optional. I don't recommend putting the username/password in /etc/my.cnf,
however.
Options in red are required. All others are optional
| Name | Values | Default | Purpose |
|---|
| host | String | | MySQL server name/IP |
| database | String | | MySQL database name |
| username | String | | MySQL user to connect as |
| password | String | | MySQL password to connect with |
| socket | Path | client default | Path to MySQL socket (if host is localhost) |
| port | Integer | client default | TCP/IP port of MySQL server (fi host is not localhost) |
| timeout | Integer | 3 | MySQL connect timeout in seconds |
| compress | 0|1 | 0 | If you want to use MySQL compressed protocol, set to 1, otherwise 0 |
| initcmd | String | | Run this SQL statement upon connect |
Overview
This is where we tell the system what sources to use for its user
information. Linux and Solaris ship with an /etc/nsswitch.conf
containing many lines. On Solaris and FreeBSD, we worry about 2 -
'password' and 'group'. On Linux we worry about 3 - 'password',
'shadow', and 'group'. On FreeBSD you must create /etc/nsswitch.conf.
There are a multitude of possible different configurations. Essentially
you want to tell the system which databases to query under what
circumstances and in what order. I recommend 'files' (/etc/passwd)
first, followed by 'mysql'.
The Gory Details
The relevant lines might look something like this (note that the
'shadow' line shouldn't be used except on Linux systems):
passwd: files mysql
shadow: files mysql
group: files mysql
I want to mention NSCD as it can greatly enhance the performance
of your libnss-mysql installation. NSCD - Name Service Cache Daemon -
caches requests made to the NSS system. For example, if you were
running NSCD and you typed 'id cinergi' for the first time, the system
would have to directly query the MySQL database - a slow process
compared to retrieving the data from cache. Once NSCD has cached it,
subsequent commands like 'id cinergi' would come from the in-memory
cache that NSCD creates - a huge performance boost, especially in
a multi-threaded situation. You'll want to 'man nscd' on your system
for more information on how it works, tuning, etc. You may need to
enable the daemon such that it's started at boot-time. You should
note that NSCD does not cache shadow/password information
NOTE: Since all queries route through NSCD if you have it enabled,
changes to libnss-mysql configuration files and /etc/nsswitch.conf
will not be seen until you restart NSCD. If you continue
having problems, restart any daemons in question or reboot the
system.
Rudimentary testing has shown that /etc/passwd user access is far
faster than MySQL access, even with thousands of users. Once you start
going into 10's or 100's of thousands, single-user lookups are much
faster on MySQL. However, complete passwd scans (like fuzzy 'finger'
matching) remain faster on /etc/passwd. That said, it's not
libnss-mysql's fault; libnss-mysql adds about 30% overhead to the
SQL queries themselves. Querying the database simply takes more time
than it does to read a relatively small file.
In multi-threaded situations, libnss-mysql performance drops
noticeably. Where possible, run multi-process software or use
NSCD (which is multi-threaded). Multi-platform library threading
is not something I want to get into at the moment.
|