Re: Changing ownership of simple composite incomplete?

Jerry Sievers <gsievers19(at)comcast(dot)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(dot)consulting(at)comcast(dot)net
p: 312.241.7800