I had this working fine this morning and now its stopped working again after a few minor changes to the SQL statements.
My libnss-mysql.cfg looks like this:
[queries]
getpwnam SELECT username,'x',uid,'5000','MySQL User',homedir,shell FROM users WHERE username='%s' LIMIT 1
getpwuid SELECT username,'x',uid,'5000','MySQL User',homedir,shell FROM users WHERE uid='%u' LIMIT 1
getspnam SELECT username,passwd,'1','0','99999','0','0','-1','0' FROM users WHERE username='%s' LIMIT 1
getpwent SELECT username,'x',uid,'5000','MySQL User',homedir,shell FROM users
getspent SELECT username,passwd,'1','0','99999','0','0','-1','0' FROM users
getgrnam SELECT name,passwd,gid FROM groups WHERE name='%s' LIMIT 1
getgrgid SELECT name,passwd,gid FROM groups WHERE gid='%u' LIMIT 1
getgrent SELECT name,passwd,gid FROM groups
memsbygid SELECT username FROM grouplist WHERE gid='%u'
gidsbymem SELECT gid FROM grouplist where username='%s'
(I omitted the [server[ section but can verify it connects OK).
What happens if you manually do the query twice - once using the non-root username/password and once with it?
You can turn on MySQL query logging (log=/path/to/log in /etc/my.cnf) or libnss-mysql debugging (recompile) or use strace/ltrace.
I assume you've set PersistentPasswd to Off in your proftpd config?
Does "id" work (try as root and as non-root)?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Yes I had debugging enabled and the appropriate Proftpd config.
But I realized I had not tested as a non-root user and then realized that libnss-mysql.cfg has 0600 perms. As soon as I had fixed that it started working again.
Proftpd is working now too. This is on a web server, and by using rssh as a login shell I can also give users scp/sftp access without giving them a login. Very cool.
I dont suppose you (or anyone else) knows of a way of putting system quota info in MySQL too. Would be great to have all account info in MySQL so we can write a nice web interface for it.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Woops, I never answered your quota question. That's something I'd like to see myself, but don't know of a way to directly do it in the filesystem (other than some sort of daemon that keeps things in sync). If a limited set of programs are doing the file I/O (e.g. just ftp and mail), then you can probably find a daemon for each that talks MySQL and can look at a MySQL field for quota enforcement.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I had this working fine this morning and now its stopped working again after a few minor changes to the SQL statements.
My libnss-mysql.cfg looks like this:
[queries]
getpwnam SELECT username,'x',uid,'5000','MySQL User',homedir,shell FROM users WHERE username='%s' LIMIT 1
getpwuid SELECT username,'x',uid,'5000','MySQL User',homedir,shell FROM users WHERE uid='%u' LIMIT 1
getspnam SELECT username,passwd,'1','0','99999','0','0','-1','0' FROM users WHERE username='%s' LIMIT 1
getpwent SELECT username,'x',uid,'5000','MySQL User',homedir,shell FROM users
getspent SELECT username,passwd,'1','0','99999','0','0','-1','0' FROM users
getgrnam SELECT name,passwd,gid FROM groups WHERE name='%s' LIMIT 1
getgrgid SELECT name,passwd,gid FROM groups WHERE gid='%u' LIMIT 1
getgrent SELECT name,passwd,gid FROM groups
memsbygid SELECT username FROM grouplist WHERE gid='%u'
gidsbymem SELECT gid FROM grouplist where username='%s'
(I omitted the [server[ section but can verify it connects OK).
My users table looks like this:
mysql> select * from users;
+----------+------+------------------------------------+----------------+---------------+
| username | uid | passwd | homedir | shell |
+----------+------+------------------------------------+----------------+---------------+
| cinergi | 5000 | $1$QEDwApOH$JVh0/bYAMNfNN19WEsBv/0 | /home/nowhere.com | /usr/bin/rssh |
+----------+------+------------------------------------+----------------+---------------+
1 row in set (0.00 sec)
This is just a test account.
When I try a login with say, ftp, I see this in my ftpd.log:
Oct 15 14:44:15 proftpd[10442] localhost : no such user 'cinergi'
If I manually do the same query as the getpwnam line uses, the query works fine.
What happens if you manually do the query twice - once using the non-root username/password and once with it?
You can turn on MySQL query logging (log=/path/to/log in /etc/my.cnf) or libnss-mysql debugging (recompile) or use strace/ltrace.
I assume you've set PersistentPasswd to Off in your proftpd config?
Does "id" work (try as root and as non-root)?
Yes I had debugging enabled and the appropriate Proftpd config.
But I realized I had not tested as a non-root user and then realized that libnss-mysql.cfg has 0600 perms. As soon as I had fixed that it started working again.
Proftpd is working now too. This is on a web server, and by using rssh as a login shell I can also give users scp/sftp access without giving them a login. Very cool.
I dont suppose you (or anyone else) knows of a way of putting system quota info in MySQL too. Would be great to have all account info in MySQL so we can write a nice web interface for it.
Woops, I never answered your quota question. That's something I'd like to see myself, but don't know of a way to directly do it in the filesystem (other than some sort of daemon that keeps things in sync). If a limited set of programs are doing the file I/O (e.g. just ftp and mail), then you can probably find a daemon for each that talks MySQL and can look at a MySQL field for quota enforcement.