psycopg2 module and SSL connection to postgresql

classic Classic list List threaded Threaded
2 messages Options
| Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

psycopg2 module and SSL connection to postgresql

Floreline TOUCHARD
Hi Galaxy Community,

I would like to connect my Galaxy instance to my postgresql database using SSL encryption and Auth method md5.
I have no problems connecting to the DB via the command line with SSL encryption.

When setting the galaxy.ini file with postgresql database connection to "postgresql://<USERNAME>:<PWD>@<HOST>:5432/<DB_NAME>?sslmode=require", I get the following message :
"OperationalError: (psycopg2.OperationalError) sslmode value "require" invalid when SSL support is not compiled in"

Regarding the former post I thought that it might come from the module psycopg2's compilation.


I followed the steps of the post but encounter a different error.

What I did: 

 1. Downloaded a tar.gz of psycopg2 2.5.1 (I saw the needed version in the file eggs.ini) to <install dir>/galaxy/ and unpacked it there 
 2. cd into psycopg2, modified the setup.py according to the instructions (added the line containing scramble_lib) 
 3. export the path to pg_config (export PATH=<path/to/postgresql>/bin:$PATH)
 4. added the file ez_setup.py (downloaded from https://bootstrap.pypa.io/ez_setup.py) to <install dir>/galaxy/scripts/scramble/lib/, the same dir where scramble_lib.py is located 
 5. ran PYTHONPATH=<install dir>/galaxy/scripts/scramble/lib python setup.py egg_info --tag-build=_9.4.4_static bdist_egg 
And get the following error :

"
Traceback (most recent call last):
  File "setup.py", line 57, in <module>
    from scramble_lib import *
  File "<install dir>/galaxy/scripts/scramble/lib/scramble_lib.py", line 150, in <module>
    use_setuptools( download_delay=8, to_dir=os.path.dirname( __file__ ) )
  File "<install dir>/galaxy/scripts/scramble/lib/ez_setup.py", line 160, in use_setuptools
    version = _resolve_version(version)
  File "<install dir>/galaxy/scripts/scramble/lib/ez_setup.py", line 368, in _resolve_version
    resp = urlopen(meta_url)
  File "/usr/lib64/python2.6/urllib2.py", line 126, in urlopen
    return _opener.open(url, data, timeout)
  File "/usr/lib64/python2.6/urllib2.py", line 391, in open
    response = self._open(req, data)
  File "/usr/lib64/python2.6/urllib2.py", line 409, in _open
    '_open', req)
  File "/usr/lib64/python2.6/urllib2.py", line 369, in _call_chain
    result = func(*args)
  File "/usr/lib64/python2.6/urllib2.py", line 1198, in https_open
    return self.do_open(httplib.HTTPSConnection, req)
  File "/usr/lib64/python2.6/urllib2.py", line 1165, in do_open
    raise URLError(err)
urllib2.URLError: <urlopen error [Errno -2] Name or service not known>
"

Could it be because I have no internet access ? What module should I install to enable SSL encryption ?


Additional information :
- Postgresql version 9.4.4
- Galaxy version 15.10
- I use virtualenv for python environment


Thanks in advance,
Floreline


___________________________________________________________
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:
  https://lists.galaxyproject.org/

To search Galaxy mailing lists use the unified search at:
  http://galaxyproject.org/search/mailinglists/
| Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: psycopg2 module and SSL connection to postgresql

John Chilton-4
I don't know what the particular issue is - I do know that after a lot
of trial and error we were able to get Postgres + SSL working at MSI
before I left my previous job there - so there is hope.

I will say however, upgrading even to one newer Galaxy - namely 16.01
might solve this issue. We re-did Galaxy's dependency handling to use
virtualenvs, pip, and wheels in 16.01. It is much closer to a standard
Python dependency management stack. I think our wheel came bundled
with SSL support and even if I'm wrong about that support any random
online tutorial about debugging this problem in Python should be
applicable after the upgrade.

