SQLResolver does not update user information

Hello list,

as documented i added a SQL-Resolver with

./bin/pi-manage resolver create_internal myfirstresolver

and i am able to add users, enroll tokens and use them.

But if i try to change user information, it isn’t saved to the database.
Acually there ist no activity at all in my MariaDB query log while saving
changes but there is, when i’m add new users of cause.
The SQL resolver is marked as editable (otherwise i can’t add users) and
i’m using privacyIDEA 2.11.1 installed via pip on CentOS 7.

Any idea?

Kind regards,
Sönke

Can the attribute mapping be wrong, if i can add users at first place?
Hi Sönke,

Yes it could.
If you are adding a user just with a username privacyidea just creates
the object with the username.
But if you set - e.g.- the surname later and you have a wrong mapping
for the surname, then it would fail to update this column.

Can you please send your DB schema and your mapping?

Thanks a lot
CorneliusAm Donnerstag, den 28.04.2016, 07:32 -0700 schrieb Sönke Martens:


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/17f286aa-c6f5-4d48-9903-205bc7e186a7%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)

Hello Cornelius,

i think, we could switch to german, but… :slight_smile:

If found warnings in my pi.log at loglevel 20 like

[2016-04-28 11:59:10,585][22742][139981455746880][WARNING][privacyidea.lib.
auditmodules.sqlaudit:57] No module named matplotlib

and copied some missing libs to
/opt/privacyidea/[lib,lib64]/python2.7/site-packages/ and after matplotlib,
pyparsing, numpy, pandas, dateutil and pytz.tzinfo there ar no more entries
in my log even at loglevel 50.

After restarting privacyidea i can add users sucessfully without any entry
in my pi.log.
Sadly changing values isn’t working so far without log entries too.

Can the attribute mapping be wrong, if i can add users at first place?

echo “select * from users_myfirstresolver;” | mysql -u privacyID3A -p
********** privacyidea

id username email password phone mobile surname
givenname description
4 Benutzer email@example.com {SSHA256}H3jlFM6ABr5TfUtuPQ1f8SS0C25ewFiKVSmGp0Iw1QcGykH1FfL4qYiesoUktQ1Q
Telefon Mobiltelefon Nachname Vorname Beschreibung

Kind regards,
Sönke

Hello Sönke,

the method SQLResolver:IdResolver.update_user should be called.

Can you please take a look in your privacyidea.log, if you see any error
or warning? A wrong attribute mapping can break the update.

Kind regards
CorneliusAm Donnerstag, den 28.04.2016, 05:42 -0700 schrieb Sönke Martens:

Hello list,

as documented i added a SQL-Resolver with

./bin/pi-manage resolver create_internal myfirstresolver

and i am able to add users, enroll tokens and use them.

But if i try to change user information, it isn’t saved to the
database. Acually there ist no activity at all in my MariaDB query log
while saving changes but there is, when i’m add new users of cause.
The SQL resolver is marked as editable (otherwise i can’t add users)
and i’m using privacyIDEA 2.11.1 installed via pip on CentOS 7.

Any idea?

Kind regards,
Sönke


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/10b873cc-3f55-4ef2-9623-2461f3a51cfe%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)

Looks good. Nothing jumps to my attention.
Which field are you trying to edit?

you might want to try to narrow down the problem by reducing the
attribute mapping in the resolver.

{“userid”: “id”, “username”: “username”, “surname”:“surname”}

and just change the surname.
Kind regards
CorneliusAm Freitag, den 29.04.2016, 04:38 -0700 schrieb Sönke Martens:

Hi Cornelius,

sorry, my fault.

[root@server ~]# echo “explain users_myfirstresolver;” | mysql -u
privacyID3A -p privacyidea
Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
username varchar(40) YES UNI NULL
email varchar(80) YES NULL
password varchar(255) YES NULL
phone varchar(40) YES NULL
mobile varchar(40) YES NULL
surname varchar(40) YES NULL
givenname varchar(40) YES NULL
description varchar(255) YES NULL

Sönke

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/b73c2735-9f9c-44db-93cd-0f92f9207a27%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)

Hi Cornelius,

sorry, my fault.

[root@server ~]# echo “explain users_myfirstresolver;” | mysql -u
privacyID3A -p privacyidea
Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
username varchar(40) YES UNI NULL
email varchar(80) YES NULL
password varchar(255) YES NULL
phone varchar(40) YES NULL
mobile varchar(40) YES NULL
surname varchar(40) YES NULL
givenname varchar(40) YES NULL
description varchar(255) YES NULL

Sönke

Hi Cornelius,

ok, here we go:

I added the SQL resolver on the commandline. The mapping is done
automatically, i think.

