Bug in PrivacyID3A

Hi Cornelius,

Thanks for all your help lately!

Wanted to let you know of a bug we ran into.

If you attach a token to a machine twice, then try to detach it, you get
this error:

(IntegrityError) (1451, ‘Cannot delete or update a parent row: a foreign
key constraint fails (pi.machinetokenoptions, CONSTRAINT
machinetokenoptions_ibfk_1 FOREIGN KEY (machinetoken_id) REFERENCES
machinetoken (id))’) ‘DELETE FROM machinetoken WHERE
machinetoken.token_id = %s AND machinetoken.machine_id = %s AND
machinetoken.machineresolver_id = %s AND machinetoken.application = %s’
(15L, ‘CN=DWSLBASECP303,OU=Linux,OU=Servers
NonProd,OU=Cloud,DC=xxxxx,DC=com’, 1L, ‘ssh’)

This error occurred because of mistakes we made when we were first
implementing the APIs (we use vRA and use Rest exclusively to add tokens
and manage machines) - so its not something we expect to happen again, but
that still leaves us with users that are permanently broken… and I have
to imagine some other fool is going to make this mistake at some point in
the future, it’d be best if the UI can just detach these tokens properly.

We are detaching tokens via API… I just checked my sample code and it
appears I’m just deleting the token… which apparently works… except
when this issue is being experienced.

I’m not quite certain how we ran into it either, it must’ve been in the
early days of testing the REST API, we replayed the request to attach the
token to the machine.

Will start looking into mySQL to fix this for us. You should either
disallow the attachment of a second identical token, or fix the detach
button, however…On Wednesday, November 4, 2015 at 11:39:08 AM UTC-5, Cornelinux K wrote:

Hi Jim,

I do not understand how you ran into this issue in the first place.

You detached the token via the API? How?

As a last resort - at the moment - for your “broken users” you can still
modify the database tables machinetoken and machinetokenoptions on the
DB level.

(machinetoken links to the token table, the token table is connected to
the user)

Kind regards
Cornelius

Am Mittwoch, den 04.11.2015, 11:29 -0500 schrieb James McCracken:

P.S. Our use case is to create and manage the ssh keys on behalf of
our end users (Windows devs find SSH confusing. It doesn’t help that
ubuntu doesn’t accept puttygen public keys) - and if they request a
new ssh key when they already have one, we want to go through
privacyidea, detach the current ssh token from any machines, delete
the old token, create the new token, and attach it to the machines the
old token had access to.

