Issue with Inheritance and Partitioning and grants

From: Maxim Boguk <mboguk(at)masterhost(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Subject: Issue with Inheritance and Partitioning and grants
Date: 2009-06-02 11:58:28
Message-ID: 4A2513E4.8060207@masterhost.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have one large table Partitioned via timestamp on monthly parts.
And i use Inheritance on it.

So structure look like:
Parent table:
entity_log
...

And lots child tables like:
entity_log_2002_01
...
Check constraints:
"chk_entity_log_2002_01" CHECK (ctime >= '2002-01-01 00:00:00'::timestamp without time zone AND ctime < '2002-02-01 00:00:00'::timestamp without
time zone)
Inherits: entity_log

... and so on
entity_log_2009_06
...

Business logic required past months entries must by read only. Do it on application side look impossible (way too many old spaghetti code).
So after some think about alternatives i choose do it via limiting grants on past months tables.

So i removed write access from application user on all old entity_log_* tables (on test system).
And testers found unexpected bug:

12:15:18 MSD bill(at)billing 67842 ERROR: permission denied for relation entity_log_2002_01
12:15:18 MSD bill(at)billing 67842 STATEMENT: UPDATE entity_log SET usermsg = 'some text' WHERE id = 46248962

really entry with id=46248962 located in entity_log_2009_06 table where write grants set (e.g. only entity_log_2009_06 table need be updated).

I not sure are it is bug or intentional. Probably would be good first check where entries to update located and check grants only after (I not sure
about possibility learn PostgreSQL do such tricks).

I really look for some working solution of my problem. I cannot solve this issue via adding 'on update' trigger on entity_log table.

--
SY, Maxim Boguk

Browse pgsql-general by date

  From Date Subject
Next Message Václav Kyselý 2009-06-02 12:11:28 New install Error : FATAL: could not create lock file "postmaster.pid"
Previous Message Isak Hansen 2009-06-02 09:43:19 Re: How can I manually alter the statistics for a column?