gmane.comp.security.openxpki.devel

paused workflows,proc state and more: first implementation

Dieter Siebeck — 2012-05-18T13:20:08

Hi,

my name is Dieter Siebeck and I am quite new to the OpenXPKI project. I 
am working with Oli Welter since nearly 3 months now, mainly on his 
idea/draft of "paused workflows" (see his mail to the list from 2012-02-14).

Now I have published my first implementation of this feature. You can 
see and download it here:

https://github.com/dsiebeck/openxpki/tree/feature%2Fworkflow

(this branch has been rebased on the devel branch 22 days ago.)

POD  Documentation  of most of the new methods is still missing, but I 
wanted to publish the current state, to give other interested people the 
opportunity to see and maybe correct the big picture.
Also missing are test files/scripts. I would appreciate some 
recommendation, which place to look for the most up2date state-of-the 
art testing files for this.

Now the idea of the implementation: basic need was to gain relatively 
low-level control to the execution of workflows and activities. To 
achieve this, the best (and maybe only) way seemed, to install an "own"

Re: exploding load after search queries

Michael Bell — 2012-05-08T11:19:36

Hi Oli,

Am 05.05.2012 08:08, schrieb Oliver Welter:

I implemented a fix but this fix needs a review by Martin because it
changes the database schema.

https://github.com/bellmich/openxpki/compare/develop...feature%2Fdbi_indexing_lob_columns

Best regards

Michael

Re: Removal of obsoleted database entries

Martin Bartosch — 2012-05-06T14:45:47

Hi Joachim,


We are having a similar issue with lots of SCEP requests. I have documented our solution onhttp://wiki.openxpki.org/index.php/Best_Practices/Housekeeping/Cleanup_Superfluous_Workflows


Yep, something to address in the future :)

cu

Martin


------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and 
threat landscape has changed and how IT managers can respond. Discussions 
will include endpoint security, mobile security and the latest in malware 
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/

Re: exploding load after search queries

Joachim Astel — 2012-05-05T12:40:12

I second.

[Cert Attributes]

Ah, i see.

Greetings
   -Achim

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and 
threat landscape has changed and how IT managers can respond. Discussions 
will include endpoint security, mobile security and the latest in malware 
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/

Re: Removal of obsoleted database entries

Oliver Welter — 2012-05-05T12:33:39

Hi Achim

Am 03.05.2012 12:50, schrieb Joachim Astel:

At least not on the "official" sources - but I guess that you can solve 
most of the "garbage collection" with proper relations and triggers in 
mysql.

Oli

Re: exploding load after search queries

Oliver Welter — 2012-05-05T12:31:11

Hi Achim,


Personally, I see the context as an opaque datastore and dislike the 
idea of doing any search queries on the data. I know that we do this at 
the moment in some places but I would like to see this "searchable 
context data" in a dedicated mapping/index/search - whatever you would 
name it - table. In return, we would not need to index the value portion 
of the context table, as it is never used to search within.


If you use subject alternative names or forced validity, this info is 
recorded here as a 1:n relation.

Oli

Re: exploding load after search queries

Joachim Astel — 2012-05-05T10:31:37

Hi Oliver, Martin,

my MySQL implementation allowed the following index creations:

CREATE INDEX wf_state_state on workflow (workflow_state);
CREATE INDEX cert_realm_index on certificate (pki_realm);
CREATE INDEX csr_profile_index on csr (profile(200));
CREATE INDEX csr_role_index on csr (role(200));
CREATE INDEX cert_csrid_index on certificate (req_key);
CREATE INDEX wf_realm_index on workflow (pki_realm);
CREATE INDEX csr_subject_index on csr (subject);
CREATE INDEX cert_role_index on certificate (role(200));
CREATE INDEX wf_hist_wfserial_index on workflow_history (workflow_id);
CREATE INDEX wf_context_key_index on workflow_context (workflow_context_key);
CREATE INDEX wf_type_index on workflow (workflow_type);
CREATE INDEX cert_subject_index on certificate (subject);
CREATE INDEX cert_identifier_index on certificate (identifier);
CREATE INDEX cert_status_index on certificate (status(200));
CREATE INDEX cert_attributes_key_index on certificate_attributes attribute_contentkey);

As you can see i respected yo

Re: exploding load after search queries

Oliver Welter — 2012-05-05T06:08:43

Hi Achim,

On 04.05.2012 22:47, Joachim Astel wrote:
The fast way - create the indexes on the first 200 chars (mysql offers 
that option). I assume that it is a safe assumption, that index searches 
are always done on scalars which wont be longer than that.

