search

mathiasertl / django-ca

Django app providing a Certificate Authority
GNU General Public License v3.0
145 stars 43 forks source link

`django-ca migrate` from the source installation fails #151

Closed theseal closed 4 days ago

theseal commented 2 months ago

The the source installation guide states that the databases should be migrated with django-ca migrate.

That command fails with the following output:

❯ django-ca migrate

Operations to perform:

  Apply all migrations: admin, auth, contenttypes, django_ca, sessions

Running migrations:

Traceback (most recent call last):

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 103, in _execute

    return self.cursor.execute(sql)

           ^^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/django-ca/venv/lib/python3.11/site-packages/psycopg/cursor.py", line 97, in execute

    raise ex.with_traceback(None)

psycopg.errors.InsufficientPrivilege: permission denied for schema public

LINE 1: CREATE TABLE "django_migrations" ("id" bigint NOT NULL PRIMA...

                     ^

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/migrations/recorder.py", line 78, in ensure_schema

    editor.create_model(self.Migration)

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/backends/base/schema.py", line 505, in create_model

    self.execute(sql, params or None)

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/backends/postgresql/schema.py", line 45, in execute

    return super().execute(sql, params)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/backends/base/schema.py", line 202, in execute

    cursor.execute(sql, params)

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 79, in execute

    return self._execute_with_wrappers(

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers

    return executor(sql, params, many, context)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 100, in _execute

    with self.db.wrap_database_errors:

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__

    raise dj_exc_value.with_traceback(traceback) from exc_value

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 103, in _execute

    return self.cursor.execute(sql)

           ^^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/django-ca/venv/lib/python3.11/site-packages/psycopg/cursor.py", line 97, in execute

    raise ex.with_traceback(None)

django.db.utils.ProgrammingError: permission denied for schema public

LINE 1: CREATE TABLE "django_migrations" ("id" bigint NOT NULL PRIMA...

                     ^

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

  File "/opt/django-ca/src/django-ca/ca/manage.py", line 26, in <module>

    execute_from_command_line(sys.argv)

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line

    utility.execute()

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/core/management/__init__.py", line 436, in execute

    self.fetch_command(subcommand).run_from_argv(self.argv)

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/core/management/base.py", line 413, in run_from_argv

    self.execute(*args, **cmd_options)

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/core/management/base.py", line 459, in execute

    output = self.handle(*args, **options)

             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/core/management/base.py", line 107, in wrapper

    res = handle_func(*args, **kwargs)

          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/core/management/commands/migrate.py", line 357, in handle

    post_migrate_state = executor.migrate(

                         ^^^^^^^^^^^^^^^^^

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/migrations/executor.py", line 107, in migrate

    self.recorder.ensure_schema()

  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/migrations/recorder.py", line 80, in ensure_schema

    raise MigrationSchemaMissing(

django.db.migrations.exceptions.MigrationSchemaMissing: Unable to create the django_migrations table (permission denied for schema public

LINE 1: CREATE TABLE "django_migrations" ("id" bigint NOT NULL PRIMA...

                     ^)

Workaround which we came up with was to set the table owner in the database with:

❯ sudo -u postgres psql

psql (15.8 (Debian 15.8-0+deb12u1))

Type "help" for help.

postgres=# ALTER DATABASE django_ca OWNER TO django_ca;

ALTER DATABASE

… but I'm no postgres guy so not sure if this correct or best practice.

mathiasertl commented 2 months ago

Hi!

In general you need to be able to change the schema to run migrate. It's actually the principal point of that command.

The guide gives this:

sudo -u postgres psql
postgres=# CREATE DATABASE django_ca;
CREATE DATABASE
postgres=# CREATE USER django_ca WITH ENCRYPTED PASSWORD 'random-password';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE django_ca TO django_ca;
GRANT

This should give that user sufficient privilege (I hope "ALL" is enough 😉). Are sure you have done this?

Kr, Mat

theseal commented 2 months ago

Yes! You made me uncertain but redid the installation and the issue still persist

❯ sudo -u postgres psql
psql (15.8 (Debian 15.8-0+deb12u1))
Type "help" for help.

postgres=# CREATE DATABASE django_ca;
CREATE DATABASE
postgres=# CREATE USER django_ca WITH ENCRYPTED PASSWORD 'YoqzHjIOzWhHWuSbkp8Od/nFtUro36F0DV9crL40nCA=';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE django_ca TO django_ca;
GRANT
❯ django-ca migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, django_ca, sessions
Running migrations:
Traceback (most recent call last):
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/django-ca/venv/lib/python3.11/site-packages/psycopg/cursor.py", line 97, in execute
    raise ex.with_traceback(None)
psycopg.errors.InsufficientPrivilege: permission denied for schema public
LINE 1: CREATE TABLE "django_migrations" ("id" bigint NOT NULL PRIMA...
                     ^

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/migrations/recorder.py", line 78, in ensure_schema
    editor.create_model(self.Migration)
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/backends/base/schema.py", line 505, in create_model
    self.execute(sql, params or None)
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/backends/postgresql/schema.py", line 45, in execute
    return super().execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/backends/base/schema.py", line 202, in execute
    cursor.execute(sql, params)
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 79, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/django-ca/venv/lib/python3.11/site-packages/psycopg/cursor.py", line 97, in execute
    raise ex.with_traceback(None)
django.db.utils.ProgrammingError: permission denied for schema public
LINE 1: CREATE TABLE "django_migrations" ("id" bigint NOT NULL PRIMA...
                     ^

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/opt/django-ca/src/django-ca/ca/manage.py", line 26, in <module>
    execute_from_command_line(sys.argv)
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/core/management/base.py", line 413, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/core/management/base.py", line 459, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/core/management/base.py", line 107, in wrapper
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/core/management/commands/migrate.py", line 357, in handle
    post_migrate_state = executor.migrate(
                         ^^^^^^^^^^^^^^^^^
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/migrations/executor.py", line 107, in migrate
    self.recorder.ensure_schema()
  File "/opt/django-ca/venv/lib/python3.11/site-packages/django/db/migrations/recorder.py", line 80, in ensure_schema
    raise MigrationSchemaMissing(
django.db.migrations.exceptions.MigrationSchemaMissing: Unable to create the django_migrations table (permission denied for schema public
LINE 1: CREATE TABLE "django_migrations" ("id" bigint NOT NULL PRIMA...
                     ^)
mathiasertl commented 2 months ago

I'm sorry, but I cannot reproduce this. I just tried this out with a fresh PostgreSQL installation, and it worked like a charm.

Either there is something wrong with your permissions or your database configuration.

mathiasertl commented 2 months ago

The issue could also be caused by your setup not loading the configuration files you think it should be loading or you somehow ending up with a configuration you're not expecting. You can simply find this out with running django-ca shell and then run:

>>> import pprint
>>> from django.conf import settings
>>> pprint.pprint(settings.SETTINGS_FILES)
>>> pprint.pprint(settings.DATABASES)

... other then that, I'm not really sure how to further assist.

SpaceFarmer commented 1 month ago

We started over with a fresh ubuntu24 machine and followed the install guide https://django-ca.readthedocs.io/en/latest/quickstart/from_source.html but used the main branch and it failed with the same error as above. This is the output from the django-ca shell before we ran the migrate command:

/opt/django-ca/src ❯ django-ca shell
Python 3.12.3 (main, Sep 11 2024, 14:17:37) [GCC 13.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> import pprint
>>> from django.conf import settings
>>> pprint.pprint(settings.SETTINGS_FILES)
(PosixPath('/etc/django-ca/00-settings.yaml'),
 PosixPath('/etc/django-ca/10-localsettings.yaml'))
>>> pprint.pprint(settings.DATABASES)
{'default': {'ATOMIC_REQUESTS': False,
             'AUTOCOMMIT': True,
             'CONN_HEALTH_CHECKS': False,
             'CONN_MAX_AGE': 0,
             'ENGINE': 'django.db.backends.postgresql',
             'HOST': 'localhost',
             'NAME': 'django_ca',
             'OPTIONS': {},
             'PASSWORD': 'random-password',
             'PORT': 5432,
             'TEST': {'CHARSET': None,
                      'COLLATION': None,
                      'MIGRATE': True,
                      'MIRROR': None,
                      'NAME': None},
             'TIME_ZONE': None,
             'USER': 'django_ca'}}
mathiasertl commented 1 month ago

🤷‍♀️ If you get the correct configuration, it must be a PostgreSQL permission issue. Please consult their docs.

Also Feedback anything that's missing, please 😉