PostgreSQL

James_McCracken · October 29, 2015, 3:29pm

Hello,

My company recently decided to use privacyidea and we are getting ready to
roll out our first production server instance.

Our database team has much better support for postgres than it does for
MySQL, so we are looking to use postgres.

I found the sqlalchemy driver for it
(http://python.projects.pgfoundry.org/) and the format for the connection
string
(http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#module-sqlalchemy.dialects.postgresql.pypostgresql)
as well as where to configure privacyidea with it
(http://privacyidea.readthedocs.org/en/latest/installation/system/inifile.html)

I’m kind of assuming I just follow these instructions and somehow copy
the mysql database into postgres (no clue how to accomplish that step) -
has anyone else gone done this road before? Any advice or gotchas I should
look out for?

Thanks,
Jim

cornelinux · October 29, 2015, 3:45pm

PS:
i.e. you do not need to copy the mysql database.
Just create the postgres DB anew.Am Donnerstag, den 29.10.2015, 16:44 +0100 schrieb Cornelius Kölbel:

Hi Jim,

I think I used psycopg2 a while ago.
PostgreSQL — SQLAlchemy 2.0 Documentation
You need to install the python module.
Depending on your privacyIDEA installation (pypi or deb) you can install
it like

pip install psycopg2

Adapt the connect string in the pi.cfg file and

create the database and

the database user.

Now you can create the encryption key and setup the database
(see 2.1. Python Package Index — privacyIDEA 3.8 documentation)

pi-manage.py create_enckey
pi-manage.py create_audit_keys
pi-manage.py createdb
pi-manage.py admin add admin

Kind regards
Cornelius

Am Donnerstag, den 29.10.2015, 08:29 -0700 schrieb James McCracken:

Hello,

My company recently decided to use privacyidea and we are getting
ready to roll out our first production server instance.

Our database team has much better support for postgres than it does
for MySQL, so we are looking to use postgres.

I found the sqlalchemy driver for it
(http://python.projects.pgfoundry.org/) and the format for the
connection string
(PostgreSQL — SQLAlchemy 2.0 Documentation) as well as where to configure privacyidea with it (2.5. The Config File — privacyIDEA 3.8 documentation) - I’m kind of assuming I just follow these instructions and somehow copy the mysql database into postgres (no clue how to accomplish that step) - has anyone else gone done this road before? Any advice or gotchas I should look out for?

Thanks,
Jim

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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/privacyidea/15de4c07-b730-46c8-ab39-10ff633809d6%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

–
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)

cornelinux · October 29, 2015, 3:44pm

Hi Jim,

I think I used psycopg2 a while ago.
http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#module-sqlalchemy.dialects.postgresql.psycopg2
You need to install the python module.
Depending on your privacyIDEA installation (pypi or deb) you can install
it like

pip install psycopg2

Adapt the connect string in the pi.cfg file and
create the database and
the database user.

Now you can create the encryption key and setup the database
(see 2.1. Python Package Index — privacyIDEA 3.8 documentation)

pi-manage.py create_enckey
pi-manage.py create_audit_keys
pi-manage.py createdb
pi-manage.py admin add admin

Kind regards
CorneliusAm Donnerstag, den 29.10.2015, 08:29 -0700 schrieb James McCracken:

Hello,

My company recently decided to use privacyidea and we are getting
ready to roll out our first production server instance.

Our database team has much better support for postgres than it does
for MySQL, so we are looking to use postgres.

I found the sqlalchemy driver for it
(http://python.projects.pgfoundry.org/) and the format for the
connection string
(PostgreSQL — SQLAlchemy 2.0 Documentation) as well as where to configure privacyidea with it (2.5. The Config File — privacyIDEA 3.8 documentation) - I’m kind of assuming I just follow these instructions and somehow copy the mysql database into postgres (no clue how to accomplish that step) - has anyone else gone done this road before? Any advice or gotchas I should look out for?

Thanks,
Jim

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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/privacyidea/15de4c07-b730-46c8-ab39-10ff633809d6%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)

cornelinux · December 2, 2015, 11:06pm

Hello Rick,

do you get any error message in the privacyidea.log?

Kind regards
CorneliusAm Mittwoch, den 02.12.2015, 14:07 -0800 schrieb RickP:

Cornelius,

we have the postgres db created and working, but are having issues
with the connections timing out every 60 seconds, so everytime a
web/api server gets a request to validate an ssh key the database
connections have shutdown and it seems to take 5-7 seconds to
establish the connection, so logins on systems are really slow.

Any input on the URI line or methods to extend the timeouts or create
pools in sqlalchemy?

here is our current connection URI in the /etc/privacyidea/pi.cfg file

SQLALCHEMY_DATABASE_URI = ‘postgresql
+psycopg2://svcprivid:somepasswd@pwslpidpg03.domain.com:5432/privacyidea’

–
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/privacyidea/702247b1-730e-4f3f-a054-874d5b12f647%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)

RickP · December 2, 2015, 10:07pm

Cornelius,

we have the postgres db created and working, but are having issues with the
connections timing out every 60 seconds, so everytime a web/api server gets
a request to validate an ssh key the database connections have shutdown and
it seems to take 5-7 seconds to establish the connection, so logins on
systems are really slow.

Any input on the URI line or methods to extend the timeouts or create pools
in sqlalchemy?

here is our current connection URI in the /etc/privacyidea/pi.cfg file

SQLALCHEMY_DATABASE_URI =
‘postgresql+psycopg2://svcprivid:somepasswd@pwslpidpg03.domain.com:5432/privacyidea’

RickP · December 3, 2015, 6:55pm

We were thinking the same things… but we are not sure how to pass any of
those options in the pi.cfg URI line successfully, any hints on that??

RickP · December 3, 2015, 2:07pm

there are no errors in the logs, it works ‘ok’ but it closes the connection
to the PG database within the minute so the next authentication somewhere
has to reconnect the web/api system to the PG instance and significantly
delays key retrieval

Our questions are from the pi.cfg file URI is there methods to pass any of
the connection timeout or pooling parameters

Our current URI line
SQLALCHEMY_DATABASE_URI = ‘postgresql+psycopg2://
svcprivid:somepasswd@pwslpidpg03.domain.com:5432/privacyidea’

SQLALCHEMY options
http://docs.sqlalchemy.org/en/latest/core/engines.html#postgresql

cornelinux · December 3, 2015, 8:04pm

You do not need to pass it in the URI.

You should be able to pass it like this in the pi.cfg:

SQLALCHEMY_DATABASE_URI = ‘mysql://pi:P4yvb3d1Thw_@localhost/pi’
SQLALCHEMY_POOL_SIZE = 20
SQLALCHEMY_POOL_TIMEOUT = 120

Kind regards
CorneliusAm Donnerstag, den 03.12.2015, 10:55 -0800 schrieb RickP:

We were thinking the same things… but we are not sure how to pass
any of those options in the pi.cfg URI line successfully, any hints on
that??

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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/privacyidea/b38e956f-aa64-474d-bdec-defbaf1c61eb%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)

cornelinux · December 8, 2015, 5:00pm

Hello Rick,

I would have tried some top level test variations to narrow down the
behavious:

Use another database type like mysql instead of postgres
(if it is due to the DB type)
Use the other database type on the same database machine
(if it is due to the machine/DNS whatsoever)
Use another postgresql python driver!
(I was using psycopg2, see
PostgreSQL — SQLAlchemy 2.0 Documentation)

We have just realized that the delay isnt in the initial packet that
would be establishing the DB connection, the delay is the packet prior
to the first packet directed at the DB instance,

So which packet is it?

we thought maybe DNS resolution but that is working fine instant
resolutions, does that give you any clue as to what the app might be
doing before initiating the DB connection?

…443 traffic
10:43:31.823697 IP 10.1.25.133.443 > 192.168.60.103.37106: Flags [.],
ack 750, win 243, options [nop,nop,TS val 528643261 ecr 152553203],
length 0

10.1.25.133 is your PI server, 10.1.24.131 the DB?
So it looks like privacyIDEA waits 5 secs before contacting the DB?
Is there anything in the privacyidea.log in DEBUG mode?
Usually there is tons of data in the debug mode.

Kind regards
CorneliusAm Dienstag, den 08.12.2015, 08:44 -0800 schrieb RickP:

delay is before the first packet directed at port 5432 on the Postgres
host

10:43:36.880382 IP 10.1.25.133.51043 > 10.1.25.131.5432: Flags [S],
seq 1816583097, win 29200, options [mss 1460,sackOK,TS val 528644525
ecr 0,nop,wscale 7], length 0
…DB 5432 traffic

–
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/privacyidea/e870152b-3baf-45f6-a889-9993bb243212%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)

