Partition Permissions

One of the interesting side effects of PostgreSQL’s table partitioning implementation is that all of the partitions can be treated as individual tables, with thier own distinct characteristics, such as different indexes and tablespaces. In some cases this is a feature, in other cases it’s a pain in the neck, such as 99% of the time with permissions. An all too common scenario I see with our clients is a situation where someone will create partitioned tables and then grant permissions to only the parent table, forgetting (or just not knowing) that the child tables need individual permission grants as well. Another version of this is where someone adds a new user to a database, and maybe even goes so far as to update some partitions, or maybe all current partitions, but doesn’t set up a mechanism to handle future partitions. In cases like these, you need to adjust your maintenence routines to make sure to add permissions as new partitions are created, but you also have to clean up any old partitions that may not have the proper permissions granted. The first part of that will be pretty implementation specific, but to help with the second part, you can use the following sql statement to find missing table permissions.


pagila=# select relname, relacl from pg_class where relname ~ 'payment' and

pagila-# relkind = 'r' and relname not in

pagila-# (select relname from pg_class where array_to_string(relacl,',') ~ 'robert');

     relname      |                    relacl

------------------+----------------------------------------------

 payment          | {postgres=arwdRxt/postgres,dylan=r/postgres}

 payment_p2007_02 | {postgres=arwdRxt/postgres,dylan=r/postgres}

 payment_p2007_03 | {postgres=arwdRxt/postgres,dylan=r/postgres}

 payment_p2007_05 | {postgres=arwdRxt/postgres,dylan=r/postgres}

(4 rows)

The above looks at every partition of the payment table to see if permissions have been granted to a user named “robert”, showing you the partition name and the current permissions on the table. You can use this query as the basis for all kinds of intricate searches, and combined with some sql based sql generation, you can quickly fix up your existing partition permissions. Just don’t forget to automate the process for future partitions!