Menu
▾
▴
pam-mysql-general
|
From: xyon <xy...@in...> - 2007-04-03 21:06:07
|
Good day to everyone,
I am having a hard time getting pam_mysql to work with a custom
database. Here is some information:
Code:
=20
# grep -v ^# /var/opt/scalix/sx/s/sys/pam.d/ual.remote=20
=20
auth sufficient /lib/security/pam_mysql.so \=20
user=3Dscalix passwd=3Dmypass \=20
host=3D127.0.0.1 db=3Dstaging table=3Dusers \=20
usercolumn=3Dscalix passwdcolumn=3Dpwd \=20
crypt=3D0 verbose=3D1=20
auth required pam_deny=20
=20
account required om_auth=20
password required om_auth nullok=20
/var/log/secure:=20
Code:
=20
Apr 2 17:24:45 scalix in.imap41d: pam_mysql - SELECT pwd FROM users
WHERE scalix =3D 'UserTestscalixTest User'=20
Apr 2 17:24:45 scalix in.imap41d: pam_mysql - SELECT returned no
result.=20
Apr 2 17:24:45 scalix in.imap41d: pam_mysql - pam_mysql_check_passwd()
returning 1.=20
Apr 2 17:24:45 scalix in.imap41d: pam_mysql - pam_sm_authenticate()
returning 10.=20
=20
Code:
=20
mysql> SELECT pwd FROM users WHERE scalix =3D 'UserTestscalixTest User';=20
+------+=20
| pwd |=20
+------+=20
| test |=20
+------+=20
1 row in set (0.00 sec)=20
=20
mysql>=20
=20
As you can see running the query manually brings back results, but it
appears it doesn't when run from pam_mysql.
Perhaps I have a misconfiguration in the ual.remote file? The ual.remote
file is used by Scalix to login web/imap/pop users.
Is there a way to test pam_mysql without scalix being involved so I can
narrow down the culprits?
Thanks,
--=20
xyon
-------------------------------------------------------------------------
"They who can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety." --Benjamin Franklin
-------------------------------------------------------------------------
|
|
From: Gary W. S. <ga...@pr...> - 2007-04-03 22:17:40
|
Xyon,=20 Try to turn on debugging for pam-mysql. If you do that then it will put the full SQL statement executed into the security log (or /var/log/secure on Linux). This will help see what pam-mysql is thinking. It's possible that it's doing something completely different than what your are expecting. Personally I use a custom SQL statement with pam-mysql. Hope that helps. Gary Wayne Smith > -----Original Message----- > From: pam...@li... [mailto:pam- > mys...@li...] On Behalf Of xyon > Sent: Tuesday, April 03, 2007 2:06 PM > To: pam...@li... > Subject: [Pam-mysql-general] SELECT returned no result >=20 > Good day to everyone, >=20 > I am having a hard time getting pam_mysql to work with a custom > database. Here is some information: >=20 >=20 > Code: >=20 > # grep -v ^# /var/opt/scalix/sx/s/sys/pam.d/ual.remote >=20 > auth sufficient /lib/security/pam_mysql.so \ > user=3Dscalix passwd=3Dmypass \ > host=3D127.0.0.1 db=3Dstaging table=3Dusers = \ > usercolumn=3Dscalix passwdcolumn=3Dpwd \ > crypt=3D0 verbose=3D1 > auth required pam_deny >=20 > account required om_auth > password required om_auth nullok >=20 >=20 > /var/log/secure: > Code: >=20 > Apr 2 17:24:45 scalix in.imap41d: pam_mysql - SELECT pwd FROM users > WHERE scalix =3D 'UserTestscalixTest User' > Apr 2 17:24:45 scalix in.imap41d: pam_mysql - SELECT returned no > result. > Apr 2 17:24:45 scalix in.imap41d: pam_mysql - pam_mysql_check_passwd() > returning 1. > Apr 2 17:24:45 scalix in.imap41d: pam_mysql - pam_sm_authenticate() > returning 10. >=20 >=20 >=20 > Code: >=20 > mysql> SELECT pwd FROM users WHERE scalix =3D 'UserTestscalixTest = User'; > +------+ > | pwd | > +------+ > | test | > +------+ > 1 row in set (0.00 sec) >=20 > mysql> >=20 >=20 >=20 > As you can see running the query manually brings back results, but it > appears it doesn't when run from pam_mysql. >=20 > Perhaps I have a misconfiguration in the ual.remote file? The > ual.remote file is used by Scalix to login web/imap/pop users. >=20 > Is there a way to test pam_mysql without scalix being involved so I can > narrow down the culprits? >=20 > Thanks, >=20 > -- > xyon >=20 > ----------------------------------------------------------------------- > -- > "They who can give up essential liberty to obtain a little temporary > safety deserve neither liberty nor safety." --Benjamin Franklin > ----------------------------------------------------------------------- > -- |
|
From: xyon <xy...@in...> - 2007-04-03 22:43:55
|
Hi Gary, Thank you so much for your reply. I thought I had debugging turned on with "verbose=3D1", but perhaps there's even more verbose debugging available? Here is what I've extracted from /var/log/secure (I apologize for the bad formatting in my previous message): pam_mysql - SELECT pwd FROM users WHERE scalix =3D 'UserTestscalixTest User' I didn't realize you could use a fully customized SQL statement with PAM_MySQL. Would you mind sharing some of your config, please? Thank you, On Tue, 2007-04-03 at 15:17 -0700, Gary W. Smith wrote: > Xyon,=20 >=20 > Try to turn on debugging for pam-mysql. If you do that then it will put > the full SQL statement executed into the security log (or > /var/log/secure on Linux). This will help see what pam-mysql is > thinking. It's possible that it's doing something completely different > than what your are expecting. >=20 > Personally I use a custom SQL statement with pam-mysql. >=20 > Hope that helps. >=20 > Gary Wayne Smith >=20 > > -----Original Message----- > > From: pam...@li... [mailto:pam- > > mys...@li...] On Behalf Of xyon > > Sent: Tuesday, April 03, 2007 2:06 PM > > To: pam...@li... > > Subject: [Pam-mysql-general] SELECT returned no result > >=20 > > Good day to everyone, > >=20 > > I am having a hard time getting pam_mysql to work with a custom > > database. Here is some information: > >=20 > >=20 > > Code: > >=20 > > # grep -v ^# /var/opt/scalix/sx/s/sys/pam.d/ual.remote > >=20 > > auth sufficient /lib/security/pam_mysql.so \ > > user=3Dscalix passwd=3Dmypass \ > > host=3D127.0.0.1 db=3Dstaging table=3Dusers \ > > usercolumn=3Dscalix passwdcolumn=3Dpwd \ > > crypt=3D0 verbose=3D1 > > auth required pam_deny > >=20 > > account required om_auth > > password required om_auth nullok > >=20 > >=20 > > /var/log/secure: > > Code: > >=20 > > Apr 2 17:24:45 scalix in.imap41d: pam_mysql - SELECT pwd FROM users > > WHERE scalix =3D 'UserTestscalixTest User' > > Apr 2 17:24:45 scalix in.imap41d: pam_mysql - SELECT returned no > > result. > > Apr 2 17:24:45 scalix in.imap41d: pam_mysql - > pam_mysql_check_passwd() > > returning 1. > > Apr 2 17:24:45 scalix in.imap41d: pam_mysql - pam_sm_authenticate() > > returning 10. > >=20 > >=20 > >=20 > > Code: > >=20 > > mysql> SELECT pwd FROM users WHERE scalix =3D 'UserTestscalixTest User'= ; > > +------+ > > | pwd | > > +------+ > > | test | > > +------+ > > 1 row in set (0.00 sec) > >=20 > > mysql> > >=20 > >=20 > >=20 > > As you can see running the query manually brings back results, but it > > appears it doesn't when run from pam_mysql. > >=20 > > Perhaps I have a misconfiguration in the ual.remote file? The > > ual.remote file is used by Scalix to login web/imap/pop users. > >=20 > > Is there a way to test pam_mysql without scalix being involved so I > can > > narrow down the culprits? > >=20 > > Thanks, > >=20 > > -- > > xyon > >=20 > > > ----------------------------------------------------------------------- > > -- > > "They who can give up essential liberty to obtain a little temporary > > safety deserve neither liberty nor safety." --Benjamin Franklin > > > ----------------------------------------------------------------------- > > -- >=20 --=20 xyon ------------------------------------------------------------------------- "They who can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." --Benjamin Franklin ------------------------------------------------------------------------- |
|
From: Gary W. S. <ga...@pr...> - 2007-04-03 22:51:43
|
Hmm, I guess I didn't use the complete SQL statement. That was
nss-mysql. Anyway, here is my pam-mysql statement. This may or may not
work for you. I have to create a custom patch of either pam-mysql or
nss-mysql to use my complicated joins. I can't remember which one off
the top of my head and the code isn't redily available where I'm at
today. Anyway, here is the pam.d/ file and the pam-mysql.conf file
(which is loaded from the pam.d/ file).
Please note that this is from one of our dev boxes and the passwords are
in clear text
Hope this helps.
[root@ etc]# cat pam.d/pop3
#%PAM-1.0
#auth required /lib/security/pam_stack.so =
service=3Dsystem-auth
#account required /lib/security/pam_stack.so =
service=3Dsystem-auth
auth sufficient /lib/security/pam_mysql.so
config_file=3D/etc/pam-mysql.conf
auth required /lib/security/pam_stack.so service=3Dsystem-auth
account sufficient /lib/security/pam_mysql.so
config_file=3D/etc/pam-mysql.conf
account required /lib/security/pam_stack.so service=3Dsystem-auth
session required pam_mkhomedir.so skel=3D/etc/skel =
umask=3D0077
[root@ etc]# cat pam-mysql.conf=20
users.host =3D ;
users.database =3D ;
users.db_user =3D ;
users.db_passwd =3D ;
users.where_clause =3D domainusers.active =3D 1 and domains.active=3D1 =
and
servers.active =3D 1 and servers.server_name in ('servername','*');
users.table =3D domainusers inner join domains on domainusers.domain_id =
=3D
domains.domain_id inner join servers on domains.server_id =3D
servers.server_id;
users.user_column =3D domainusers.user_name;
users.password_column =3D domainusers.shell_password;
users.status_column =3D 'A';
users.password_crypt =3D 0;
users.use_323_password =3D false;
users.use_md5 =3D false;
users.update_table =3D ;
verbose =3D 0;
> -----Original Message-----
> From: pam...@li... [mailto:pam-
> mys...@li...] On Behalf Of xyon
> Sent: Tuesday, April 03, 2007 3:44 PM
> To: Gary W. Smith
> Cc: pam...@li...
> Subject: Re: [Pam-mysql-general] SELECT returned no result
>=20
> Hi Gary,
>=20
> Thank you so much for your reply.
>=20
> I thought I had debugging turned on with "verbose=3D1", but perhaps
> there's even more verbose debugging available?
>=20
> Here is what I've extracted from /var/log/secure (I apologize for the
> bad formatting in my previous message):
> pam_mysql - SELECT pwd FROM users WHERE scalix =3D 'UserTestscalixTest
> User'
>=20
> I didn't realize you could use a fully customized SQL statement with
> PAM_MySQL. Would you mind sharing some of your config, please?
>=20
> Thank you,
>=20
> On Tue, 2007-04-03 at 15:17 -0700, Gary W. Smith wrote:
> > Xyon,
> >
> > Try to turn on debugging for pam-mysql. If you do that then it will
> > put the full SQL statement executed into the security log (or
> > /var/log/secure on Linux). This will help see what pam-mysql is
> > thinking. It's possible that it's doing something completely
> > different than what your are expecting.
> >
> > Personally I use a custom SQL statement with pam-mysql.
> >
> > Hope that helps.
> >
> > Gary Wayne Smith
> >
> > > -----Original Message-----
> > > From: pam...@li... [mailto:pam-
> > > mys...@li...] On Behalf Of xyon
> > > Sent: Tuesday, April 03, 2007 2:06 PM
> > > To: pam...@li...
> > > Subject: [Pam-mysql-general] SELECT returned no result
> > >
> > > Good day to everyone,
> > >
> > > I am having a hard time getting pam_mysql to work with a custom
> > > database. Here is some information:
> > >
> > >
> > > Code:
> > >
> > > # grep -v ^# /var/opt/scalix/sx/s/sys/pam.d/ual.remote
> > >
> > > auth sufficient /lib/security/pam_mysql.so \
> > > user=3Dscalix passwd=3Dmypass \
> > > host=3D127.0.0.1 db=3Dstaging =
table=3Dusers \
> > > usercolumn=3Dscalix passwdcolumn=3Dpwd =
\
> > > crypt=3D0 verbose=3D1
> > > auth required pam_deny
> > >
> > > account required om_auth
> > > password required om_auth nullok
> > >
> > >
> > > /var/log/secure:
> > > Code:
> > >
> > > Apr 2 17:24:45 scalix in.imap41d: pam_mysql - SELECT pwd FROM
> users
> > > WHERE scalix =3D 'UserTestscalixTest User'
> > > Apr 2 17:24:45 scalix in.imap41d: pam_mysql - SELECT returned
> no
> > > result.
> > > Apr 2 17:24:45 scalix in.imap41d: pam_mysql -
> > pam_mysql_check_passwd()
> > > returning 1.
> > > Apr 2 17:24:45 scalix in.imap41d: pam_mysql -
> pam_sm_authenticate()
> > > returning 10.
> > >
> > >
> > >
> > > Code:
> > >
> > > mysql> SELECT pwd FROM users WHERE scalix =3D 'UserTestscalixTest
> > > mysql> User';
> > > +------+
> > > | pwd |
> > > +------+
> > > | test |
> > > +------+
> > > 1 row in set (0.00 sec)
> > >
> > > mysql>
> > >
> > >
> > >
> > > As you can see running the query manually brings back results, but
> > > it appears it doesn't when run from pam_mysql.
> > >
> > > Perhaps I have a misconfiguration in the ual.remote file? The
> > > ual.remote file is used by Scalix to login web/imap/pop users.
> > >
> > > Is there a way to test pam_mysql without scalix being involved so
I
> > can
> > > narrow down the culprits?
> > >
> > > Thanks,
> > >
> > > --
> > > xyon
> > >
> > >
> >
---------------------------------------------------------------------
> -
> > -
> > > --
> > > "They who can give up essential liberty to obtain a little
> temporary
> > > safety deserve neither liberty nor safety." --Benjamin Franklin
> > >
> >
---------------------------------------------------------------------
> -
> > -
> > > --
> >
> --
> xyon
>=20
>
-----------------------------------------------------------------------
> --
> "They who can give up essential liberty to obtain a little temporary
> safety deserve neither liberty nor safety." --Benjamin Franklin
>
-----------------------------------------------------------------------
> --
|
|
From: xyon <xy...@in...> - 2007-04-04 14:00:13
|
Thank you very much for the samples! I've extracted your data and have
come up with the below configs.
Yet, I am still getting the same error that SELECT returned no result. I
can run the same query with results just fine manually as the scalix
user from the scalix host. I've gone from pam_mysql 0.6.2 to 0.7RC1, yet
the same results.
# cat /var/opt/scalix/sx/s/sys/pam.d/ual.remote
#%PAM-1.0
#auth required /lib/security/pam_stack.so service=3Dsystem-auth
#account required /lib/security/pam_stack.so service=3Dsystem-auth
auth sufficient /lib/security/pam_mysql.so
config_file=3D/etc/pam-mysql.conf
account sufficient /lib/security/pam_mysql.so
config_file=3D/etc/pam-mysql.conf
# cat /etc/pam-mysql.conf
users.host =3D 127.0.0.1;
users.database =3D staging;
users.db_user =3D scalix;
users.db_passwd =3D mypass;
#users.where_clause =3D domainusers.active =3D 1 and domains.active=3D1 and
servers.active =3D 1 and servers.server_name in ('servername','*');
#users.table =3D domainusers inner join domains on domainusers.domain_id =
=3D
domains.domain_id inner join servers on domains.server_id =3D
servers.server_id;
users.table =3D users;
users.user_column =3D scalix;
users.password_column =3D pwd;
#users.status_column =3D 'A';
users.password_crypt =3D 0;
users.use_323_password =3D false;
users.use_md5 =3D false;
users.update_table =3D ;
verbose =3D 1;
On Tue, 2007-04-03 at 15:51 -0700, Gary W. Smith wrote:
> Hmm, I guess I didn't use the complete SQL statement. That was
> nss-mysql. Anyway, here is my pam-mysql statement. This may or may not
> work for you. I have to create a custom patch of either pam-mysql or
> nss-mysql to use my complicated joins. I can't remember which one off
> the top of my head and the code isn't redily available where I'm at
> today. Anyway, here is the pam.d/ file and the pam-mysql.conf file
> (which is loaded from the pam.d/ file).
>=20
> Please note that this is from one of our dev boxes and the passwords are
> in clear text
>=20
> Hope this helps.
>=20
> [root@ etc]# cat pam.d/pop3
> #%PAM-1.0
> #auth required /lib/security/pam_stack.so service=3Dsystem-auth
> #account required /lib/security/pam_stack.so service=3Dsystem-auth
>=20
> auth sufficient /lib/security/pam_mysql.so
> config_file=3D/etc/pam-mysql.conf
> auth required /lib/security/pam_stack.so service=3Dsystem-auth
> account sufficient /lib/security/pam_mysql.so
> config_file=3D/etc/pam-mysql.conf
> account required /lib/security/pam_stack.so service=3Dsystem-auth
> session required pam_mkhomedir.so skel=3D/etc/skel umask=3D007=
7
>=20
> [root@ etc]# cat pam-mysql.conf=20
> users.host =3D ;
> users.database =3D ;
> users.db_user =3D ;
> users.db_passwd =3D ;
>=20
> users.where_clause =3D domainusers.active =3D 1 and domains.active=3D1 an=
d
> servers.active =3D 1 and servers.server_name in ('servername','*');
>=20
> users.table =3D domainusers inner join domains on domainusers.domain_id =
=3D
> domains.domain_id inner join servers on domains.server_id =3D
> servers.server_id;
>=20
> users.user_column =3D domainusers.user_name;
> users.password_column =3D domainusers.shell_password;
> users.status_column =3D 'A';
> users.password_crypt =3D 0;
> users.use_323_password =3D false;
> users.use_md5 =3D false;
> users.update_table =3D ;
>=20
> verbose =3D 0;
>=20
>=20
> > -----Original Message-----
> > From: pam...@li... [mailto:pam-
> > mys...@li...] On Behalf Of xyon
> > Sent: Tuesday, April 03, 2007 3:44 PM
> > To: Gary W. Smith
> > Cc: pam...@li...
> > Subject: Re: [Pam-mysql-general] SELECT returned no result
> >=20
> > Hi Gary,
> >=20
> > Thank you so much for your reply.
> >=20
> > I thought I had debugging turned on with "verbose=3D1", but perhaps
> > there's even more verbose debugging available?
> >=20
> > Here is what I've extracted from /var/log/secure (I apologize for the
> > bad formatting in my previous message):
> > pam_mysql - SELECT pwd FROM users WHERE scalix =3D 'UserTestscalixTest
> > User'
> >=20
> > I didn't realize you could use a fully customized SQL statement with
> > PAM_MySQL. Would you mind sharing some of your config, please?
> >=20
> > Thank you,
> >=20
> > On Tue, 2007-04-03 at 15:17 -0700, Gary W. Smith wrote:
> > > Xyon,
> > >
> > > Try to turn on debugging for pam-mysql. If you do that then it will
> > > put the full SQL statement executed into the security log (or
> > > /var/log/secure on Linux). This will help see what pam-mysql is
> > > thinking. It's possible that it's doing something completely
> > > different than what your are expecting.
> > >
> > > Personally I use a custom SQL statement with pam-mysql.
> > >
> > > Hope that helps.
> > >
> > > Gary Wayne Smith
> > >
> > > > -----Original Message-----
> > > > From: pam...@li... [mailto:pam-
> > > > mys...@li...] On Behalf Of xyon
> > > > Sent: Tuesday, April 03, 2007 2:06 PM
> > > > To: pam...@li...
> > > > Subject: [Pam-mysql-general] SELECT returned no result
> > > >
> > > > Good day to everyone,
> > > >
> > > > I am having a hard time getting pam_mysql to work with a custom
> > > > database. Here is some information:
> > > >
> > > >
> > > > Code:
> > > >
> > > > # grep -v ^# /var/opt/scalix/sx/s/sys/pam.d/ual.remote
> > > >
> > > > auth sufficient /lib/security/pam_mysql.so \
> > > > user=3Dscalix passwd=3Dmypass \
> > > > host=3D127.0.0.1 db=3Dstaging table=3Duse=
rs \
> > > > usercolumn=3Dscalix passwdcolumn=3Dpwd \
> > > > crypt=3D0 verbose=3D1
> > > > auth required pam_deny
> > > >
> > > > account required om_auth
> > > > password required om_auth nullok
> > > >
> > > >
> > > > /var/log/secure:
> > > > Code:
> > > >
> > > > Apr 2 17:24:45 scalix in.imap41d: pam_mysql - SELECT pwd FROM
> > users
> > > > WHERE scalix =3D 'UserTestscalixTest User'
> > > > Apr 2 17:24:45 scalix in.imap41d: pam_mysql - SELECT returned
> > no
> > > > result.
> > > > Apr 2 17:24:45 scalix in.imap41d: pam_mysql -
> > > pam_mysql_check_passwd()
> > > > returning 1.
> > > > Apr 2 17:24:45 scalix in.imap41d: pam_mysql -
> > pam_sm_authenticate()
> > > > returning 10.
> > > >
> > > >
> > > >
> > > > Code:
> > > >
> > > > mysql> SELECT pwd FROM users WHERE scalix =3D 'UserTestscalixTest
> > > > mysql> User';
> > > > +------+
> > > > | pwd |
> > > > +------+
> > > > | test |
> > > > +------+
> > > > 1 row in set (0.00 sec)
> > > >
> > > > mysql>
> > > >
> > > >
> > > >
> > > > As you can see running the query manually brings back results, but
> > > > it appears it doesn't when run from pam_mysql.
> > > >
> > > > Perhaps I have a misconfiguration in the ual.remote file? The
> > > > ual.remote file is used by Scalix to login web/imap/pop users.
> > > >
> > > > Is there a way to test pam_mysql without scalix being involved so
> I
> > > can
> > > > narrow down the culprits?
> > > >
> > > > Thanks,
> > > >
> > > > --
> > > > xyon
> > > >
> > > >
> > >
> ---------------------------------------------------------------------
> > -
> > > -
> > > > --
> > > > "They who can give up essential liberty to obtain a little
> > temporary
> > > > safety deserve neither liberty nor safety." --Benjamin Franklin
> > > >
> > >
> ---------------------------------------------------------------------
> > -
> > > -
> > > > --
> > >
> > --
> > xyon
> >=20
> >
> -----------------------------------------------------------------------
> > --
> > "They who can give up essential liberty to obtain a little temporary
> > safety deserve neither liberty nor safety." --Benjamin Franklin
> >
> -----------------------------------------------------------------------
> > --
>=20
--=20
xyon
-------------------------------------------------------------------------
"They who can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety." --Benjamin Franklin
-------------------------------------------------------------------------
|
|
From: Gary W. S. <ga...@pr...> - 2007-04-04 14:39:06
|
> #users.where_clause =3D domainusers.active =3D 1 and =
domains.active=3D1 and
> servers.active =3D 1 and servers.server_name in ('servername','*');
>=20
> #users.table =3D domainusers inner join domains on =
domainusers.domain_id
> =3D domains.domain_id inner join servers on domains.server_id =3D
> servers.server_id; users.table =3D users;
>=20
> users.user_column =3D scalix;
> users.password_column =3D pwd;
> #users.status_column =3D 'A';
Why are you commenting out the where, table and status_columns? Leave
users.status_column =3D 'A' in place. It will have no impact but I
believe it's a required field.
You commented out the table and where clause but didn't put another one
in. =20
|
|
From: xyon <xy...@in...> - 2007-04-04 15:25:31
|
Ah, I think the formatting of the email left some things out, but you
are correct I did leave user.status_column and users.where_clause
undefined, I thought they were not needed, I apologize.
Here is what I currently have:
users.host =3D 127.0.0.1;
users.database =3D staging;
users.db_user =3D scalix;
users.db_passwd =3D mypass;
users.where_clause =3D idUsers > 0;
users.table =3D users;
users.user_column =3D scalix;
users.password_column =3D pwd;
users.status_column =3D 'A';
users.password_crypt =3D 0;
users.use_323_password =3D false;
users.use_md5 =3D false;
users.update_table =3D ;
verbose =3D 1;
The reason I put a where clause like that listed above is when
'users.where_clause' is defined, pam_mysql adds an "AND ()" to the end
of the statement causing the query to break:
"MySQL error(You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near ')' at line 1)"
"SELECT pwd FROM users WHERE scalix =3D 'lastfirstscalixfirst last' AND
()"
I've rebooted just in case pam_mysql had any cached settings from stale
connections, and tried with the above config, but I still receive
"SELECT returned no result", while a manual query works fine. Is there
further debugging I can turn on in pam_mysql?
FYI, this is an stunnel connection redirecting 127.0.0.1:3306 to a
remote mysql host, could that have anything to do with the problems?
MySQL from the linux command line as the user defined works fine,
though.
On Wed, 2007-04-04 at 07:39 -0700, Gary W. Smith wrote:
> > #users.where_clause =3D domainusers.active =3D 1 and domains.active=3D1=
and
> > servers.active =3D 1 and servers.server_name in ('servername','*');
> >=20
> > #users.table =3D domainusers inner join domains on domainusers.domain_i=
d
> > =3D domains.domain_id inner join servers on domains.server_id =3D
> > servers.server_id; users.table =3D users;
> >=20
> > users.user_column =3D scalix;
> > users.password_column =3D pwd;
> > #users.status_column =3D 'A';
>=20
> Why are you commenting out the where, table and status_columns? Leave
> users.status_column =3D 'A' in place. It will have no impact but I
> believe it's a required field.
>=20
> You commented out the table and where clause but didn't put another one
> in. =20
--=20
xyon
-------------------------------------------------------------------------
"They who can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety." --Benjamin Franklin
-------------------------------------------------------------------------
|
|
From: Gary W. S. <ga...@pr...> - 2007-04-04 15:40:14
|
> "SELECT pwd FROM users WHERE scalix =3D 'lastfirstscalixfirst last' = AND > ()" In this case you could also just do 1=3D1 in the where clause. =20 > FYI, this is an stunnel connection redirecting 127.0.0.1:3306 to a > remote mysql host, could that have anything to do with the problems? > MySQL from the linux command line as the user defined works fine, > though. This could have something to do with it. I don't do much with stunnels. As for the debugging, are you seeing the expected SQL statement as output in the secure log file? If so can you post the section of the log for an entire pam-mysql access? |
|
From: xyon <xy...@in...> - 2007-04-04 17:12:13
|
On Wed, 2007-04-04 at 08:40 -0700, Gary W. Smith wrote: > > "SELECT pwd FROM users WHERE scalix =3D 'lastfirstscalixfirst last' AND > > ()" >=20 >=20 > In this case you could also just do 1=3D1 in the where clause. =20 Ah, I see. Thanks! >=20 > > FYI, this is an stunnel connection redirecting 127.0.0.1:3306 to a > > remote mysql host, could that have anything to do with the problems? > > MySQL from the linux command line as the user defined works fine, > > though. >=20 > This could have something to do with it. I don't do much with stunnels. >=20 >=20 > As for the debugging, are you seeing the expected SQL statement as > output in the secure log file? If so can you post the section of the > log for an entire pam-mysql access? Certainly, here it is: =3D=3D> /var/log/secure <=3D=3D Apr 4 11:24:50 scalix in.imap41d: pam_mysql - option verbose is set to "1" Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_sm_authenticate() called. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_open_db() called. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_open_db() returning 0. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_check_passwd() called. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_format_string() called Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_quick_escape() called. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - SELECT pwd FROM users WHERE scalix =3D 'UserTestscalixTest User' AND (idUsers > 0) Apr 4 11:24:50 scalix in.imap41d: pam_mysql - SELECT returned no result. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_check_passwd() returning 1. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_sql_log() called. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_sql_log() returning 0. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_converse() called. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_open_db() called. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_check_passwd() called. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_format_string() called Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_quick_escape() called. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - SELECT pwd FROM users WHERE scalix =3D 'UserTestscalixTest User' AND (idUsers > 0) Apr 4 11:24:50 scalix in.imap41d: pam_mysql - SELECT returned no result. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_check_passwd() returning 1. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_sql_log() called. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_sql_log() returning 0. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_sm_authenticate() returning 10. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_release_ctx() called. Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_destroy_ctx() called. Apr 4 11:24:51 scalix in.imap41d: pam_mysql - pam_mysql_close_db() called. The statement used by pam_mysql (SELECT pwd FROM users WHERE scalix =3D 'UserTestscalixTest User' AND (idUsers > 0)) returns results when run from the mysql prompt. --=20 xyon ------------------------------------------------------------------------- "They who can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." --Benjamin Franklin ------------------------------------------------------------------------- |
|
From: Gary W. S. <ga...@pr...> - 2007-04-04 17:37:36
|
> =3D=3D> /var/log/secure <=3D=3D > Apr 4 11:24:50 scalix in.imap41d: pam_mysql - option verbose is set to > "1" > Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_sm_authenticate() > called. > Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_open_db() > called. > Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_open_db() > returning 0. > Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_check_passwd() > called. > Apr 4 11:24:50 scalix in.imap41d: pam_mysql - > pam_mysql_format_string() called Apr 4 11:24:50 scalix in.imap41d: > pam_mysql - pam_mysql_quick_escape() called. > Apr 4 11:24:50 scalix in.imap41d: pam_mysql - SELECT pwd FROM users > WHERE scalix =3D 'UserTestscalixTest User' AND (idUsers > 0) Apr 4 SELECT pwd FROM users WHERE scalix =3D 'UserTestscalixTest User' AND (idUsers > 0) Is this just a typo (or replacement) or does the username actually have a space in it? Also, when you cut and paste this exact SQL statement into mysql client do you indeed get a new result. If you do then it could be the tunnel. |
|
From: xyon <xy...@in...> - 2007-04-04 17:53:39
|
On Wed, 2007-04-04 at 10:37 -0700, Gary W. Smith wrote: > > =3D=3D> /var/log/secure <=3D=3D > > Apr 4 11:24:50 scalix in.imap41d: pam_mysql - option verbose is set > to > > "1" > > Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_sm_authenticate() > > called. > > Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_open_db() > > called. > > Apr 4 11:24:50 scalix in.imap41d: pam_mysql - pam_mysql_open_db() > > returning 0. > > Apr 4 11:24:50 scalix in.imap41d: pam_mysql - > pam_mysql_check_passwd() > > called. > > Apr 4 11:24:50 scalix in.imap41d: pam_mysql - > > pam_mysql_format_string() called Apr 4 11:24:50 scalix in.imap41d: > > pam_mysql - pam_mysql_quick_escape() called. > > Apr 4 11:24:50 scalix in.imap41d: pam_mysql - SELECT pwd FROM users > > WHERE scalix =3D 'UserTestscalixTest User' AND (idUsers > 0) Apr 4 >=20 > SELECT pwd FROM users WHERE scalix =3D 'UserTestscalixTest User' AND > (idUsers > 0) >=20 > Is this just a typo (or replacement) or does the username actually have > a space in it? Yes, there is purposely a space. > Also, when you cut and paste this exact SQL statement > into mysql client do you indeed get a new result. I get a proper result when cutting and pasting this exact statement into the MySQL command line. > If you do then it could be the tunnel. I have eliminated the tunnel from the equation, and am just connecting to the MySQL host directly (since this is not in production and I'm using a test database/passwords and it's all on a private subnet, encryption isn't a big deal for troubleshooting) and have the exact same results (see below). I have the 'mysqlclient14' libs installed, but accessing a mysql5 server, although I don't see how this could be a problem as I've done this many times with other applications before. Here is the log results: =3D=3D> /var/log/secure <=3D=3D Apr 4 13:51:23 scalix in.imap41d: pam_mysql - option verbose is set to "1" Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_sm_authenticate() called. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_open_db() called. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_open_db() returning 0. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_check_passwd() called. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_format_string() called Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_quick_escape() called. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - SELECT pwd FROM users WHERE scalix =3D 'lastfirstscalixfirst last' AND (1=3D1) Apr 4 13:51:23 scalix in.imap41d: pam_mysql - SELECT returned no result. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_check_passwd() returning 1. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_sql_log() called. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_sql_log() returning 0. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_converse() called. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_open_db() called. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_check_passwd() called. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_format_string() called Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_quick_escape() called. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - SELECT pwd FROM users WHERE scalix =3D 'lastfirstscalixfirst last' AND (1=3D1) Apr 4 13:51:23 scalix in.imap41d: pam_mysql - SELECT returned no result. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_check_passwd() returning 1. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_sql_log() called. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_sql_log() returning 0. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_sm_authenticate() returning 10. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_release_ctx() called. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_destroy_ctx() called. Apr 4 13:51:23 scalix in.imap41d: pam_mysql - pam_mysql_close_db() called. --=20 xyon ------------------------------------------------------------------------- "They who can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." --Benjamin Franklin ------------------------------------------------------------------------- |
|
From: Gary W. S. <ga...@pr...> - 2007-04-04 17:57:07
|
> Apr 4 13:51:23 scalix in.imap41d: pam_mysql - SELECT pwd FROM users > WHERE scalix =3D 'lastfirstscalixfirst last' AND (1=3D1) Apr 4 = 13:51:23 > scalix in.imap41d: pam_mysql - SELECT returned no result. This should indeed return something if the same thing is return a result directly with mysql. This is beyond me. At this point I would just see about email the author to see if he knows anything else that we may have missed. Gary |
|
From: xyon <xy...@in...> - 2007-04-05 12:41:32
|
Understood. Thank you so much for all your help. I will post back here my findings. On Wed, 2007-04-04 at 10:57 -0700, Gary W. Smith wrote: > > Apr 4 13:51:23 scalix in.imap41d: pam_mysql - SELECT pwd FROM users > > WHERE scalix =3D 'lastfirstscalixfirst last' AND (1=3D1) Apr 4 13:51:2= 3 > > scalix in.imap41d: pam_mysql - SELECT returned no result. >=20 > This should indeed return something if the same thing is return a result > directly with mysql. This is beyond me. At this point I would just see > about email the author to see if he knows anything else that we may have > missed. >=20 > Gary --=20 xyon ------------------------------------------------------------------------- "They who can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." --Benjamin Franklin ------------------------------------------------------------------------- |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X