Ownership mismatch on postgresql when multiple roles are writing to the same partitioned table

[nmandery]

This is a situation which will arise when multiple database roles are writing to a postgresql database. Lets assume the following situation:

• Role A: This is the role used to manage the schema and is also used to create the paritioning triggers using architect.
• Role B: Is used to write data to the partitioned tables. The role has been granted write permissions to the parent table and the relevant sequences for the primary key. The permissions to the child tables are automatically granted by postgresqls table inheritance when they ar accessed via the parent table.

Now role B inserts data requiring the creation of a new partition. The insert trigger is executed but fails with the following error:

architect_test=> insert into testapp_gl (content, dt) values ('d', now());
ERROR:  must be owner of relation testapp_gl
CONTEXT:  SQL statement "CREATE TABLE testapp_gl_y2018m01 (
                                CHECK ("dt" >= '2018-01-01 00:00:00+01' AND "dt" < '2018-02-01 00:00:00+01'),
                                LIKE "testapp_gl" INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES
                            ) INHERITS ("testapp_gl");"
PL/pgSQL function testapp_gl_insert_child() line 21 at EXECUTE

So only Role A, the owner of the table, is able to create partitions.

This PR resolves this issue by making use of the SECURITY DEFINER keyword (see CREATE FUNCTION when creating the insert trigger. This keyword works a bit like the UNIX setuid flag and allows executing functions with the permissions of the role which did define the function. I think it can be assumed that the role which manages the table schema and the role which creates the partitioning triggers is the same.

The SECURITY DEFINER keyword seems to be supported starting with Postgresql 7.3, so this PR will not break a resonable backward compatibility. I also do not see any security implications as the permission set on the parent table still apply.

[maxtepkeev]

Cool, looks great, thx, tests are failing but it looks like it's because of some changes in pymysql that break Python 2.6 support, so I'll have a look at it separately.

[nmandery]

Great - thanks for the merge.