RickP · December 8, 2015, 6:41pm

in a basic python script to connect to the DB we effeminately see the delay
if using hostname, and do not see the delay at all if using IP, leads one
to think something bad in dns, but nslookups immediately return the
hostname lookup, using IP inside the pi.cfg did not change the behavior
however, enabling debug logging to see what pops there

the test scripts were simply:

conn_string = “host=‘wslpidpg098.inmar.com’ dbname='privacyidea’
user=‘svcpidea’ password=‘catonaroof’”

vs

conn_string = “host=‘10.1.25.131’ dbname=‘privacyidea’ user='svcpidea’
password=‘catonaroof’”

RickP · December 8, 2015, 4:44pm

We have just realized that the delay isnt in the initial packet that would
be establishing the DB connection, the delay is the packet prior to the
first packet directed at the DB instance, we thought maybe DNS resolution
but that is working fine instant resolutions, does that give you any clue
as to what the app might be doing before initiating the DB connection?

…443 traffic
10:43:31.823697 IP 10.1.25.133.443 > 192.168.60.103.37106: Flags [.], ack
750, win 243, options [nop,nop,TS val 528643261 ecr 152553203], length 0

delay is before the first packet directed at port 5432 on the Postgres host

10:43:36.880382 IP 10.1.25.133.51043 > 10.1.25.131.5432: Flags [S], seq
1816583097, win 29200, options [mss 1460,sackOK,TS val 528644525 ecr
0,nop,wscale 7], length 0
…DB 5432 traffic

