Debuggers,
Having an interesting issue with RESET ROLE. So far it's defied my
ability to come up with a simpler test case so that I can diagnose the
issue. Here's the situ:
1. plpgsql function create_data_tables() creates a bunch of tables.
2. as part of this, it uses SET ROLE to a role the calling user is a
member of ("dbowner").
3. create_data_tables then calls create_partitions() to partition the
newly created tables, in a loop.
4. create_partitions() also does SET ROLE dbowner, and RESET ROLE at the
end of the function.
5. If called, on the ~~ 8th iteration of create_partitions(), it has a
permissions problem: "must be owner of table new_master_table"
6. If I remove the SET/RESET from the create_partitions function, it
works fine.
Anyway, I can't imagine how you could diagnose this without a valid test
case, and my attempts to construct a very simple reproduceable one so
far have met with failure. So I'm filing this bug *in case* we see other
issues with repetitive RESET ROLE calls in the future.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
