postgres user change problem
Dear all, due to some work on the user management of our servers we had to rename the user galaxy runs as. Up to now we used ident as postgres authentication method, meaning that here postgres expects unix username "galaxy" to have permissions of "galaxy" postgres user. The entry in universe_wsgi.ini is simply: database_connection = postgres:///galaxy?host=/var/run/postgresql After the renaming, the new user "galaxynew" didn't get access at all at first. Now I tried two things: - adding a user "galaxynew" to postgres with permissions for database galaxy and - dumping the contents of database "galaxy" into a file and re-reading this into database "galaxynew", which is owned by the user "galaxynew" and of course changing the config file line to database_connection = postgres:///galaxynew?host=/var/run/postgresql In both cases I end up with an error during startup of galaxy: [...] WARNING 2012-01-20 14:46:55,556 Error closing cursor: current transaction is aborted, commands ignored until end of transaction block [...] ProgrammingError: (ProgrammingError) permission denied for relation migrate_version 'SELECT migrate_version.repository_id, migrate_version.repository_path, migrate_version.version \nFROM migrate_version \nWHERE migrate_version.repository_id = %(repository_id_1)s' {'repository_id_1': 'Galaxy'} (complete traceback below). Does anyone have a hint on how I could fix this? Cheers, Holger sqlalchemy.pool.QueuePool.0x...8d10 WARNING 2012-01-20 14:46:55,556 Error closing cursor: current transaction is aborted, commands ignored until end of transaction block Traceback (most recent call last): File "/local/data/home/galaxy/galaxy-dist-2011-11-23/lib/galaxy/web/buildapp.py", line 82, in app_factory app = UniverseApplication( global_conf = global_conf, **kwargs ) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/lib/galaxy/app.py", line 39, in __init__ create_or_verify_database( db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options ) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/lib/galaxy/model/migrate/check.py", line 99, in create_or_verify_database db_schema = schema.ControlledSchema( engine, migrate_repository ) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 24, in __init__ self._load() File "/local/data/home/galaxy/galaxy-dist-2011-11-23/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 41, in _load self.table.c.repository_id == str(self.repository.id))) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.6.egg/sqlalchemy/engine/base.py", line 1202, in execute return connection.execute(statement, *multiparams, **params) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.6.egg/sqlalchemy/engine/base.py", line 824, in execute return Connection.executors[c](self, object, multiparams, params) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.6.egg/sqlalchemy/engine/base.py", line 874, in _execute_clauseelement return self.__execute_context(context) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.6.egg/sqlalchemy/engine/base.py", line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.6.egg/sqlalchemy/engine/base.py", line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.6.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) ProgrammingError: (ProgrammingError) permission denied for relation migrate_version 'SELECT migrate_version.repository_id, migrate_version.repository_path, migrate_version.version \nFROM migrate_version \nWHERE migrate_version.repository_id = %(repository_id_1)s' {'repository_id_1': 'Galaxy'} -- Dr. Holger Klein Core Facility Bioinformatics Institute of Molecular Biology gGmbH (IMB) http://www.imb-mainz.de/ Tel: +49(6131) 39 21511
On Jan 20, 2012, at 9:04 AM, Holger Klein wrote:
Dear all,
due to some work on the user management of our servers we had to rename the user galaxy runs as. Up to now we used ident as postgres authentication method, meaning that here postgres expects unix username "galaxy" to have permissions of "galaxy" postgres user.
The entry in universe_wsgi.ini is simply: database_connection = postgres:///galaxy?host=/var/run/postgresql
After the renaming, the new user "galaxynew" didn't get access at all at first. Now I tried two things: - adding a user "galaxynew" to postgres with permissions for database galaxy and - dumping the contents of database "galaxy" into a file and re-reading this into database "galaxynew", which is owned by the user "galaxynew" and of course changing the config file line to database_connection = postgres:///galaxynew?host=/var/run/postgresql
In both cases I end up with an error during startup of galaxy: [...] WARNING 2012-01-20 14:46:55,556 Error closing cursor: current transaction is aborted, commands ignored until end of transaction block [...] ProgrammingError: (ProgrammingError) permission denied for relation migrate_version 'SELECT migrate_version.repository_id, migrate_version.repository_path, migrate_version.version \nFROM migrate_version \nWHERE migrate_version.repository_id = %(repository_id_1)s' {'repository_id_1': 'Galaxy'}
(complete traceback below).
Does anyone have a hint on how I could fix this?
Hi Holger, I'd use \dp in psql to check the table and sequence permissions. Most likely the restore set permissions on all the db objects back to the 'galaxy' postgres role even though the database itself is owned by galaxynew. --nate
Cheers, Holger
sqlalchemy.pool.QueuePool.0x...8d10 WARNING 2012-01-20 14:46:55,556 Error closing cursor: current transaction is aborted, commands ignored until end of transaction block
Traceback (most recent call last): File "/local/data/home/galaxy/galaxy-dist-2011-11-23/lib/galaxy/web/buildapp.py", line 82, in app_factory app = UniverseApplication( global_conf = global_conf, **kwargs ) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/lib/galaxy/app.py", line 39, in __init__ create_or_verify_database( db_url, kwargs.get( 'global_conf', {} ).get( '__file__', None ), self.config.database_engine_options ) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/lib/galaxy/model/migrate/check.py", line 99, in create_or_verify_database db_schema = schema.ControlledSchema( engine, migrate_repository ) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 24, in __init__ self._load() File "/local/data/home/galaxy/galaxy-dist-2011-11-23/eggs/sqlalchemy_migrate-0.5.4-py2.6.egg/migrate/versioning/schema.py", line 41, in _load self.table.c.repository_id == str(self.repository.id))) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.6.egg/sqlalchemy/engine/base.py", line 1202, in execute return connection.execute(statement, *multiparams, **params) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.6.egg/sqlalchemy/engine/base.py", line 824, in execute return Connection.executors[c](self, object, multiparams, params) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.6.egg/sqlalchemy/engine/base.py", line 874, in _execute_clauseelement return self.__execute_context(context) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.6.egg/sqlalchemy/engine/base.py", line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.6.egg/sqlalchemy/engine/base.py", line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File "/local/data/home/galaxy/galaxy-dist-2011-11-23/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.6.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) ProgrammingError: (ProgrammingError) permission denied for relation migrate_version 'SELECT migrate_version.repository_id, migrate_version.repository_path, migrate_version.version \nFROM migrate_version \nWHERE migrate_version.repository_id = %(repository_id_1)s' {'repository_id_1': 'Galaxy'}
-- Dr. Holger Klein Core Facility Bioinformatics Institute of Molecular Biology gGmbH (IMB) http://www.imb-mainz.de/ Tel: +49(6131) 39 21511 ___________________________________________________________ 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:
participants (2)
-
Holger Klein -
Nate Coraor