RickP · December 8, 2015, 10:11pm

have tried both basic postgres postgresql: and postgresql+psycopg2:

yes have restarted uwsgi with each change,

perhaps we are looking down the wrong path at this point enabling debug
logging found a 5 second ldap delay see below

[2015-12-08 14:31:21,496][13346][140328807311296][DEBUG][ldap3:84]
BASIC:instantiated Tls: <Tls(validate=0)>
[2015-12-08 14:31:21,496][13346][140328807311296][DEBUG][ldap3:84]
BASIC:instantiated Server: <Server(host=u’company.com’, port=636,
use_ssl=True, tls=Tls(validate=0), get_info=‘NO_INFO’)>
[2015-12-08 14:31:21,496][13346][140328807311296][DEBUG][ldap3:84]
BASIC:instantiated : <ldaps://company.com:636 - ssl - user:
CN=vcadminr,OU=Service Accounts Exemption,OU=Support
Resources,DC=company,DC=com - unbound - closed - - tls not
started - not listening - No strategy - async - real DSA - not pooled -
cannot stream output>
[2015-12-08 14:31:21,497][13346][140328807311296][DEBUG][ldap3:84]
BASIC:instantiated Connection:
<Connection(server=Server(host=u’company.com’, port=636, use_ssl=True,
tls=Tls(validate=0), get_info=‘NO_INFO’), user=u’CN=vcadminr,OU=Service
Accounts Exemption,OU=Support Resources,DC=company,DC=com’,
password=‘passwd2’, auto_bind=‘NONE’, version=3, authentication=‘SIMPLE’,
client_strategy=‘SYNC’, auto_referrals=False, check_names=True,
read_only=False, lazy=False, raise_exceptions=False)>
[2015-12-08 14:31:21,497][13346][140328807311296][DEBUG][ldap3:84]
NETWORK:opening connection for <ldaps://company.com:636 - ssl - user:
CN=vcadminr,OU=Service Accounts Exemption,OU=Support
Resources,DC=company,DC=com - unbound - closed - - tls not
started - not listening - SyncStrategy>

