1

I am trying to update the USER auth model in Django in the middle of a project. I am also trying to learn a bit more, so I chose to do the "hard way" and switch out the standard auth model with a modified AbstractUserModel than doing the 'profile' 1-to-1 method often suggested. I feel I am quite close, as I am trying to apply the final migration with walkthrough suggestions from here and here. There is some inclination that Django runs the ALTER SEQUENCE OWNED BY with a different (auth user maybe?) user than the database user (who you are logged in as maybe?). I have confirmed that the OWNER of the tables and sequences are owned by all the same OWNER, but am still getting the migrate error. Thank you all in advance :)

When trying to run ./manage.py migrate --verbosity 3, I get the following error:

IN DEV XXXXXXX
Operations to perform:
  Apply all migrations: account, … trucks
Running pre-migrate handlers for application auth
…
Running pre-migrate handlers for application account
Running pre-migrate handlers for application trucks
Running migrations:
  Applying accounts.0004_auto_20220424_0024...Traceback (most recent call last):
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.ObjectNotInPrerequisiteState: sequence must have same owner as table it is linked to


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

Traceback (most recent call last):
  File "/Users/me/dev/PROJECT/./manage.py", line 22, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/core/management/base.py", line 89, in wrapped
    res = handle_func(*args, **kwargs)
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/core/management/commands/migrate.py", line 244, in handle
    post_migrate_state = executor.migrate(
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/db/migrations/executor.py", line 227, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/db/migrations/migration.py", line 126, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/db/migrations/operations/fields.py", line 244, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 608, in alter_field
    self._alter_field(model, old_field, new_field, old_type, new_type,
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/db/backends/postgresql/schema.py", line 196, in _alter_field
    super()._alter_field(
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 794, in _alter_field
    self.execute(sql, params)
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 145, in execute
    cursor.execute(sql, params)
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/Users/me/.virtualenvs/virtualENV/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.OperationalError: sequence must have same owner as table it is linked to

Django accounts Model:

from django.contrib.auth.models import AbstractUser
class User(AbstractUser):
    member_id = models.CharField(max_length=100, blank=True, default='')
    member_default_location = models.CharField(max_length=100, blank=True, default='1234')

Django accounts migration_0004:

# Generated by Django 3.2.12 on 2022-04-24 04:24
from django.db import migrations, models
class Migration(migrations.Migration):

    dependencies = [
        ('accounts', '0003_change_user_type'),
    ]

    operations = [
        migrations.AlterModelOptions(
            name='user',
            options={'verbose_name': 'user', 'verbose_name_plural': 'users'},
        ),
        migrations.AddField(
            model_name='user',
            name='member_default_location',
            field=models.CharField(blank=True, default='1234', max_length=100),
        ),
        migrations.AddField(
            model_name='user',
            name='member_id',
            field=models.CharField(blank=True, default='', max_length=100),
        ),
        migrations.AlterField(
            model_name='user',
            name='id',
            field=models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID'),
        ),
        migrations.AlterModelTable(
            name='user',
            table=None,
        ),
    ]

./manage.py sqlmigrate accounts 0004 yields:

IN DEV XXXXXXX
BEGIN;
--
-- Change Meta options on user
--
--
-- Add field square_member_default_location to user
--
ALTER TABLE "auth_user" ADD COLUMN "square_member_default_location" varchar(100) DEFAULT '6HKMHRR5AW5X8' NOT NULL;
ALTER TABLE "auth_user" ALTER COLUMN "square_member_default_location" DROP DEFAULT;
--
-- Add field square_member_id to user
--
ALTER TABLE "auth_user" ADD COLUMN "square_member_id" varchar(100) DEFAULT '' NOT NULL;
ALTER TABLE "auth_user" ALTER COLUMN "square_member_id" DROP DEFAULT;
--
-- Alter field id on user
--
SET CONSTRAINTS "auth_user_groups_user_id_6a12ed8b_fk_auth_user_id" IMMEDIATE; ALTER TABLE "auth_user_groups" DROP CONSTRAINT "auth_user_groups_user_id_6a12ed8b_fk_auth_user_id";
SET CONSTRAINTS "auth_user_user_permissions_user_id_a95ead1b_fk_auth_user_id" IMMEDIATE; ALTER TABLE "auth_user_user_permissions" DROP CONSTRAINT "auth_user_user_permissions_user_id_a95ead1b_fk_auth_user_id";
SET CONSTRAINTS "accounts_accountuser_user_id_ae8d4e71_fk_auth_user_id" IMMEDIATE; ALTER TABLE "accounts_accountuser" DROP CONSTRAINT "accounts_accountuser_user_id_ae8d4e71_fk_auth_user_id";
ALTER TABLE "auth_user" ALTER COLUMN "id" TYPE integer USING "id"::integer;
DROP SEQUENCE IF EXISTS "auth_user_id_seq" CASCADE;
CREATE SEQUENCE "auth_user_id_seq";
ALTER TABLE "auth_user" ALTER COLUMN "id" SET DEFAULT nextval('"auth_user_id_seq"');
SELECT setval('"auth_user_id_seq"', MAX("id")) FROM "auth_user";
ALTER SEQUENCE "auth_user_id_seq" OWNED BY "auth_user"."id";
ALTER TABLE "auth_user_groups" ALTER COLUMN "user_id" TYPE integer USING "user_id"::integer;
ALTER TABLE "auth_user_user_permissions" ALTER COLUMN "user_id" TYPE integer USING "user_id"::integer;
ALTER TABLE "accounts_accountuser" ALTER COLUMN "user_id" TYPE integer USING "user_id"::integer;
ALTER TABLE "accounts_accountuser" ADD CONSTRAINT "accounts_accountuser_user_id_ae8d4e71_fk" FOREIGN KEY ("user_id") REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED;
--
-- Rename table for user to (default)
--
ALTER TABLE "auth_user" RENAME TO "accounts_user";
ALTER TABLE "auth_user_groups" RENAME TO "accounts_user_groups";
ALTER TABLE "auth_user_user_permissions" RENAME TO "accounts_user_user_permissions";
COMMIT;

./manage.py sqlsequencereset accounts yields:

IN DEV XXXXXXX
BEGIN;
SELECT setval(pg_get_serial_sequence('"accounts_user_groups"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "accounts_user_groups";
SELECT setval(pg_get_serial_sequence('"accounts_user_user_permissions"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "accounts_user_user_permissions";
SELECT setval(pg_get_serial_sequence('"accounts_user"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "accounts_user";
SELECT setval(pg_get_serial_sequence('"accounts_accountuser"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "accounts_accountuser";
SELECT setval(pg_get_serial_sequence('"accounts_service"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "accounts_service";
SELECT setval(pg_get_serial_sequence('"accounts_account_services"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "accounts_account_services";
SELECT setval(pg_get_serial_sequence('"accounts_account"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "accounts_account";
SELECT setval(pg_get_serial_sequence('"accounts_vendor"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "accounts_vendor";
SELECT setval(pg_get_serial_sequence('"accounts_accountowner"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "accounts_accountowner";
SELECT setval(pg_get_serial_sequence('"accounts_usercookie"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "accounts_usercookie";
SELECT setval(pg_get_serial_sequence('"accounts_managercontact"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "accounts_managercontact";
SELECT setval(pg_get_serial_sequence('"accounts_settings"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "accounts_settings";
COMMIT;

Per this, I have confirmed that there is not a clearly visible mismatch between the show migrations and the actual database migrations.

./manage.py showmigrations yields:

…
accounts
 [X] 0001_initial
 [X] 0002_account_dormant
 [X] 0003_change_user_type
 [ ] 0004_auto_20220424_0024
…

Confirming in the 'django_migrations' table in the postrges db that the last migration was accounts__0003 - 782 "accounts". "0003_change_user_type". "2022-04-23 23:53:46.195421-04"

Running psql -U postgres , with the users via \du+:

                                          List of roles
 Role name |                         Attributes                         | Member of | Description 
-----------+------------------------------------------------------------+-----------+-------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        | 
 meme      | Superuser, Create role, Create DB                          | {}        |

and the sequences via \ds+:

                                                  List of relations
 Schema |                    Name                     |   Type   |  Owner   | Persistence |    Size    | Description 
--------+---------------------------------------------+----------+----------+-------------+------------+-------------
 …
 public | accounts_account_id_seq                     | sequence | postgres | permanent   | 8192 bytes | 
 public | accounts_account_service_id_seq             | sequence | postgres | permanent   | 8192 bytes | 
 …
 public | auth_group_id_seq                           | sequence | postgres | permanent   | 8192 bytes | 
 public | auth_group_permissions_id_seq               | sequence | postgres | permanent   | 8192 bytes | 
 public | auth_permission_id_seq                      | sequence | postgres | permanent   | 8192 bytes | 
 public | auth_user_groups_id_seq                     | sequence | postgres | permanent   | 8192 bytes | 
 public | auth_user_id_seq                            | sequence | postgres | permanent   | 8192 bytes | 
 public | auth_user_user_permissions_id_seq           | sequence | postgres | permanent   | 8192 bytes | 
 …

and the table information via \dt+:

                                                     List of relations
 Schema |                 Name                 | Type  |  Owner   | Persistence | Access method |    Size    | Description 
--------+--------------------------------------+-------+----------+-------------+---------------+------------+-------------
 …
 public | accounts_account                     | table | postgres | permanent   | heap          | 8192 bytes | 
 public | accounts_account_services            | table | postgres | permanent   | heap          | 8192 bytes | 
 public | accounts_accountowner                | table | postgres | permanent   | heap          | 8192 bytes | 
 public | accounts_accountuser                 | table | postgres | permanent   | heap          | 8192 bytes | 
 …
 public | auth_group                           | table | postgres | permanent   | heap          | 8192 bytes | 
 public | auth_group_permissions               | table | postgres | permanent   | heap          | 8192 bytes | 
 public | auth_permission                      | table | postgres | permanent   | heap          | 104 kB     | 
 public | auth_user                            | table | postgres | permanent   | heap          | 16 kB      | 
 …

and the privelages via \dp+:

                                                 Access privileges
 Schema |                    Name                     |   Type   | Access privileges | Column privileges | Policies 
--------+---------------------------------------------+----------+-------------------+-------------------+----------
 public | account_emailaddress                        | table    |                   |                   | 
 public | account_emailaddress_id_seq                 | sequence |                   |                   | 
 public | account_emailconfirmation                   | table    |                   |                   | 
 public | account_emailconfirmation_id_seq            | sequence |                   |                   | 
 public | accounts_account                            | table    |                   |                   | 
 public | accounts_account_id_seq                     | sequence |                   |                   | 
 …
 public | auth_user                                   | table    |                   |                   | 
 public | auth_user_groups                            | table    |                   |                   | 
 public | auth_user_groups_id_seq                     | sequence |                   |                   | 
 public | auth_user_id_seq                            | sequence |                   |                   | 
 …

The method failing in django > db > utils is:

def _execute(self, sql, params, *ignored_wrapper_args):
    self.db.validate_no_broken_transaction()
    with self.db.wrap_database_errors:
        if params is None:
            # params default might be backend specific.
            return self.cursor.execute(sql)
        else:
            return self.cursor.execute(sql, params)

and the failing sequence is : ALTER SEQUENCE "auth_user_id_seq" OWNED BY "auth_user"."id" with an empty list for params

Versions:

  • Python 3.9.9
  • Django==3.2.12
  • psql (14.2)
chris Frisina
  • 19,086
  • 22
  • 87
  • 167
  • Hello Chris, I am getting same error wit h same django and Psql versions. Did you find a solution for this owner issue? – soduncu Oct 17 '22 at 14:45

1 Answers1

2

For Local development, with a simple architecture (ie no multiple deployments or servers), be sure to check your postgres connection in the settings.py file. With a blank parameter, I guess Django loads a different or default postgres user, disregarding privileges and checks, despite prior confirmation as noted in question.

Change :

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'mst_db',
        'USER': '',
        'PASSWORD': '',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

to:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'mst_db',  
        'USER': 'postgres', # This was required
        'PASSWORD': '',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}
chris Frisina
  • 19,086
  • 22
  • 87
  • 167