Обсуждение: Changing ownership of simple composite incomplete?
Hackers; I spotted this after doing some owner reassignments and then dropping the old roles. It looks like using either reassign or alter type owner to, the pg_class entry stays with old rolID. Then if we drop that old role going forward pg_dump complains about invalid owner. See below test case. I did look at releast notes above 9.4 and didn't notice a fix. I observed the problem originally on a 9.1 system here. And the warning is a bit confusing since it says "table" rather than type. FYI Thanks $ cat q \set ON_ERROR_STOP begin; select version(); create role foo; create schema foo; set search_path to foo; prepare foo as select c.relowner, t.typowner from pg_class c join pg_type t on typrelid = c.oid and typname = 'foo'; create type foo as (a int); execute foo; alter type foo owner to foo; execute foo; reassign owned by foo to postgres; drop role foo; execute foo; alter type foo owner to postgres; execute foo; commit; \! pg_dump --schema-only --schema foo >/dev/null \echo '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n\n\n' drop schema foo cascade; $ psql -fq SET BEGIN version ----------------------------------------------------------------------------------------------PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu,compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit (1 row) CREATE ROLE CREATE SCHEMA SET PREPARE CREATE TYPErelowner | typowner ----------+---------- 16387 | 16387 (1 row) ALTER TYPErelowner | typowner ----------+---------- 266324 | 266324 (1 row) REASSIGN OWNED DROP ROLErelowner | typowner ----------+---------- 266324 | 10 (1 row) ALTER TYPErelowner | typowner ----------+---------- 266324 | 10 (1 row) COMMIT pg_dump: WARNING: owner of table "foo" appears to be invalid ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ psql:q:39: NOTICE: drop cascades to type foo DROP SCHEMA $ $ -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
Jerry Sievers <gsievers19@comcast.net> writes: > Hackers; > > I spotted this after doing some owner reassignments and then dropping > the old roles. > > It looks like using either reassign or alter type owner to, the pg_class > entry stays with old rolID. > > Then if we drop that old role going forward pg_dump complains about > invalid owner. > > See below test case. I did look at releast notes above 9.4 and didn't > notice a fix. I observed the problem originally on a 9.1 system here. Oop! I looked at release notes above 9.3.4... FYI > > And the warning is a bit confusing since it says "table" rather than type. > > FYI Thanks > > > > > > $ cat q > \set ON_ERROR_STOP > > begin; > > select version(); > > create role foo; > create schema foo; > set search_path to foo; > > prepare foo as > select c.relowner, t.typowner > from pg_class c > join pg_type t on typrelid = c.oid > and typname = 'foo'; > > create type foo as (a int); > > execute foo; > > alter type foo owner to foo; > > execute foo; > > reassign owned by foo to postgres; > drop role foo; > > execute foo; > > alter type foo owner to postgres; > > execute foo; > > commit; > > \! pg_dump --schema-only --schema foo >/dev/null > \echo '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n\n\n' > > drop schema foo cascade; > > > > > $ psql -fq > SET > BEGIN > version > ---------------------------------------------------------------------------------------------- > PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit > (1 row) > > CREATE ROLE > CREATE SCHEMA > SET > PREPARE > CREATE TYPE > relowner | typowner > ----------+---------- > 16387 | 16387 > (1 row) > > ALTER TYPE > relowner | typowner > ----------+---------- > 266324 | 266324 > (1 row) > > REASSIGN OWNED > DROP ROLE > relowner | typowner > ----------+---------- > 266324 | 10 > (1 row) > > ALTER TYPE > relowner | typowner > ----------+---------- > 266324 | 10 > (1 row) > > COMMIT > pg_dump: WARNING: owner of table "foo" appears to be invalid > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ > > > > psql:q:39: NOTICE: drop cascades to type foo > DROP SCHEMA > > $ $ -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800