Here is the 5 seconds …. Here relating to ldap though but perhaps that is
the problem and why db connection pooling and dns changes aren’t affecting
the time pulling keys

[2015-12-08 14:31:26,508][13346][140328807311296][DEBUG][ldap3:84]
BASIC:address for <ldaps://company.com:636 - ssl> resolved as <[2, 1, 6,
’’, (‘10.234.20.99’, 636)]>
[2015-12-08 14:31:26,509][13346][140328807311296][DEBUG][ldap3:84]
BASIC:address for <ldaps://company.com:636 - ssl> resolved as <[2, 1, 6,
’’, (‘10.234.20.97’, 636)]>
[2015-12-08 14:31:26,510][13346][140328807311296][DEBUG][ldap3:84]
BASIC:address for <ldaps://company.com:636 - ssl> resolved as <[2, 1, 6,
’’, (‘10.234.20.98’, 636)]>
[2015-12-08 14:31:26,510][13346][140328807311296][DEBUG][ldap3:84]
BASIC:address for <ldaps://company.com:636 - ssl> resolved as <[2, 1, 6,
’’, (‘10.11.28.21’, 636)]>
[2015-12-08 14:31:26,511][13346][140328807311296][DEBUG][ldap3:84]
BASIC:address for <ldaps://company.com:636 - ssl> resolved as <[2, 1, 6,
’’, (‘10.22.244.103’, 636)]>
[2015-12-08 14:31:26,511][13346][140328807311296][DEBUG][ldap3:84]
BASIC:obtained candidate address for <ldaps://company.com:636 - ssl>: <[2,
1, 6, ‘’, (‘10.234.20.99’, 636)]> with mode IP_V6_PREFERRED
[2015-12-08 14:31:26,511][13346][140328807311296][DEBUG][ldap3:84]
BASIC:obtained candidate address for <ldaps://company.com:636 - ssl>: <[2,
1, 6, ‘’, (‘10.234.20.97’, 636)]> with mode IP_V6_PREFERRED
[2015-12-08 14:31:26,511][13346][140328807311296][DEBUG][ldap3:84]
BASIC:obtained candidate address for <ldaps://company.com:636 - ssl>: <[2,
1, 6, ‘’, (‘10.234.20.98’, 636)]> with mode IP_V6_PREFERRED
[2015-12-08 14:31:26,512][13346][140328807311296][DEBUG][ldap3:84]
BASIC:obtained candidate address for <ldaps://company.com:636 - ssl>: <[2,
1, 6, ‘’, (‘10.235.28.21’, 636)]> with mode IP_V6_PREFERRED
[2015-12-08 14:31:26,512][13346][140328807311296][DEBUG][ldap3:84]
BASIC:obtained candidate address for <ldaps://company.com:636 - ssl>: <[2,
1, 6, ‘’, (‘10.225.244.103’, 636)]> with mode IP_V6_PREFERRED
[2015-12-08 14:31:26,512][13346][140328807311296][DEBUG][ldap3:84]
BASIC:try to open candidate address [2, 1, 6, ‘’, (‘10.234.20.99’, 636)]
[2015-12-08 14:31:26,520][13346][140328807311296][DEBUG][ldap3:84]
NETWORK:socket wrapped with SSL for <ldaps://company.com:636 - ssl - user:
CN=vcadminr,OU=Service Accounts Exemption,OU=Support
Resources,DC=company,DC=com - unbound - closed - <local:
10.234.25.133:35511 - remote: 10.234.20.99:636> - tls not started - not
listening - SyncStrategy>
[2015-12-08 14:31:26,521][13346][140328807311296][DEBUG][ldap3:84]
NETWORK:connection open for <ldaps://company.com:636 - ssl - user:
CN=vcadminr,OU=Service Accounts Exemption,OU=Support
Resources,DC=company,DC=com - unbound - open - <local: 10.234.25.133:35511

