Page 1 of 1

'admin reindex' fails due to pg authentication problem...

PostPosted: Thu Oct 30, 2014 2:03 pm
by j.abbott
Hello,

I'm trying to reindex our omero instance to solve a problem with searching not finding the images it should, following the 'online' process described at https://www.openmicroscopy.org/site/sup ... monitoring. The initial stages of this complete ok, but running

Code: Select all
omero admin reindex --reset 0


produces a bunch of stack traces, seeming to stem from a postgres authentication issue:

Code: Select all
2014-10-30 13:31:49,879 1952       [      main] INFO   ome.services.db.SelfCorrectingDataSource - Found location in errorTimes: -1
2014-10-30 13:31:49,882 1955       [      main] INFO   ome.services.db.SelfCorrectingDataSource - Removing 0 from errorTimes
2014-10-30 13:31:49,882 1955       [      main] WARN   ome.services.db.SelfCorrectingDataSource - Registering error with list: Current size: 0
2014-10-30 13:31:49,882 1955       [      main] INFO   ome.services.db.SelfCorrectingDataSource - Sleeping for 0 then retry: 1
2014-10-30 13:31:49,884 1957       [      main] INFO   ome.services.db.SelfCorrectingDataSource - Found location in errorTimes: -1
2014-10-30 13:31:49,884 1957       [      main] INFO   ome.services.db.SelfCorrectingDataSource - Removing 0 from errorTimes
2014-10-30 13:31:49,884 1957       [      main] WARN   ome.services.db.SelfCorrectingDataSource - Registering error with list: Current size: 1
2014-10-30 13:31:49,884 1957       [      main] INFO   ome.services.db.SelfCorrectingDataSource - Sleeping for 1000 then retry: 2
2014-10-30 13:31:50,887 2960       [      main] INFO   ome.services.db.SelfCorrectingDataSource - Found location in errorTimes: -1
2014-10-30 13:31:50,888 2961       [      main] INFO   ome.services.db.SelfCorrectingDataSource - Removing 0 from errorTimes
2014-10-30 13:31:50,888 2961       [      main] WARN   ome.services.db.SelfCorrectingDataSource - Registering error with list: Current size: 2
2014-10-30 13:31:50,900 2973       [      main] ERROR  ome.services.db.SelfCorrectingDataSource - Failed to acquire connection after retries=3
java.sql.SQLException: unable to get a connection from pool of a PoolingDataSource containing an XAPool of resource 1e7ef441-69c4-43d7-8d9a-fbbcfdd592f3 with 0 connection(s) (0 still available)
<!--snip-->
Caused by: org.postgresql.util.PSQLException: FATAL: password authentication failed for user "db_user"


So this should be simple enough...however we have not seen any problems with database authentication elsewhere in the system, which otherwise seems to be running ok.

I can successfully connect to the postgres server from the same terminal where this command failed, using credentials/hostnames copy and pasted from the `omero config get` output, so the underlying database connectivity does not seem to be a problem, and the configuration parameters seem to be correct within omero.

I've copied the diagnostics output below....any suggestions as to why database authentication is failing with this command but not elsewhere in omero?



Code: Select all
================================================================================
OMERO Diagnostics 5.0.5-ice35-b47
===============
Commands:   java -version                  1.8.0     (/opt/jdk1.8.0/bin/java -- 3 others)
Commands:   python -V                      2.6.6     (/usr/bin/python)
Commands:   icegridnode --version          3.5.1     (/usr/bin/icegridnode)
Commands:   icegridadmin --version         3.5.1     (/usr/bin/icegridadmin)
Commands:   psql --version                 8.4.20    (/usr/bin/psql)

