Heroku CI does not work with in-dyno databases

[ddahan]

When running Heroku CI with in-dyno Postgres database, I got a django.db.utils.ProgrammingError: role "postgres" does not exist error.

Here is my app.json

{
  "buildpacks": [
    {
      "url": "heroku/python"
    }
  ],
  "environments": {
    "test": {
      "formation": {
        "test": {
          "quantity": 1,
          "size": "performance-m"
        }
      },
      "scripts": {
        "test": "flake8 fugoproj && python fugoproj/manage.py test fugoproj"
      },
      "addons": [
        "heroku-postgresql:in-dyno"
      ]
    }
  }
}

Here is the heroku test setup log (succeed)

-----> Fetching https://github.com/heroku/heroku-buildpack-ci-postgresql buildpack...
       buildpack downloaded
-----> Fetching heroku/python buildpack...
       buildpack downloaded
-----> postgresql app detected
       Using postgresql version: 10.1
       Using stack version: heroku-16
-----> Fetching and vendoring postgresql into slug
-----> Initializing database
       The files belonging to this database system will be owned by user "u36536".
       This user must also own the server process.

       The database cluster will be initialized with locale "en_US.UTF-8".
       The default text search configuration will be set to "english".

       Data page checksums are disabled.

       creating directory /app/vendor/postgresql/data ... ok
       creating subdirectories ... ok
       selecting default max_connections ... 100
       selecting default shared_buffers ... 128MB
       selecting dynamic shared memory implementation ... posix
       creating configuration files ... ok
       running bootstrap script ... ok
       performing post-bootstrap initialization ... ok
       syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

       Success. You can now start the database server using:

           pg_ctl -D /app/vendor/postgresql/data -l logfile start

       waiting for server to start.... done
       server started
       CREATE ROLE
-----> Copying .profile.d/pg.sh to add postgresql binaries to PATH
-----> postgresql done
-----> Python app detected
       Skipping installation, as Pipfile.lock hasn't changed since last deploy.
-----> No test-setup command provided. Skipping.

Here is the heroku test log (fails)

-----> Running test command `flake8 fugoproj && python fugoproj/manage.py test fugoproj`...
/app/.heroku/python/lib/python3.6/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)
Using existing test database for alias 'default'...
System check identified no issues (0 silenced).
..........................................................................................................................................................................................................................................................................................................................
----------------------------------------------------------------------
Ran 314 tests in 50.910s
OK
Traceback (most recent call last):
  File "/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
psycopg2.ProgrammingError: role "postgres" does not exist
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "fugoproj/manage.py", line 22, in <module>
    execute_from_command_line(sys.argv)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/core/management/__init__.py", line 364, in execute_from_command_line
    utility.execute()
  File "/app/.heroku/python/lib/python3.6/site-packages/django/core/management/__init__.py", line 356, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/core/management/commands/test.py", line 29, in run_from_argv
    super(Command, self).run_from_argv(argv)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/core/management/commands/test.py", line 62, in handle
    failures = test_runner.run_tests(test_labels)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/test/runner.py", line 604, in run_tests
    self.teardown_databases(old_config)
  File "/app/.heroku/python/lib/python3.6/site-packages/django_heroku/core.py", line 41, in teardown_databases
    self._wipe_tables(connection)
  File "/app/.heroku/python/lib/python3.6/site-packages/django_heroku/core.py", line 33, in _wipe_tables
    """
  File "/app/.heroku/python/lib/python3.6/site-packages/raven/contrib/django/client.py", line 127, in execute
    return real_execute(self, sql, params)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
django.db.utils.ProgrammingError: role "postgres" does not exist
-----> test command `flake8 fugoproj && python fugoproj/manage.py test fugoproj` failed with exit status 1

Additional note

• I'm configuring DATABASE with ssl_require=False, otherwise it does not work at all.

[nilsabdi]

Sadly running into the same issue here. Also got ssl_require=False..

[sigmavirus24]

Seems this is due to this method: https://github.com/heroku/django-heroku/blob/433ac4b0b82fa41ee88c2bb501874a49b3aff5fc/django_heroku/core.py#L24

We'll need to find a better way to remove all the tables from the public schema for test teardown

[sigmavirus24]

An alternative would be to more simply do

DROP TABLE (SELECT * FROM information_schema.tables WHERE table_schema = 'public') CASCADE;

[iMerica]

Issue persists. Please re-open the ticket.

[iMerica]

The teardown logic causes test failures even though all tests pass.

Ran 33 tests in 1.945s
OK
Traceback (most recent call last):
  File "/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py", line 83, in _execute
    return self.cursor.execute(sql)
psycopg2.ProgrammingError: role "postgres" does not exist
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "manage.py", line 15, in <module>
    execute_from_command_line(sys.argv)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/app/.heroku/python/lib/python3.6/site-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/core/management/commands/test.py", line 26, in run_from_argv
    super().run_from_argv(argv)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/core/management/base.py", line 316, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/core/management/base.py", line 353, in execute
    output = self.handle(*args, **options)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/core/management/commands/test.py", line 56, in handle
    failures = test_runner.run_tests(test_labels)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/test/runner.py", line 607, in run_tests
    self.teardown_databases(old_config)
  File "/app/.heroku/python/lib/python3.6/site-packages/django_heroku/core.py", line 41, in teardown_databases
    self._wipe_tables(connection)
  File "/app/.heroku/python/lib/python3.6/site-packages/django_heroku/core.py", line 33, in _wipe_tables
    """
  File "/app/.heroku/python/lib/python3.6/site-packages/raven/contrib/django/client.py", line 127, in execute
    return real_execute(self, sql, params)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/app/.heroku/python/lib/python3.6/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py", line 83, in _execute
    return self.cursor.execute(sql)