remote: 10.234.20.99:636> - tls not started - listening - SyncStrategy>

RickP · December 8, 2015, 3:49pm

We have added these lines to the pi.cfg file but they have not changed the
behavior, if we request a token (ssh key) with the
privacyidea-authorizedkeys command sleep beyond 60 seconds and request a
key there is still a very long 5-7second pause waiting for the DB
connection. Where might we find the actual python code that is initiating
the db connection?

SQLALCHEMY_POOL_SIZE = 20
SQLALCHEMY_POOL_TIMEOUT = 3600

This pause is very visible in a tcpdump:

… port 443 traffic prior
10:43:31.823697 IP 10.1.25.133.443 > 192.168.60.103.37106: Flags [.], ack
750, win 243, options [nop,nop,TS val 528643261 ecr 152553203], length 0

first packet directed at port 5432 on the Postgres host, delays 5 seconds
very consistently if the default 60 second connection expires

10:43:36.880382 IP 10.1.25.133.51043 > 10.1.25.131.5432: Flags [S], seq
1816583097, win 29200, options [mss 1460,sackOK,TS val 528644525 ecr
0,nop,wscale 7], length 0

cornelinux · December 8, 2015, 6:58pm

Hi Rick,

to rule out the nginx/uwsgi you could run the server with the runscript
as user root:

pi-manage runserver -t 10.1.25.133 -p 1234

And connect to this machine (if it is ok for you, to run it unencrypted.

Otherwise run it locallay and do an SSH port forwarding.

pi-manage runserver -z 127.0.0.1

If the 5sec remain it is either due to PI code or due to the database.
If it is gone. We need to take a look at nginx/uwsgi.

Kind regards
CorneliusAm Dienstag, den 08.12.2015, 19:43 +0100 schrieb Cornelius Kölbel:

Which postgres driver are you using?

Have you restarted uwsgi after changing pi.cfg?

Kind regards
Cornelius

Am Dienstag, den 08.12.2015, 10:41 -0800 schrieb RickP:

in a basic python script to connect to the DB we effeminately see the
delay if using hostname, and do not see the delay at all if using IP,
leads one to think something bad in dns, but nslookups immediately
return the hostname lookup, using IP inside the pi.cfg did not change
the behavior however, enabling debug logging to see what pops there

the test scripts were simply:

conn_string = “host=‘wslpidpg098.inmar.com’ dbname=‘privacyidea’
user=‘svcpidea’ password=‘catonaroof’”

vs

conn_string = “host=‘10.1.25.131’ dbname=‘privacyidea’ user=‘svcpidea’
password=‘catonaroof’”

–
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/privacyidea/39c72a57-281e-4853-8422-95d31281ad23%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

–
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)

RickP · December 8, 2015, 6:11pm

Cornelius we created these PI frontends running nginx/uwsgi instead of
Apache perhaps the issues are within the uwsgi app config?

cornelinux · December 8, 2015, 7:13pm

Hi Rick,

I can not reproduce 5 secs. But I have the impression, that using nginx
produces a similar effect on my system. Well not 5 secs but maybe
0.5secs delay. (But at the moment I am using it with a local MySQL)…

OK, here is my suggestion. After 60 secs the uwsgi workers get killed.
For what reason ever - probably because I am no nginx expert

So please try to change the value in
/etc/uwsgi/apps-enabled/privacyidea.xml
to a higher value. Several minutes and restart uwsgi.