It should be also safe to shrink the role/profile/status fields to 
something like tinytext or varchar 255 - the default roles and status 
names are around are around 10 chars long, the longest profile name is 
49 chars, as long as you did not choose to make very bulky names, that 
should do the trick.

As the problem with the context_value column is even worse on oracle, a proper solution is on the to-do list - so if anybody has some better ideas, just shoot!

Oli

Re: exploding load after search queries

Joachim Astel — 2012-05-04T20:47:35

Hi OpenXPKI-folks,


I've tried this with MySQL as backend database, and ran into the
following index creation problems:

CREATE INDEX csr_profile_index on csr (profile);
-> ERROR 1170 (42000) at line 1: BLOB/TEXT column 'profile' used in key specification without a key length
(profile is a "text" field without length restriction.)

CREATE INDEX csr_role_index on csr (role);
-> ERROR 1170 (42000) at line 1: BLOB/TEXT column 'role' used in key specification without a key length
(role is a "text" field without length restriction.)

CREATE INDEX wf_context_value_index on workflow_context (workflow_context_value);
-> ERROR 1170 (42000) at line 1: BLOB/TEXT column 'workflow_context_value' used in key specification without a key length
(workflow_context_value is a "text" field without length restriction.)

CREATE INDEX cert_role_index on certificate (role);
-> ERROR 1170 (42000) at line 1: BLOB/TEXT column 'role' used in key specification without a key length
(role is a "text" field without length restriction.)

C

Removal of obsoleted database entries

Joachim Astel — 2012-05-03T10:50:55

We're doing some database cleanup within our database via cronjob for
our OpenXPKI databases, especially for the audittrail log:

DELETE FROM audittrail WHERE DATEDIFF(NOW(),CONVERT(logtimestamp,DATETIME))>365

Unfortunately, generated CRL (via openxpkicmd) are growing within our
workflow table. We would like to remove old workflows with workflow_type
CRL_ISSUANCE, but backend database entries which are associated with it
would also have to be deleted -- to avoid database garbage.
Are there already scripts around in other projects addressing this issue?

Greetings
    -Achim

PS: I've already deleted workflows with 
"DELETE FROM WORKFLOW WHERE WORKFLOW_TYPE='I18N_OPENXPKI_WF_TYPE_CRL_ISSUANCE'"
I suppose a garbage collector would be good for this problem too. :-)

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and 
threat landscape has changed and how IT managers can respond. Discuss

Re: SQL DYNAMIC patch incomplete ?

Michael Bell — 2012-05-03T08:39:58

Hi,

after a small private discussion Oliver fixed my bug and implemented
support for the old behaviour ('key' => 'value'). If you use the old
behaviour then you get a warning because you use a deprecated part of
the code.

The old behaviour is used to avoid any breaking of the code.
Additionally we think that there should only be one correct/right way.
So I would like to remove the old behaviour in terms of clarity and
simplicity. Oliver immediately discovered the backside of my straight
forward approach. Finally we agreed in a compromise - support for the
old behaviour with a big warning.

Best regards

Michael

Am 26.04.2012 19:24, schrieb Oliver Welter:

Re: SQL DYNAMIC patch incomplete ?

Michael Bell — 2012-05-03T08:38:29

Am 26.04.2012 22:45, schrieb Michael Bell:

This was in fact the problem (plus an additional bug in the new code
from Oliver).


The patch was incomplete.

Best regards

Michael

Re: SQL DYNAMIC patch incomplete ?

Oliver Welter — 2012-04-27T10:37:08

Hi Micha,


On 26.04.2012 22:45, Michael Bell wrote:
That should be fixed with 710da93cd.

I tried to get a "fallback to old syntax" into the code - might you 
please do me a favour and have a look on that.
Its commit 23717d68, which is currently only in my private repo.

Oli

Re: SQL DYNAMIC patch incomplete ?

Michael Bell — 2012-04-26T20:45:37

Hi Oli,

Am 26.04.2012 19:24, schrieb Oliver Welter:

DATAPOOL_KEY is still in wrong format.

Best regards, Michael

P.S. Yes, it looks like the patch was incomplete.

SQL DYNAMIC patch incomplete ?

Oliver Welter — 2012-04-26T17:24:55

Hi Micha,

I am running into some problems using the datapool after merging your 
dynamic-patch.

my $msg = CTX('api')->get_data_pool_entry( {
   KEY => $chipid ,
   NAMESPACE => 'smartcard.smartchipid'
} );

results in an exception (I added ref and column)