django.db.utils.ProgrammingError: role "postgres" does not exist
-----> test command `python manage.py test` failed with exit status 1

[iMerica]

cc/ @sigmavirus24 .

Please note: I'm already paying $10 per-month-per-pipeline for this service and it does not work. I've followed the official docs and used some of the hacks on various gists .... no luck.

Meanwhile Gitlab and Circle CI are both free and work really well.

[sigmavirus24]

@iMerica are you using the project from source or installing from PyPI? I'm not certain this fix was ever released. Testing from source would be best right now as it may unblock you and you can help verify this fixes the issue.

[bwilliams18]

I'm currently experiencing this issue – has anyone found a solution?

[danielsousaio]

@bwilliams18 This seems to be fixed on master, following @sigmavirus24 example, but not released:

An alternative would be to more simply do

DROP TABLE (SELECT * FROM information_schema.tables WHERE table_schema = 'public') CASCADE;

Check PR #20

Pypi releases don't include that: https://pypi.org/project/django-heroku/#history

[kyleconroy]

Why are the tables being dropped at all? In-dyno databases are ephemeral

[petarnikolovski]

Is this resolved? I'm using the latest version of the library, and I still get the same error.

[ChrisBurch]

@sigmavirus24 could you please cut a new release with this fix? It's a tease knowing this issue was fixed almost 9 months ago, but not having access to it through PyPI :-)

[the-mace]

@bwilliams18 This seems to be fixed on master, following @sigmavirus24 example, but not released:

An alternative would be to more simply do

DROP TABLE (SELECT * FROM information_schema.tables WHERE table_schema = 'public') CASCADE;

Check PR #20

Pypi releases don't include that: https://pypi.org/project/django-heroku/#history

Even pulling from master with the change from PR #20 in it, it does not work on the Heroku CI:

OK (skipped=2)
Traceback (most recent call last):
  File "/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.SyntaxError: syntax error at or near "("
LINE 2:                     DROP TABLE (
                                       ^

Any other suggestions?

[the-mace]

For those looking for a workaround I did this:

1) Add to settings.py: TEST_RUNNER = 'app.test_runner.NoDbTearDownTestRunner'

2) Create file app/test_runner.py with:

from django.test.runner import DiscoverRunner
import os

class NoDbTearDownTestRunner(DiscoverRunner):
    def teardown_databases(self, old_config, **kwargs):
        """ Override the database teardown defined in parent class if needed """
        if not os.environ.get("APP_SKIP_DB_TEARDOWN"):
            super().teardown_databases(old_config, **kwargs)

3) In Heroku CI config set APP_SKIP_DB_TEARDOWN = True

This will stop Heroku CI from doing a DB teardown at the end of the tests (which breaks despite PR #20 etc) but will leave everything else as before.

[YPCrumble]

This issue should be reopened for a couple reasons:

1. The require_ssl configuration fixed by #43 (though I have been unable to get that PR to work fully) raises an error.
2. If there are multiple apps, the test hang on the teardown as discussed in comments above, though I haven't been able to get any of the workarounds to work.

My current workaround is to add this below my django-heroku config to use SQLite for my CI environment only:

# TODO: This is a hack while waiting for
# https://github.com/heroku/django-heroku/issues/17
# to be resolved.
if os.environ.get('CI'):
    DATABASES['default'] = {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': 'HerokuCI'
    }