Mysql resolver user problem

Dear all

I try to use mysql database to be a new resolver and suffer from some
problems.
below is my setting.

create database ssh
grant all privileges for sshuser on ssh
create table sshlist which contains userid char(20),username char(20),
phone char(20), mobile char(20), email char(20), givenname char(20),surname
char(20), password char(20), PRIMARY KEY (userid))
add one user pi in the table.
insert info sshlist(userid, username, password) values(‘00000001’, ‘pi’,
‘12345678’);

open privacyidea web, and setup user resolver and realm.
I can see one user in user profile, but I cannot see user id in id column.
I cannot change password and create new account in web.
web login with the user also failed.

Is there any problem I should take care?

Harvey

Hi Cornelius

I try to use this resolver to store remote device account to do OTP
authentication when ssh login.
device account is pi;

table is :
mysql> select * from sshlist;±—±---------±----------±-------------±------±-------±-----------±----------±---------------+
| ID | username | password | email | phone | mobile | first_name |
last_name | description |
±—±---------±----------±-------------±------±-------±-----------±----------±---------------+
| 1 | pi | raspberry | pi@localhost | 0000 | 0000 | pi |
pi | ssh login user |
±—±---------±----------±-------------±------±-------±-----------±----------±---------------+
1 row in set (0.00 sec)

mapping table :
{ “userid” : “ID”, “username”: “username”, “password” : “password”, “email”
: “email”, “phone” : “phone”, “mobile” : “mobile”, “givenname” :
“first_name”, “surname” : “last_name”, “description” : “description”}

When I check user preview, I cannot see the id value. Is it correct?
Please see the attached screenshot.

And another password problem I already resolved. It seems need to use sha
to hash password and I can login web successfully.
I use web to change password.
mysql> select * from sshlist;
±—±---------±--------------------------------------------------------------------------±-------------±------±-------±-----------±----------±---------------+
| ID | username | password
| email | phone | mobile | first_name | last_name |
description |
±—±---------±--------------------------------------------------------------------------±-------------±------±-------±-----------±----------±---------------+
| 1 | pi |
{SSHA256}fQ+s953SEpuuJg9eRqKR8xY9ZoNp2OhquulMJ2Bo4eIWoHvc1f8hM3vl/9fQKpyN |
pi@localhost | 0000 | 0000 | pi | pi | ssh login user |
±—±---------±--------------------------------------------------------------------------±-------------±------±-------±-----------±----------±---------------+
1 row in set (0.00 sec)

I still cannot add new user by web because I cannot set ID value in the
content.
It will show below error message.
This Session’s transaction has been rolled back due to a previous exception
during flush. To begin a new transaction with this Session, first issue
Session.rollback(). Original exception was: Instance <MappedSshlist at
0x7f3e1dd6dfd0> has a NULL identity key.

Harvey

I have a similar issue with an existing vpopmail database.
My mapping is:
{ “userid” : “pw_record_id”, “username”: “pw_name”, “givenname” :
“pw_gecos”, “password” : “pw_passwd” }

pw_record_id is : int(25) AUTO_INCREMENT (just a rownumber)

Debug result contains:
{‘username’: u’rick’, ‘givenname’: u’Rick Romero’, ‘editable’: True,
‘resolver’: u’havokmon.com’, ‘password’: u’$6$rounds=5000$blahblah’, ‘id’:
3L},

3 is correct. Not sure what the L is.

Hi Rick,

as you can see, it is not ‘3L’ but 3L.
L means long integer in this case.
It is still an integer, no string.

Kind regards
CorneliusAm Donnerstag, den 14.07.2016, 07:33 -0700 schrieb Rick Romero:

I have a similar issue with an existing vpopmail database.
My mapping is:
{ “userid” : “pw_record_id”, “username”: “pw_name”, “givenname” :
“pw_gecos”, “password” : “pw_passwd” }

pw_record_id is : int(25) AUTO_INCREMENT (just a rownumber)

Debug result contains:
{‘username’: u’rick’, ‘givenname’: u’Rick Romero’, ‘editable’: True,
‘resolver’: u’havokmon.com’, ‘password’: u’$6$rounds=5000$blahblah’,
‘id’: 3L},

3 is correct. Not sure what the L is.


Please read the blog post about getting help
https://www.privacyidea.org/getting-help/.

For professional services and consultancy regarding two factor
authentication please visit
https://netknights.it/en/leistungen/one-time-services/

In an enterprise environment you should get a SERVICE LEVEL AGREEMENT
which suites your needs for SECURITY, AVAILABILITY and LIABILITY:
https://netknights.it/en/leistungen/service-level-agreements/

You received this message because you are subscribed to the Google
Groups “privacyidea” group.
To unsubscribe from this group and stop receiving emails from it, send
an email to privacyidea+unsubscribe@googlegroups.com.
To post to this group, send email to privacyidea@googlegroups.com.
Visit this group at https://groups.google.com/group/privacyidea.
To view this discussion on the web visit
https://groups.google.com/d/msgid/privacyidea/03d9c34e-418d-4a52-9ad0-0ff9b1892b91%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


Cornelius Kölbel
@cornelinux
+49 151 2960 1417

NetKnights GmbH
http://www.netknights.it
Landgraf-Karl-Str. 19, 34131 Kassel, Germany
Tel: +49 561 3166797, Fax: +49 561 3166798

Amtsgericht Kassel, HRB 16405
Geschäftsführer: Cornelius Kölbel

signature.asc (836 Bytes)