My theory is, that the uwsgi workers get killed. And when they are
initialized again, they need to setup all the environment. Including the
DB connection, which takes more time to your remote postgres server.

At the moment I am to lazy to setup a postgres to confirm my theory
Can you do so by changing the above mentioned config?

Kind regards
CorneliusAm Dienstag, den 08.12.2015, 19:58 +0100 schrieb Cornelius Kölbel:

Hi Rick,

to rule out the nginx/uwsgi you could run the server with the runscript
as user root:

pi-manage runserver -t 10.1.25.133 -p 1234

And connect to this machine (if it is ok for you, to run it unencrypted.

Otherwise run it locallay and do an SSH port forwarding.

pi-manage runserver -z 127.0.0.1

If the 5sec remain it is either due to PI code or due to the database.
If it is gone. We need to take a look at nginx/uwsgi.

Kind regards
Cornelius

Am Dienstag, den 08.12.2015, 19:43 +0100 schrieb Cornelius Kölbel:

Which postgres driver are you using?

Have you restarted uwsgi after changing pi.cfg?

Kind regards
Cornelius

Am Dienstag, den 08.12.2015, 10:41 -0800 schrieb RickP:

in a basic python script to connect to the DB we effeminately see the
delay if using hostname, and do not see the delay at all if using IP,
leads one to think something bad in dns, but nslookups immediately
return the hostname lookup, using IP inside the pi.cfg did not change
the behavior however, enabling debug logging to see what pops there

the test scripts were simply:

conn_string = “host=‘wslpidpg098.inmar.com’ dbname=‘privacyidea’
user=‘svcpidea’ password=‘catonaroof’”

vs

conn_string = “host=‘10.1.25.131’ dbname=‘privacyidea’ user=‘svcpidea’
password=‘catonaroof’”

–
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/privacyidea/39c72a57-281e-4853-8422-95d31281ad23%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

–
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

–
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)

cornelinux · December 8, 2015, 6:43pm

Which postgres driver are you using?

Have you restarted uwsgi after changing pi.cfg?

Kind regards
CorneliusAm Dienstag, den 08.12.2015, 10:41 -0800 schrieb RickP:

in a basic python script to connect to the DB we effeminately see the
delay if using hostname, and do not see the delay at all if using IP,
leads one to think something bad in dns, but nslookups immediately
return the hostname lookup, using IP inside the pi.cfg did not change
the behavior however, enabling debug logging to see what pops there

the test scripts were simply:

conn_string = “host=‘wslpidpg098.inmar.com’ dbname=‘privacyidea’
user=‘svcpidea’ password=‘catonaroof’”

vs

conn_string = “host=‘10.1.25.131’ dbname=‘privacyidea’ user=‘svcpidea’
password=‘catonaroof’”

–
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/privacyidea/39c72a57-281e-4853-8422-95d31281ad23%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)

cornelinux · December 9, 2015, 8:31am

Hi Rick,

it looks like you used ldaps://domain.com as the AD Server address which
obviously is resolved to all your domain controllers.
I am not sure, how the underlying LDAP module handles this.

In fact the delay is in the debug log of [DEBUG][ldap3:84].

I recommend doing the following for testing:

In the LDAP resolver you can also provide a list of ldap servers.
See
http://privacyidea.readthedocs.org/en/latest/configuration/useridresolvers.html#ldap-resolver

So just try a list of your domain controllers.

ldaps://dc1.domain.com, ldaps://dc2.domain.com

The LDAP module will create a pool to do redundancy.

Kind regards
CorneliusAm Dienstag, den 08.12.2015, 14:11 -0800 schrieb RickP:

have tried both basic postgres postgresql: and postgresql+psycopg2:

yes have restarted uwsgi with each change,

perhaps we are looking down the wrong path at this point enabling
debug logging found a 5 second ldap delay see below