'LABEL' => 
'I18N_OPENXPKI_SERVER_DBI_SQL_SELECT_DYNAMIC_PARAM_NO_HASH_REFERENCE',
PARAMS' => {
     '__REF__' => '',
    '__COLUMN__' => 'DATAPOOL_KEY',
    '__TABLE__' => 'DATAPOOL'
}

I added some more debug in SQL.pm lin 970

##! 1: ' DYNAMIC arg ' . Dumper $args->{DYNAMIC}

OpenXPKI::Server::DBI::SQL::select (line 970):  DYNAMIC arg $VAR1 = {
           'DATAPOOL_KEY' => 'chipid1234',
           'NAMESPACE' => 'smartcard.smartchipid',
           'PKI_REALM' => 'I18N_OPENXPKI_DEPLOYMENT_TEST_DUMMY_CA'
         };


I found the  {value => xx } brackets are missing in the statement in 
Server/API/Object.pm, Line 604ff , but even after adding them, the error 
remains the samem, having this debug output:

OpenXPKI::Server::DBI::SQL::select (line 970):  D

Re: Git and OpenXPKI … a short primer for people new to git and GitHub

Andreas Leibl — 2012-04-15T17:02:16

Hi Michael,

This is not a GitHub thing ... it's the git way of doing (somewhat) centralised development with a de-centralised version control system.

To git every repository is equal. If we assign more importance to one repo(sitory) than another it is by convention only. Period.

Now if a couple of people work together they could conceivably all have their own version of the product and only pull changes from others as they see fit. Git would support that 100%. The problem is that, as the product evolves (grows), it takes more and more effort to keep in sync for the features/fixes you want.

In real life, it's often more practical to agree on one source repo as a reference ... and then make changes relative to that. You can still have your own version (branch, fork, whatever) but you would be moving alongside this agreed central reference. Take Linux as an example. The rep Linus Torvalds maintains is, from a git point of view, no better than anybody else's. However, since most people agree he's doing a gre

Re: Git and OpenXPKI … a short primer for people new to git and GitHub

Michael Bell — 2012-04-15T15:58:04

Hi Andreas,

github.com uses a remote repo called upstream. The primer document does
not describe the usage of such a repository.

So if I understand the beginner documentation on github.com correctly
then the changes to upstream are never visible in the forked repo. So I
think that the described developer workflow brings the forked repo
permanently out of sync with openxpki/openxpki.

Is my understanding of git correct here?

Best regards

Michael


Am 02.04.2012 10:43, schrieb Andreas Leibl:

Re: Database layer: indices and schema

Martin Bartosch — 2012-04-13T09:57:14

Hi Michael,


I guess that would most probably be us... :)

I will need some time for actually testing the submission - I am currently working on a tight schedule to meet my project deadline. ETA for real feedback on this: early May.

I read the commits in the feature branch and I noticed that this (naturally) involves a schema change. This is not trivial on our side and also requires a migration script that adds the computed hashes for already existing entries. I need to think about this for a while...

cu

Martin


------------------------------------------------------------------------------
For Developers, A Lot Can Happen In A Second.
Boundary is the first to Know...and Tell You.
Monitor Your Applications in Ultra-Fine Resolution. Try it FREE!
http://p.sf.net/sfu/Boundary-d2dvs2

Re: Database layer: indices and schema

Michael Bell — 2012-04-12T20:56:42

Hi,

I had some time in the evening and implemented this solution. It is
available via github. The branch is feature/dbi_indexing_lob_columns.

https://github.com/bellmich/openxpki/commits/feature/dbi_indexing_lob_columns

Now I'm looking for somebody who can and is willing to test it with Oracle.

Any volunteers?

Best regards, Michael


Am 12.04.2012 10:42, schrieb Michael Bell:

Re: Management of pull requests

Martin Bartosch — 2012-04-12T15:38:03

Hi Michael,


you are very right, a review by a second developer adds a nice safety net. We should follow this policy.


I am currently quite busy with my work here. I'll see if I can merge it in tomorrow, hopefully together with Joachim's plentiful submissions (thanks, btw!).

cheers

Martin






------------------------------------------------------------------------------
For Developers, A Lot Can Happen In A Second.
Boundary is the first to Know...and Tell You.
Monitor Your Applications in Ultra-Fine Resolution. Try it FREE!
http://p.sf.net/sfu/Boundary-d2dvs2

Management of pull requests

Michael Bell — 2012-04-12T15:24:33

Hi,

could somebody please review my last pull request? I think it is
generally a good idea to ask for a review and do not merge my own stuff.

So any volunteers?

Best regards, Michael