Hi, I am trying to connect my galaxy instance to a DB on a different host. This DB uses an SSL encryption and Auth method md5. I have no problems in connecting to the DB via the command line, but the line (in universe_wsqi.ini) : database_connection = postgres://<USERNAME>:<PASSWORD>@<HOST>:5432/<DB_NAME> gives the following error when I run the script 'run.sh': OperationalError: (OperationalError) FATAL: no pg_hba.conf entry for host "<HOST_IP>", user "<USERNAME>", database "<DB_NAME>", SSL off I managed to connect to a local instance of PostgreSQL server (without SSL, though) Can you give me some hints? Thanks in advance Nikolay -- Nikolay Vazov, PhD Research Computing Centre - http://hpc.uio.no USIT, University of Oslo
Nikolai Vazov wrote:
Hi,
I am trying to connect my galaxy instance to a DB on a different host. This DB uses an SSL encryption and Auth method md5.
I have no problems in connecting to the DB via the command line, but the line (in universe_wsqi.ini) :
database_connection = postgres://<USERNAME>:<PASSWORD>@<HOST>:5432/<DB_NAME>
gives the following error when I run the script 'run.sh':
OperationalError: (OperationalError) FATAL: no pg_hba.conf entry for host "<HOST_IP>", user "<USERNAME>", database "<DB_NAME>", SSL off
I managed to connect to a local instance of PostgreSQL server (without SSL, though)
Can you give me some hints?
Hi Nikolay, You may be able to connect with: postgres://<USERNAME>:<PASSWORD>@<HOST>:5432/<DB_NAME>?sslmode=<SSLMODE> Where SSLMODE is one of the valid values from libpq: http://www.postgresql.org/docs/9.0/static/libpq-ssl.html#LIBPQ-SSL-SSLMODE-S... If psycopg2 was built without SSL this will probably fail, in which case I can rebuild the psycopg2 eggs to link against SSL, but this may take a while. --nate
Thanks in advance
Nikolay
-- Nikolay Vazov, PhD Research Computing Centre - http://hpc.uio.no USIT, University of Oslo ___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at:
Hi, Nate, Thanks a lot for the hint : I also had suspicions about the SSL and it seems to be working, well, I mean, failing, as you supposed because of my psycopg2 which was built without SSL. OperationalError: (OperationalError) sslmode value "require" invalid when SSL support is not compiled in At least I know what I shall do :) Best regards Nikolay
Hi Nikolay,
You may be able to connect with:
postgres://<USERNAME>:<PASSWORD>@<HOST>:5432/<DB_NAME>?sslmode=<SSLMODE>
Where SSLMODE is one of the valid values from libpq:
http://www.postgresql.org/docs/9.0/static/libpq-ssl.html#LIBPQ-SSL-SSLMODE-S...
If psycopg2 was built without SSL this will probably fail, in which case I can rebuild the psycopg2 eggs to link against SSL, but this may take a while.
--nate
-- Nikolay Vazov, PhD Research Computing Centre - http://hpc.uio.no USIT, University of Oslo
Nikolai Vazov wrote:
Hi, Nate,
Thanks a lot for the hint : I also had suspicions about the SSL and it seems to be working, well, I mean, failing, as you supposed because of my psycopg2 which was built without SSL.
OperationalError: (OperationalError) sslmode value "require" invalid when SSL support is not compiled in
At least I know what I shall do :)
We provide the psycopg2 egg, so rebuilding it may not be so simple. I would suggest the following, if you want to do it by hand and link it against your system libpq: 1. Download psycopg2 2.0.13. The version is going to matter since Galaxy expects it. 2. In setup.py, find: from distutils.core import setup, Extension Prior to that import, add the following: from scramble_lib import * 3. Find the pg_config executable and make sure the version you want to link against is the version found first on your $PATH. 4. Execute: % PYTHONPATH=/path/to/galaxy-dist/scripts/scramble/lib python setup.py egg_info --tag-build=_8.4.2_static bdist_egg Let me know if you have any troubles with this process. --nate
Best regards
Nikolay
Hi Nikolay,
You may be able to connect with:
postgres://<USERNAME>:<PASSWORD>@<HOST>:5432/<DB_NAME>?sslmode=<SSLMODE>
Where SSLMODE is one of the valid values from libpq:
http://www.postgresql.org/docs/9.0/static/libpq-ssl.html#LIBPQ-SSL-SSLMODE-S...
If psycopg2 was built without SSL this will probably fail, in which case I can rebuild the psycopg2 eggs to link against SSL, but this may take a while.
--nate
-- Nikolay Vazov, PhD Research Computing Centre - http://hpc.uio.no USIT, University of Oslo
Hi, Nate, I am trying to follow the designed steps below. 1. I downloaded psycopg2 2.0.13. Does it matter where in galaxy-dist directory I place it? 2. I modified setup.py - OK 3. pg_config --version gives : PostgreSQL 8.1.23 I don't really get what you mean by " make sure the version you want to link against is the version found first on your $PATH " Is this the Python version you mean? I am using python26 (64bit for CentosOS) and I use a link I created to it in my /home/galaxy/galaxy-python directory. The galaxy dist is located at /home/galaxy/galaxy-dist 4. Given the above details, can I just execute 4? Thank you Nikolay On Mon, 2 May 2011 10:27:41 -0400, Nate Coraor <nate@bx.psu.edu> wrote:
Nikolai Vazov wrote:
Hi, Nate,
Thanks a lot for the hint : I also had suspicions about the SSL and it seems to be working, well, I mean, failing, as you supposed because of my psycopg2 which was built without SSL.
OperationalError: (OperationalError) sslmode value "require" invalid when SSL support is not compiled in
At least I know what I shall do :)
We provide the psycopg2 egg, so rebuilding it may not be so simple. I would suggest the following, if you want to do it by hand and link it against your system libpq:
1. Download psycopg2 2.0.13. The version is going to matter since Galaxy expects it.
2. In setup.py, find:
from distutils.core import setup, Extension
Prior to that import, add the following:
from scramble_lib import *
3. Find the pg_config executable and make sure the version you want to link against is the version found first on your $PATH.
4. Execute:
% PYTHONPATH=/path/to/galaxy-dist/scripts/scramble/lib python setup.py egg_info --tag-build=_8.4.2_static bdist_egg
Let me know if you have any troubles with this process.
--nate
-- Nikolay Vazov, PhD Research Computing Centre - http://hpc.uio.no USIT, University of Oslo
Nikolai Vazov wrote:
Hi, Nate,
I am trying to follow the designed steps below.
1. I downloaded psycopg2 2.0.13. Does it matter where in galaxy-dist directory I place it?
2. I modified setup.py - OK
3. pg_config --version gives :
PostgreSQL 8.1.23
I don't really get what you mean by
" make sure the version you want to link against is the version found first on your $PATH "
Is this the Python version you mean? I am using python26 (64bit for CentosOS) and I use a link I created to it in my /home/galaxy/galaxy-python directory. The galaxy dist is located at /home/galaxy/galaxy-dist
4. Given the above details, can I just execute 4?
Yes, you should be able to. That version of postgres is rather old but psycopg2 should be backwards compatible. --nate
Thank you
Nikolay
On Mon, 2 May 2011 10:27:41 -0400, Nate Coraor <nate@bx.psu.edu> wrote:
Nikolai Vazov wrote:
Hi, Nate,
Thanks a lot for the hint : I also had suspicions about the SSL and it seems to be working, well, I mean, failing, as you supposed because of my psycopg2 which was built without SSL.
OperationalError: (OperationalError) sslmode value "require" invalid when SSL support is not compiled in
At least I know what I shall do :)
We provide the psycopg2 egg, so rebuilding it may not be so simple. I would suggest the following, if you want to do it by hand and link it against your system libpq:
1. Download psycopg2 2.0.13. The version is going to matter since Galaxy expects it.
2. In setup.py, find:
from distutils.core import setup, Extension
Prior to that import, add the following:
from scramble_lib import *
3. Find the pg_config executable and make sure the version you want to link against is the version found first on your $PATH.
4. Execute:
% PYTHONPATH=/path/to/galaxy-dist/scripts/scramble/lib python setup.py egg_info --tag-build=_8.4.2_static bdist_egg
Let me know if you have any troubles with this process.
--nate
-- Nikolay Vazov, PhD Research Computing Centre - http://hpc.uio.no USIT, University of Oslo
Hi again, The version of the PostgreSQL server I am trying to connect to is actually 8.3. The DB department say they will soon move to 9.0. The psycopg2 version in my eggs.ini for Galaxy is 8.4.2: psycopg2-2.0.13_8.4.2_static-py2.6-linux-x86_64-ucs4.egg Do you think it will work?
3. Find the pg_config executable and make sure the version you want to link against is the version found first on your $PATH.
-- Nikolay Vazov, PhD Research Computing Centre - http://hpc.uio.no USIT, University of Oslo
Nikolai Vazov wrote:
Hi again,
The version of the PostgreSQL server I am trying to connect to is actually 8.3. The DB department say they will soon move to 9.0. The psycopg2 version in my eggs.ini for Galaxy is 8.4.2:
psycopg2-2.0.13_8.4.2_static-py2.6-linux-x86_64-ucs4.egg
Do you think it will work?
Using the instructions I sent you will actually link the psycopg2 egg against your own postgres libraries, so that version string would be incorrect anyway. Regardless, the 8.4.2 egg is known to work against 9.0 databases, and older versions may work as well. From what I've seen, the client and server just communicate at the level of the lower version of the two. --nate
3. Find the pg_config executable and make sure the version you want to link against is the version found first on your $PATH.
-- Nikolay Vazov, PhD Research Computing Centre - http://hpc.uio.no USIT, University of Oslo
Hi, Nate, When trying to execute PYTHONPATH=/home/galaxy/galaxy-dist/scripts/scramble/lib python26 setup.py egg_info --tag-build=_8.4.2_static bdist_egg I get Traceback (most recent call last): File "setup.py", line 52, in <module> from scramble_lib import * File "/home/galaxy/galaxy-dist/scripts/scramble/lib/scramble_lib.py", line 149, in <module> from ez_setup import use_setuptools ImportError: No module named ez_setup I guess I shall install both setuptools and ez_setup before I continue ... Thank you again for your time Nikolay -- Nikolay Vazov, PhD Research Computing Centre - http://hpc.uio.no USIT, University of Oslo
Nikolai Vazov wrote:
Hi, Nate,
When trying to execute
PYTHONPATH=/home/galaxy/galaxy-dist/scripts/scramble/lib python26 setup.py egg_info --tag-build=_8.4.2_static bdist_egg
I get
Traceback (most recent call last): File "setup.py", line 52, in <module> from scramble_lib import * File "/home/galaxy/galaxy-dist/scripts/scramble/lib/scramble_lib.py", line 149, in <module> from ez_setup import use_setuptools ImportError: No module named ez_setup
Sorry, this is a step that's handled automatically by the scramble script, which is what's normally used to build eggs for Galaxy. If you grab ez_setup.py[1] and place it in: /home/galaxy/galaxy-dist/scripts/scramble/lib/scramble_lib.py then you can continue. --nate [1] http://peak.telecommunity.com/dist/ez_setup.py
I guess I shall install both setuptools and ez_setup before I continue ...
Thank you again for your time
Nikolay
-- Nikolay Vazov, PhD Research Computing Centre - http://hpc.uio.no USIT, University of Oslo
Hi, Nate, I followed your instructions and managed to compile the psycopg2. What I did: 1. Downloaded a tar.gz of psycopg2 2.0.13 to /home/galaxy/galaxy-dist and unpacked it there 2. cd into psycopg2, modified the setup.py according to your instructions (added the line containing scramble_lib) 3. added the file ez_setup.py to /home/galaxy/galaxy-dist/scripts/scramble/lib/, the same dir where scramble_lib.py is located 4. ran PYTHONPATH=/home/galaxy/galaxy-dist/scripts/scramble/lib python26 setup.py egg_info --tag-build=_8.4.2_static bdist_egg the output is attached (looks ok) 5. ran run.sh (database_connection = postgres://user:pass@host:5432/vd_galaxy?sslmode=require) and got the error OperationalError: (OperationalError) sslmode value "require" invalid when SSL support is not compiled in Was the new egg version taken into account? Nikolay -- Nikolay Vazov, PhD Research Computing Centre - http://hpc.uio.no USIT, University of Oslo
Nikolai Vazov wrote:
Hi, Nate,
I followed your instructions and managed to compile the psycopg2.
What I did:
1. Downloaded a tar.gz of psycopg2 2.0.13 to /home/galaxy/galaxy-dist and unpacked it there 2. cd into psycopg2, modified the setup.py according to your instructions (added the line containing scramble_lib) 3. added the file ez_setup.py to /home/galaxy/galaxy-dist/scripts/scramble/lib/, the same dir where scramble_lib.py is located 4. ran PYTHONPATH=/home/galaxy/galaxy-dist/scripts/scramble/lib python26 setup.py egg_info --tag-build=_8.4.2_static bdist_egg
the output is attached (looks ok)
5. ran run.sh (database_connection = postgres://user:pass@host:5432/vd_galaxy?sslmode=require) and got the error
OperationalError: (OperationalError) sslmode value "require" invalid when SSL support is not compiled in
Was the new egg version taken into account?
Whoops, missed a step! Copy: /home/galaxy/galaxy-dist/psycopg2-2.0.13/dist/psycopg2-2.0.13_8.4.2_static-<platform>.egg to: /home/galaxy/galaxy-dist/eggs --nate
Nikolay
-- Nikolay Vazov, PhD Research Computing Centre - http://hpc.uio.no USIT, University of Oslo
participants (2)
-
Nate Coraor -
Nikolai Vazov