SQLResolver does not update user information

[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

[Cornelius Kölbel]

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

[Sönke Martens]

Hello Cornelius,

i think, we could switch to german, but... :)

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

[Cornelius Kölbel]

Am Donnerstag, den 28.04.2016, 07:32 -0700 schrieb Sönke Martens:

>
> 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
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/17f286aa-c6f5-4d48-9903-205bc7e186a7%40googlegroups.com.

[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

[Cornelius Kölbel]

Hi Sönke,

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

"users_myfirstresolver"

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/59441f84-f248-43d5-bd93-92e761439d7d%40googlegroups.com.

[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

[Cornelius Kölbel]

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
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/b73c2735-9f9c-44db-93cd-0f92f9207a27%40googlegroups.com.