Server:     icegridnode                    running
Server:     Blitz-0                        active (pid = 3538, enabled)
Server:     DropBox                        active (pid = 3584, enabled)
Server:     FileServer                     active (pid = 3587, enabled)
Server:     Indexer-0                      inactive (disabled)
Server:     MonitorServer                  active (pid = 3593, enabled)
Server:     OMERO.Glacier2                 active (pid = 3595, enabled)
Server:     OMERO.IceStorm                 active (pid = 3626, enabled)
Server:     PixelData-0                    active (pid = 3623, enabled)
Server:     Processor-0                    active (pid = 3624, enabled)
Server:     Tables-0                       active (pid = 3596, enabled)
Server:     TestDropBox                    inactive (enabled)

OMERO:      SSL port                       4064
OMERO:      TCP port                       4063

Log dir:    /opt/omero/var/log             exists

Log files:  Blitz-0.log                    141.0 MB      errors=1322 warnings=295
Log files:  Blitz-0.log.1                  524.0 MB      errors=61   warnings=35418
Log files:  DropBox.log                    3.0 KB        errors=0    warnings=1   
Log files:  FileServer.log                 0.0 KB       
Log files:  Indexer-0.log                  15.0 MB       errors=1968 warnings=89140
Log files:  MonitorServer.log              2.0 KB       
Log files:  OMEROweb.log                   2.0 KB       
Log files:  OMEROweb.log.1                 14.0 KB     
Log files:  OMEROweb.log.2                 37.0 KB     
Log files:  OMEROweb.log.3                 42.0 KB     
Log files:  OMEROweb.log.4                 39.0 KB     
Log files:  OMEROweb.log.5                 64.0 KB     
Log files:  OMEROweb_request.log           1.0 KB        errors=0    warnings=8   
Log files:  PixelData-0.log                275.0 KB     
Log files:  Processor-0.log                20.0 KB       errors=1    warnings=1   
Log files:  Tables-0.log                   2.0 KB        errors=0    warnings=1   
Log files:  TestDropBox.log                n/a
Log files:  master.err                     0.0 KB        errors=0    warnings=9   
Log files:  master.out                     0.0 KB       
Log files:  Total size                     681.59 MB


Environment:OMERO_HOME=(unset)             
Environment:OMERO_NODE=(unset)             
Environment:OMERO_MASTER=(unset)           
Environment:OMERO_TEMPDIR=/local_scratch/omero
Environment:PATH=/opt/jdk1.8.0/bin/:/home/cisbic/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/opt/dell/srvadmin/bin
Environment:PYTHONPATH=(unset)             
Environment:ICE_HOME=(unset)               
Environment:LD_LIBRARY_PATH=(unset)       
Environment:DYLD_LIBRARY_PATH=(unset)     

OMERO data dir: '/data/omero'   Exists? True    Is writable? True
OMERO temp dir: '/local_scratch/omero/omero/tmp'        Exists? True    Is writable? True   (Size: 0)
OMERO.web status... [RUNNING] (PID 30363)

Re: 'admin reindex' fails due to pg authentication problem..

PostPosted: Thu Oct 30, 2014 2:41 pm
by seb
Based on [1], my guess would be that this is because the database password is intentionally excluded from the cli arguments.

Running 'JAVA_OPTS="-Domero.db.pass=$(omero config get omero.db.pass)" omero admin reindex --reset 0' as a temporary trick might get you unstuck, but it's probably not the way it was intended...)

[1] https://github.com/openmicroscopy/openm ... 1375-L1380

Re: 'admin reindex' fails due to pg authentication problem..

PostPosted: Fri Oct 31, 2014 8:56 am
by j.abbott
Ah, that did the trick...brilliant - many thanks.

James

Re: 'admin reindex' fails due to pg authentication problem..

PostPosted: Fri Oct 31, 2014 2:51 pm
by sbesson
Hi James,

thanks for reporting this issue. As Sebastien pointed out, the database password is voluntarily not passed in the command-line arguments for security reasons. We are investigating a workaround in https://github.com/openmicroscopy/openm ... /pull/3161 using environment variables to prevent administrators from passing it manually.

We will keep you posted about the outcome of this request,
Sebastien