cd /opt/privacyidea/
source bin/activate
./bin/pi-manage resolver create_internal myfirstresolver

Inside MariaDB it looks like this:

[root@server ~]# echo “select * from resolver;” | mysql -u privacyID3A -p
privacyidea
id name rtype
6 myfirstresolver sqlresolver

[root@server ~]# echo “select * from resolverconfig where resolver_id = 6;”
| mysql -u privacyID3A -p privacyidea
id resolver_id Key Value Type Description
38 6 Database privacyidea string
39 6 Driver mysql string
40 6 Editable 1 int
42 6 Limit 500 int
37 6 Map {“userid”: “id”, “username”: “username”,
“email”:“email”, “password”: “password”, “phone”:“phone”,
“mobile”:“mobile”, “surname”:“surname”, “givenname”:“givenname”,
“description”: “description”} string
45 6 Password ******************************
password
41 6 Server localhost string
44 6 Table users_myfirstresolver string
43 6 User privacyID3A string

Here’s my pi.cfg

[root@server ~]# cat /etc/privacyidea/pi.cfg

The realm, where users are allowed to login as administrators

SUPERUSER_REALM = [‘super’, ‘administrators’]

Your database

SQLALCHEMY_DATABASE_URI =
‘mysql://privacyID3A:****************@localhost/privacyidea’

This is used to encrypt the auth_token

SECRET_KEY = ‘*************’

This is used to encrypt the admin passwords

PI_PEPPER = “***************”

This is used to encrypt the token data and token passwords

PI_ENCFILE = ‘/etc/privacyidea/enckey’

This is used to sign the audit log

PI_AUDIT_KEY_PRIVATE = '/etc/privacyidea/private.pem’
PI_AUDIT_KEY_PUBLIC = '/etc/privacyidea/public.pem’
PI_LOGFILE = '/var/log/privacyidea/privacyidea.log’
PI_LOGLEVEL = 50

PI_INIT_CHECK_HOOK = ‘your.module.function’

If i add a new user, all attributes are saved correctly to the database and
i can log in, enroll and use tokens.

Do you need anything else?

Best regards from Hamburg,
Sönke

Hi Sönke,

I am interested in the schema of the user table that is referenced by
the resolver.

“users_myfirstresolver”

Kind regards
CorneliusAm Freitag, den 29.04.2016, 04:04 -0700 schrieb Sönke Martens:

Hi Cornelius,

ok, here we go:

I added the SQL resolver on the commandline. The mapping is done
automatically, i think.

cd /opt/privacyidea/
source bin/activate
./bin/pi-manage resolver create_internal myfirstresolver

Inside MariaDB it looks like this:

[root@server ~]# echo “select * from resolver;” | mysql -u privacyID3A
-p privacyidea
id name rtype
6 myfirstresolver sqlresolver

[root@server ~]# echo “select * from resolverconfig where resolver_id
= 6;” | mysql -u privacyID3A -p privacyidea
id resolver_id Key Value Type Description
38 6 Database privacyidea string
39 6 Driver mysql string
40 6 Editable 1 int
42 6 Limit 500 int
37 6 Map {“userid”: “id”, “username”: “username”,
“email”:“email”, “password”: “password”, “phone”:“phone”,
“mobile”:“mobile”, “surname”:“surname”, “givenname”:“givenname”,
“description”: “description”} string
45 6 Password ******************************
password
41 6 Server localhost string
44 6 Table users_myfirstresolver string
43 6 User privacyID3A string

Here’s my pi.cfg

[root@server ~]# cat /etc/privacyidea/pi.cfg

The realm, where users are allowed to login as administrators

SUPERUSER_REALM = [‘super’, ‘administrators’]

Your database

SQLALCHEMY_DATABASE_URI =
‘mysql://privacyID3A:****************@localhost/privacyidea’

This is used to encrypt the auth_token

SECRET_KEY = ‘*************’

This is used to encrypt the admin passwords

PI_PEPPER = “***************”

This is used to encrypt the token data and token passwords

PI_ENCFILE = ‘/etc/privacyidea/enckey’

This is used to sign the audit log

PI_AUDIT_KEY_PRIVATE = ‘/etc/privacyidea/private.pem’
PI_AUDIT_KEY_PUBLIC = ‘/etc/privacyidea/public.pem’
PI_LOGFILE = ‘/var/log/privacyidea/privacyidea.log’
PI_LOGLEVEL = 50

PI_INIT_CHECK_HOOK = ‘your.module.function’

If i add a new user, all attributes are saved correctly to the
database and i can log in, enroll and use tokens.

Do you need anything else?

Best regards from Hamburg,
Sönke

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/59441f84-f248-43d5-bd93-92e761439d7d%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)