On Wed, Nov 4, 2015 at 11:27 AM, James McCracken <merl...@gmail.com <javascript:>> wrote:
Hi Cornelius,

    Thanks for all your help lately! 
    
    
    Wanted to let you know of a bug we ran into. 
    
    
    If you attach a token to a machine twice, then try to detach 
    it, you get this error: 
    
    
    (IntegrityError) (1451, 'Cannot delete or update a parent row: 
    a foreign key constraint fails (`pi`.`machinetokenoptions`, 
    CONSTRAINT `machinetokenoptions_ibfk_1` FOREIGN KEY 
    (`machinetoken_id`) REFERENCES `machinetoken` (`id`))') 
    'DELETE FROM machinetoken WHERE machinetoken.token_id = %s AND 
    machinetoken.machine_id = %s AND 
    machinetoken.machineresolver_id = %s AND 
    machinetoken.application = %s' (15L, 
    'CN=DWSLBASECP303,OU=Linux,OU=Servers 
    NonProd,OU=Cloud,DC=xxxxx,DC=com', 1L, 'ssh') 
    
    
    
    This error occurred because of mistakes we made when we were 
    first implementing the APIs (we use vRA and use Rest 
    exclusively to add tokens and manage machines) - so its not 
    something we expect to happen again, but that still leaves us 
    with users that are permanently broken... and I have to 
    imagine some other fool is going to make this mistake at some 
    point in the future, it'd be best if the UI can just detach 
    these tokens properly. 


Cornelius Kölbel
corneliu…@netknights.it <javascript:>
+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

You are of course right.
Till know the logic was “to complicated”.

We would have to distinguish between major and minor options.
Or just avoid attaching tokens where “all options” are the same.

But this also would sometimes not be the expected behaviour…Am Mittwoch, den 04.11.2015, 15:23 -0500 schrieb James McCracken:

I know and agree that sometimes it makes sense to allow duplicate
tokens to be attached to a machine. If my options are identical, then
that doesn’t necessarily make as much sense…

On Wed, Nov 4, 2015 at 3:22 PM, Cornelius Kölbel <@cornelinux> wrote:
In some cases adding the same token a 2nd time makes sense.
You can add the same token to the machine with other options.

    In case of the ssh token you can use a different user.
    Attaching the same ssh key on the same machine to different
    users.
    
    
    There is already an issue on github. 
    I will look into this.
    
    
    Kind regards 
    Cornelius 
    
    
    
    
    
    
    Cornelius Kölbel
    Cornelius.koelbel@netknights.it
    +49 151 2960 1417
    
    
    NetKnights GmbH
    http://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
    
    
    -------- Ursprüngliche Nachricht --------
    Von: James McCracken <merlinjim@gmail.com> 
    Datum: 04.11.2015 19:34 (GMT+01:00) 
    An: privacyidea <privacyidea@googlegroups.com> 
    Betreff: Re: Bug in PrivacyID3A 
    
    We are detaching tokens via API... I just checked my sample
    code and it appears I'm just deleting the token... which
    apparently works... except when this issue is being
    experienced.
    
    
    I'm not quite certain how we ran into it either, it must've
    been in the early days of testing the REST API, we replayed
    the request to attach the token to the machine.
    
    
    Will start looking into mySQL to fix this for us.  You should
    either disallow the attachment of a second identical token, or
    fix the detach button, however...
    
    On Wednesday, November 4, 2015 at 11:39:08 AM UTC-5, Cornelinux K wrote:
            Hi Jim, 
            
            I do not understand how you ran into this issue in the
            first place. 
            
            You detached the token via the API? How? 
            
            As a last resort - at the moment - for your "broken
            users" you can still 
            modify the database tables machinetoken and
            machinetokenoptions on the 
            DB level. 
            
            (machinetoken links to the token table, the token
            table is connected to 
            the user) 
            
            Kind regards 
            Cornelius 
            
            Am Mittwoch, den 04.11.2015, 11:29 -0500 schrieb James
            McCracken: 
            > P.S. Our use case is to create and manage the ssh
            keys on behalf of 
            > our end users (Windows devs find SSH confusing.  It
            doesn't help that 
            > ubuntu doesn't accept puttygen public keys) - and if
            they request a 
            > new ssh key when they already have one, we want to
            go through 
            > privacyidea, detach the current ssh token from any
            machines, delete 
            > the old token, create the new token, and attach it
            to the machines the 
            > old token had access to. 
            > 
            > On Wed, Nov 4, 2015 at 11:27 AM, James McCracken <merl...@gmail.com>  wrote: 
            >         Hi Cornelius, 
            >         
            >         
            >         Thanks for all your help lately! 
            >         
            >         
            >         Wanted to let you know of a bug we ran
            into. 
            >         
            >         
            >         If you attach a token to a machine twice,
            then try to detach 
            >         it, you get this error: 
            >         
            >         
            >         (IntegrityError) (1451, 'Cannot delete or
            update a parent row: 
            >         a foreign key constraint fails
            (`pi`.`machinetokenoptions`, 
            >         CONSTRAINT `machinetokenoptions_ibfk_1`
            FOREIGN KEY 
            >         (`machinetoken_id`) REFERENCES
            `machinetoken` (`id`))') 
            >         'DELETE FROM machinetoken WHERE
            machinetoken.token_id = %s AND 
            >         machinetoken.machine_id = %s AND 
            >         machinetoken.machineresolver_id = %s AND 
            >         machinetoken.application = %s' (15L, 
            >         'CN=DWSLBASECP303,OU=Linux,OU=Servers 
            >         NonProd,OU=Cloud,DC=xxxxx,DC=com', 1L,
            'ssh') 
            >         
            >         
            >         
            >         This error occurred because of mistakes we
            made when we were 
            >         first implementing the APIs (we use vRA and
            use Rest 
            >         exclusively to add tokens and manage
            machines) - so its not 
            >         something we expect to happen again, but
            that still leaves us 
            >         with users that are permanently broken...
            and I have to 
            >         imagine some other fool is going to make
            this mistake at some 
            >         point in the future, it'd be best if the UI
            can just detach 
            >         these tokens properly. 
            > 
            > 
            -- 
            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 


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)

P.S. Our use case is to create and manage the ssh keys on behalf of our end
users (Windows devs find SSH confusing. It doesn’t help that ubuntu
doesn’t accept puttygen public keys) - and if they request a new ssh key
when they already have one, we want to go through privacyidea, detach the
current ssh token from any machines, delete the old token, create the new
token, and attach it to the machines the old token had access to.On Wed, Nov 4, 2015 at 11:27 AM, James McCracken <@James_McCracken> wrote:

Hi Cornelius,

Thanks for all your help lately!

Wanted to let you know of a bug we ran into.

If you attach a token to a machine twice, then try to detach it, you get
this error:

(IntegrityError) (1451, ‘Cannot delete or update a parent row: a foreign
key constraint fails (pi.machinetokenoptions, CONSTRAINT
machinetokenoptions_ibfk_1 FOREIGN KEY (machinetoken_id) REFERENCES
machinetoken (id))’) ‘DELETE FROM machinetoken WHERE
machinetoken.token_id = %s AND machinetoken.machine_id = %s AND
machinetoken.machineresolver_id = %s AND machinetoken.application = %s’
(15L, ‘CN=DWSLBASECP303,OU=Linux,OU=Servers
NonProd,OU=Cloud,DC=xxxxx,DC=com’, 1L, ‘ssh’)

This error occurred because of mistakes we made when we were first
implementing the APIs (we use vRA and use Rest exclusively to add tokens
and manage machines) - so its not something we expect to happen again, but
that still leaves us with users that are permanently broken… and I have
to imagine some other fool is going to make this mistake at some point in
the future, it’d be best if the UI can just detach these tokens properly.


You received this message because you are subscribed to a topic in the
Google Groups “privacyidea” group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/privacyidea/tHoNuFV_d1o/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
privacyidea+unsubscribe@googlegroups.com.
To post to this group, send email to privacyidea@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/privacyidea/8dcc48cd-91e0-4266-9514-28cc3cf4e37f%40googlegroups.com
https://groups.google.com/d/msgid/privacyidea/8dcc48cd-91e0-4266-9514-28cc3cf4e37f%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

Hi Jim,

I do not understand how you ran into this issue in the first place.

You detached the token via the API? How?

As a last resort - at the moment - for your “broken users” you can still
modify the database tables machinetoken and machinetokenoptions on the
DB level.

(machinetoken links to the token table, the token table is connected to
the user)

Kind regards
CorneliusAm Mittwoch, den 04.11.2015, 11:29 -0500 schrieb James McCracken:

P.S. Our use case is to create and manage the ssh keys on behalf of
our end users (Windows devs find SSH confusing. It doesn’t help that
ubuntu doesn’t accept puttygen public keys) - and if they request a
new ssh key when they already have one, we want to go through
privacyidea, detach the current ssh token from any machines, delete
the old token, create the new token, and attach it to the machines the
old token had access to.

On Wed, Nov 4, 2015 at 11:27 AM, James McCracken merlinjim@gmail.com wrote:
Hi Cornelius,

    Thanks for all your help lately!
    
    
    Wanted to let you know of a bug we ran into.
    
    
    If you attach a token to a machine twice, then try to detach
    it, you get this error:
    
    
    (IntegrityError) (1451, 'Cannot delete or update a parent row:
    a foreign key constraint fails (`pi`.`machinetokenoptions`,
    CONSTRAINT `machinetokenoptions_ibfk_1` FOREIGN KEY
    (`machinetoken_id`) REFERENCES `machinetoken` (`id`))')
    'DELETE FROM machinetoken WHERE machinetoken.token_id = %s AND
    machinetoken.machine_id = %s AND
    machinetoken.machineresolver_id = %s AND
    machinetoken.application = %s' (15L,
    'CN=DWSLBASECP303,OU=Linux,OU=Servers
    NonProd,OU=Cloud,DC=xxxxx,DC=com', 1L, 'ssh')
    
    
    
    This error occurred because of mistakes we made when we were
    first implementing the APIs (we use vRA and use Rest
    exclusively to add tokens and manage machines) - so its not
    something we expect to happen again, but that still leaves us
    with users that are permanently broken... and I have to
    imagine some other fool is going to make this mistake at some
    point in the future, it'd be best if the UI can just detach
    these tokens properly.


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)