FATAL: password authentication failed for user "datastore_ro"

[swingbit]

CKAN Version if known (or site URL)

upgrading from 2.8.0a (commit 2ba2829) to 2.8.3
Docker deployment

Please describe the expected behaviour

With a running dockerized deployment of CKAN 2.8.0a, I wanted to upgrade to 2.8.3.
The PostgreSQL database needs conversion from version 10 to version 11.

After moving my existing database to version 11, I expected a normal CKAN startup.

Please describe the actual behaviour

  File "/usr/local/bin/ckan-paster", line 8, in <module>
    sys.exit(run())
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/paste/script/command.py", line 102, in run
    invoke(command, command_name, options, args[1:])
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/paste/script/command.py", line 141, in invoke
    exit_code = runner.run(args)
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/paste/script/command.py", line 236, in run
    result = self.command()
  File "/usr/lib/ckan/venv/src/ckan/ckan/lib/cli.py", line 357, in command
    self._load_config(cmd!='upgrade')
  File "/usr/lib/ckan/venv/src/ckan/ckan/lib/cli.py", line 330, in _load_config
    self.site_user = load_config(self.options.config, load_site_user)
  File "/usr/lib/ckan/venv/src/ckan/ckan/lib/cli.py", line 237, in load_config
    load_environment(conf.global_conf, conf.local_conf)
  File "/usr/lib/ckan/venv/src/ckan/ckan/config/environment.py", line 116, in load_environment
    p.load_all()
  File "/usr/lib/ckan/venv/src/ckan/ckan/plugins/core.py", line 140, in load_all
    load(*plugins)
  File "/usr/lib/ckan/venv/src/ckan/ckan/plugins/core.py", line 168, in load
    plugins_update()
  File "/usr/lib/ckan/venv/src/ckan/ckan/plugins/core.py", line 122, in plugins_update
    environment.update_config()
  File "/usr/lib/ckan/venv/src/ckan/ckan/config/environment.py", line 292, in update_config
    plugin.configure(config)
  File "/usr/lib/ckan/venv/src/ckan/ckanext/datastore/plugin.py", line 80, in configure
    self.backend.configure(config)
  File "/usr/lib/ckan/venv/src/ckan/ckanext/datastore/backend/postgres.py", line 1645, in configure
    self.read_engine = self._get_read_engine()
  File "/usr/lib/ckan/venv/src/ckan/ckanext/datastore/backend/postgres.py", line 1543, in _get_read_engine
    return _get_engine_from_url(self.read_url)
  File "/usr/lib/ckan/venv/src/ckan/ckanext/datastore/backend/postgres.py", line 126, in _get_engine_from_url
    register_default_json(conn_or_curs=engine.raw_connection().connection,
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2177, in raw_connection
    self.pool.unique_connection, _connection)
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2151, in _wrap_pool_connect
    e, dialect, self)
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1465, in _handle_dbapi_exception_noconnection
    exc_info
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2147, in _wrap_pool_connect
    return fn()
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 328, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 766, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 516, in checkout
    rec = pool._do_get()
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 1138, in _do_get
    self._dec_overflow()
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 1135, in _do_get
    return self._create_connection()
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 333, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 461, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 651, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 105, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 393, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/lib/ckan/venv/local/lib/python2.7/site-packages/psycopg2/__init__.py", line 130, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  password authentication failed for user "datastore_ro"

I have not changed any password and I am sure they are correct.
Note that by using host all all all trust in pg_hba.conf everything starts correctly (of course I don't want to keep that - it was only to check whether it really was an authentication issue).

Did I need a different procedure during the database import/export to preserve the encrypted passwords? Or, has the encryption method changed from 2.8.0a to 2.8.3?

What steps can be taken to reproduce the issue?

What I did:

1. exported the v10 database (ckan 2.8.0a)
   docker-compose exec db pg_dumpall -U ckan > pgdump10.sql
2. changed all container versions in docker-compose.yml to the docker images for 2.8.3, and mapped the volume for the database to a new, empty folder
3. started a fresh db container based on 2.8.3
4. imported the old database:
   docker-compose exec -T db psql -U ckan < pgdump10.sql
5. started the ckan service:

[swingbit]

This could be related to #3985 and #3986, but my understanding is that those are fixed and 2.8.3 should contain those fixes.

[swingbit]

I have tried to enable md5 for local connections and then login from prompt:

psql -U ckan -d ckan

and

psql -U datastore_ro -d datastore

While the first logs in correctly with the right password (and refuses with a wrong password), the second does not accept the right password.