References: <20200302185904.GA6528@panix.com>
 <30c3c06e-61fa-3210-666d-1696af320a75@aklaver.com>
 <20200302202829.GA19161@panix.com>
User-agent: mu4e 1.3.9; emacs 27.0.90
From: Tim Cross <theophilusx@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Detecting which columns a query will modify in a function
 called by a trigger
In-reply-to: <20200302202829.GA19161@panix.com>
Message-ID: <871rqatrae.fsf@gmail.com>
Date: Tue, 03 Mar 2020 07:50:33 +1100
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk


stan <stanb@panix.com> writes:

> On Mon, Mar 02, 2020 at 11:02:54AM -0800, Adrian Klaver wrote:
>> On 3/2/20 10:59 AM, stan wrote:
>> > I need to implement a fairly fine grained security model. Probably a bit
>> > finer that I can do with the standard ownership functionality.
>> >
>> > My thinking on this is to create a table that contains the users, and a
>> > "permission bit" for each function that they may want to do, vis a vi
>> > altering an existing row,or rows, or inserting new rows.
>> >
>> > Looks relatively straight forward, if fairly time consuming to do. But I
>> > would need to know which column(s) a given query would add..alter from the
>> > function to implement this via a trigger. looks like I see most of what I
>> > need t do this in the docs, but I can't quite figure out if I can get this
>> > down to what column(s) a given trigger will modify. Is this possible?
>>
>> Before you get too far into this I would look at RLS:
>>
>> https://www.postgresql.org/docs/12/ddl-rowsecurity.html
>>
> Thanks for pointing that out.
>
> Using that functionality was my original plan, but let me describe why I do not think it
> can do what I need. This may be an indication of my weakness in design
> though.
>
> Envision a table with a good many columns. This table represents the "life
> history" of a part on a project. Some of the columns need to be
> created/modified by the engineer. Some need to be created/modified by the
> purchasing agent, some of the columns need to be created by the receiving
> department, some of the columns need to be created/modified by the accounts
> payable department.
>
> Make sense?

When you speak of columns needing to be created/modified, do you really
mean columns or rows? It would be a very unusual approach to allow
multiple different 'agencies' to create/modify underlying table design.
If this is the case, then you are in an impossible position and have no
hope of implementing anything that will be maintainable and you will
never be able to manage security.

I'm hoping you mean different agencies which need to add/modify rows
wihtin the tables?

--
Tim Cross