[2015-12-08 14:31:21,496][13346][140328807311296][DEBUG][ldap3:84]
BASIC:instantiated Tls: <Tls(validate=0)>
[2015-12-08 14:31:21,496][13346][140328807311296][DEBUG][ldap3:84]
BASIC:instantiated Server: <Server(host=u’company.com’, port=636,
use_ssl=True, tls=Tls(validate=0), get_info=‘NO_INFO’)>
[2015-12-08 14:31:21,496][13346][140328807311296][DEBUG][ldap3:84]
BASIC:instantiated : <ldaps://company.com:636 - ssl -
user: CN=vcadminr,OU=Service Accounts Exemption,OU=Support
Resources,DC=company,DC=com - unbound - closed - - tls not
started - not listening - No strategy - async - real DSA - not pooled

cannot stream output>
[2015-12-08 14:31:21,497][13346][140328807311296][DEBUG][ldap3:84]
BASIC:instantiated Connection:
<Connection(server=Server(host=u’company.com’, port=636, use_ssl=True,
tls=Tls(validate=0), get_info=‘NO_INFO’),
user=u’CN=vcadminr,OU=Service Accounts Exemption,OU=Support
Resources,DC=company,DC=com’, password=‘passwd2’, auto_bind=‘NONE’,
version=3, authentication=‘SIMPLE’, client_strategy=‘SYNC’,
auto_referrals=False, check_names=True, read_only=False, lazy=False,
raise_exceptions=False)>
[2015-12-08 14:31:21,497][13346][140328807311296][DEBUG][ldap3:84]
NETWORK:opening connection for <ldaps://company.com:636 - ssl - user:
CN=vcadminr,OU=Service Accounts Exemption,OU=Support
Resources,DC=company,DC=com - unbound - closed - - tls not
started - not listening - SyncStrategy>

Here is the 5 seconds …. Here relating to ldap though but perhaps that
is the problem and why db connection pooling and dns changes aren’t
affecting the time pulling keys

[2015-12-08 14:31:26,508][13346][140328807311296][DEBUG][ldap3:84]
BASIC:address for <ldaps://company.com:636 - ssl> resolved as <[2, 1,
6, ‘’, (‘10.234.20.99’, 636)]>
[2015-12-08 14:31:26,509][13346][140328807311296][DEBUG][ldap3:84]
BASIC:address for <ldaps://company.com:636 - ssl> resolved as <[2, 1,
6, ‘’, (‘10.234.20.97’, 636)]>
[2015-12-08 14:31:26,510][13346][140328807311296][DEBUG][ldap3:84]
BASIC:address for <ldaps://company.com:636 - ssl> resolved as <[2, 1,
6, ‘’, (‘10.234.20.98’, 636)]>
[2015-12-08 14:31:26,510][13346][140328807311296][DEBUG][ldap3:84]
BASIC:address for <ldaps://company.com:636 - ssl> resolved as <[2, 1,
6, ‘’, (‘10.11.28.21’, 636)]>
[2015-12-08 14:31:26,511][13346][140328807311296][DEBUG][ldap3:84]
BASIC:address for <ldaps://company.com:636 - ssl> resolved as <[2, 1,
6, ‘’, (‘10.22.244.103’, 636)]>
[2015-12-08 14:31:26,511][13346][140328807311296][DEBUG][ldap3:84]
BASIC:obtained candidate address for <ldaps://company.com:636 - ssl>:
<[2, 1, 6, ‘’, (‘10.234.20.99’, 636)]> with mode IP_V6_PREFERRED
[2015-12-08 14:31:26,511][13346][140328807311296][DEBUG][ldap3:84]
BASIC:obtained candidate address for <ldaps://company.com:636 - ssl>:
<[2, 1, 6, ‘’, (‘10.234.20.97’, 636)]> with mode IP_V6_PREFERRED
[2015-12-08 14:31:26,511][13346][140328807311296][DEBUG][ldap3:84]
BASIC:obtained candidate address for <ldaps://company.com:636 - ssl>:
<[2, 1, 6, ‘’, (‘10.234.20.98’, 636)]> with mode IP_V6_PREFERRED
[2015-12-08 14:31:26,512][13346][140328807311296][DEBUG][ldap3:84]
BASIC:obtained candidate address for <ldaps://company.com:636 - ssl>:
<[2, 1, 6, ‘’, (‘10.235.28.21’, 636)]> with mode IP_V6_PREFERRED
[2015-12-08 14:31:26,512][13346][140328807311296][DEBUG][ldap3:84]
BASIC:obtained candidate address for <ldaps://company.com:636 - ssl>:
<[2, 1, 6, ‘’, (‘10.225.244.103’, 636)]> with mode IP_V6_PREFERRED
[2015-12-08 14:31:26,512][13346][140328807311296][DEBUG][ldap3:84]
BASIC:try to open candidate address [2, 1, 6, ‘’, (‘10.234.20.99’,
636)]
[2015-12-08 14:31:26,520][13346][140328807311296][DEBUG][ldap3:84]
NETWORK:socket wrapped with SSL for <ldaps://company.com:636 - ssl -
user: CN=vcadminr,OU=Service Accounts Exemption,OU=Support
Resources,DC=company,DC=com - unbound - closed - <local:
10.234.25.133:35511 - remote: 10.234.20.99:636> - tls not started -
not listening - SyncStrategy>
[2015-12-08 14:31:26,521][13346][140328807311296][DEBUG][ldap3:84]
NETWORK:connection open for <ldaps://company.com:636 - ssl - user:
CN=vcadminr,OU=Service Accounts Exemption,OU=Support
Resources,DC=company,DC=com - unbound - open - <local:
10.234.25.133:35511 - remote: 10.234.20.99:636> - tls not started -
listening - SyncStrategy>

