Django.db.utils.OperationalError: последовательность должна иметь того же владельца, что и таблица, с которой она связана
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 :)
При попытке запустить ./manage.py migrate --verbosity 3, я получаю следующую ошибку:
Модель учетных записей в Django:
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_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 выдает:
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 выдает:
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;
После этого я подтвердил, что нет четко видимого несоответствия между показанными миграциями и фактическими миграциями базы данных.
./manage.py showmigrations выдает:
…
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 | {} |
и последовательности через \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 |
…
и информация о таблице через \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 |
…
и привилегии через \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 | | |
…
Для локальной разработки, с простой архитектурой (т.е. без нескольких развертываний или серверов), обязательно проверьте подключение к postgres в файле settings.py. С пустым параметром, я полагаю, Django загружает другого пользователя postgres или пользователя по умолчанию, не обращая внимания на привилегии и проверки, несмотря на предварительное подтверждение, как отмечено в вопросе.
Изменение :
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',
}
}