-John


On Mon, Jan 2, 2017 at 12:22 PM, Floreline TOUCHARD
<[hidden email]> wrote:

> Hi Galaxy Community,
>
> I have the same problem than Nikolay few years ago
> (http://dev.list.galaxyproject.org/connecting-to-a-DB-on-a-different-host-td4139952.html#a4139955)
> :
> I would like to connect my Galaxy instance to my postgresql database using
> SSL encryption and Auth method md5.
> I have no problems connecting to the DB via the command line with SSL
> encryption.
>
> When setting the galaxy.ini file with postgresql database connection to
> "postgresql://<USERNAME>:<PWD>@<HOST>:5432/<DB_NAME>?sslmode=require", I get
> the following message :
> "OperationalError: (psycopg2.OperationalError) sslmode value "require"
> invalid when SSL support is not compiled in"
>
> Regarding the former post I thought that it might come from the module
> psycopg2's compilation.
>
>
> I followed the steps of the post but encounter a different error.
>
> What I did:
>
>  1. Downloaded a tar.gz of psycopg2 2.5.1 (I saw the needed version in the
> file eggs.ini) to <install dir>/galaxy/ and unpacked it there
>  2. cd into psycopg2, modified the setup.py according to the instructions
> (added the line containing scramble_lib)
>  3. export the path to pg_config (export
> PATH=<path/to/postgresql>/bin:$PATH)
>  4. added the file ez_setup.py (downloaded from
> https://bootstrap.pypa.io/ez_setup.py) to <install
> dir>/galaxy/scripts/scramble/lib/, the same dir where scramble_lib.py is
> located
>  5. ran PYTHONPATH=<install dir>/galaxy/scripts/scramble/lib python setup.py
> egg_info --tag-build=_9.4.4_static bdist_egg
> And get the following error :
>
> "
> Traceback (most recent call last):
>   File "setup.py", line 57, in <module>
>     from scramble_lib import *
>   File "<install dir>/galaxy/scripts/scramble/lib/scramble_lib.py", line
> 150, in <module>
>     use_setuptools( download_delay=8, to_dir=os.path.dirname( __file__ ) )
>   File "<install dir>/galaxy/scripts/scramble/lib/ez_setup.py", line 160, in
> use_setuptools
>     version = _resolve_version(version)
>   File "<install dir>/galaxy/scripts/scramble/lib/ez_setup.py", line 368, in
> _resolve_version
>     resp = urlopen(meta_url)
>   File "/usr/lib64/python2.6/urllib2.py", line 126, in urlopen
>     return _opener.open(url, data, timeout)
>   File "/usr/lib64/python2.6/urllib2.py", line 391, in open
>     response = self._open(req, data)
>   File "/usr/lib64/python2.6/urllib2.py", line 409, in _open
>     '_open', req)
>   File "/usr/lib64/python2.6/urllib2.py", line 369, in _call_chain
>     result = func(*args)
>   File "/usr/lib64/python2.6/urllib2.py", line 1198, in https_open
>     return self.do_open(httplib.HTTPSConnection, req)
>   File "/usr/lib64/python2.6/urllib2.py", line 1165, in do_open
>     raise URLError(err)
> urllib2.URLError: <urlopen error [Errno -2] Name or service not known>
> "
>
> Could it be because I have no internet access ? What module should I install
> to enable SSL encryption ?
>
>
> Additional information :
> - Postgresql version 9.4.4
> - Galaxy version 15.10
> - I use virtualenv for python environment
>
>
> Thanks in advance,
> Floreline
>
>
> ___________________________________________________________
> 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:
>   https://lists.galaxyproject.org/
>
> To search Galaxy mailing lists use the unified search at:
>   http://galaxyproject.org/search/mailinglists/
___________________________________________________________
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:
  https://lists.galaxyproject.org/

To search Galaxy mailing lists use the unified search at:
  http://galaxyproject.org/search/mailinglists/
Loading...