–
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/privacyidea/16194ad8-8523-4c45-ad3d-de11dd9c1113%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)

cornelinux · December 9, 2015, 1:57pm

…this was an easy one.
THanks!
https://github.com/privacyidea/privacyidea/commit/6975f4c58c57411c25e5b3da51711236adec03aeAm Mittwoch, den 09.12.2015, 14:45 +0100 schrieb Cornelius Kölbel:

You are right, Rick.
The LDAP Machine resolver does not support the server pool.

Too bad.
I will create an issue and fix this.

Kind regards
Cornelius

Am Mittwoch, den 09.12.2015, 05:32 -0800 schrieb RickP:

Unfortunately the list of ldap servers works in the Users resolver
with comma seperated entries, but for computers it fails, gets LDAP
InvalidServerError() with the exact same cut and pasted entry from
Users when tried on Computers.

We had tried the base company name as a method to provide multiple
backends there and it seemed to work. Changed that yesterday to a
single entry but did not see any change, will review and test again
this morning here.

Thanks for all the replies!!

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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/privacyidea/15be07a1-31ce-42c9-bca1-9a6ee0f3ae69%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

–
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)

RickP · December 9, 2015, 1:32pm

Unfortunately the list of ldap servers works in the Users resolver with
comma seperated entries, but for computers it fails, gets LDAP
InvalidServerError() with the exact same cut and pasted entry from Users
when tried on Computers.

We had tried the base company name as a method to provide multiple backends
there and it seemed to work. Changed that yesterday to a single entry but
did not see any change, will review and test again this morning here.

Thanks for all the replies!!

PostgreSQL

Thanks, Jim

Thanks, Jim

We were thinking the same things… but we are not sure how to pass any of those options in the pi.cfg URI line successfully, any hints on that??

pi-manage runserver -t 10.1.25.133 -p 1234

pi-manage runserver -z 127.0.0.1

pi-manage runserver -t 10.1.25.133 -p 1234

pi-manage runserver -z 127.0.0.1

Thanks for all the replies!!

Thanks,
Jim

Thanks,
Jim

We were thinking the same things… but we are not sure how to pass
any of those options in the pi.cfg URI line successfully, any hints on
that??