Re: BUG #13809: Reassign owned throws error

alexander(dot)ashurkov(at)gmail(dot)com wrote:

> Hello,
> when I execute "reassign owned by user to user_role" command (even with
> 'postgres' user) postgresql outputs this message:
> "ERROR: unexpected classid 1418
> SQL state: XX000"
>
> My DB has postgresql FDW (it owned by 'postgres' user as it should be), one
> foreign server (owned by 'user_role') and two user mappings for 'user' and
> 'user_role'.
>
> 'user' is member of 'user_role' ('GRANT user_role TO user').
>
> Postgresql installed from "deb http://apt.postgresql.org/pub/repos/apt/
> trusty-pgdg main" repo.

Interesting, thanks. I think this should be dealt with just like
default ACLs: REASSIGN OWNED should just ignore the USER MAPPING
objects (because they aren't "owned" as such, but they go together with
the role itself), and they would be removed by DROP OWNED. So the
attached patch would fix it.

One could instead consider that this needs to be fixed by creating a
user mapping for the receiving user instead. But this seems way too
complicated: in particular, if such a user mapping already exists, we
would have to instead do nothing. I think that would run counter to the
current simplicity of REASSIGN OWNED.

This is not a for-commit patch; I need to verify what happens with FDWs
and foreign servers and such. FDWs and servers *are* owned, so fixing
those would require a different patch.

My current assumption is that the whole FDW machinery just didn't
account for REASSIGN OWNED at all.

Also, need to put some test cases somewhere.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services