mysql resolver user problem

[Harvey Chang]

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

[cornelius.koelbel]

As you are calling this table ssh user,   what are you trying to achieve?

How does your resolver an mapping look like?

Kind regards 

Cornelius 

Cornelius Kölbel 

+49 151 2960 1417

NetKnights GmbH

Http://NetKnights. It

+49 561 3166 797

-------- Ursprüngliche Nachricht --------

Von: Harvey Chang <harve...@gmail.com>

Datum: 04.07.16 11:01 (GMT+01:00)

An: privacyidea <priva...@googlegroups.com>

Betreff: [privacyidea] mysql resolver user problem

--
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...@googlegroups.com.
To post to this group, send email to priva...@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/e7a2a50c-94a2-488a-a3b4-846467478de6%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Harvey Chang]

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

[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.

[Cornelius Kölbel]

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
Cornelius

> --
> 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...@googlegroups.com.
> To post to this group, send email to priva...@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
corneliu...@netknights.it

+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