BUG #13809: Reassign owned throws error
The following bug has been logged on the website:
Bug reference: 13809
Logged by: Alexander Ashurkov
Email address: alexander.ashurkov@gmail.com
PostgreSQL version: 9.4.5
Operating system: Linux 3.19.0-31-generic #36~14.04.1-Ubuntu SMP Thu
Description:
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.
With version 9.4.4 it worked fine. Do I miss something or it is a bug?
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
alexander.ashurkov@gmail.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
Attachments:
reassign-user-mapping.patchtext/x-diff; charset=us-asciiDownload+2-0
Alvaro, I compiled postgres with your patch on test machine and copied my
DB there, now reassign command works fine!
Alexander Ashurkov wrote:
Alvaro, I compiled postgres with your patch on test machine and copied my
DB there, now reassign command works fine!
Thanks for the followup. I just pushed this patch.
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs