Thread: Detecting which columns a query will modify in a function called bya trigger
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?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
Re: Detecting which columns a query will modify in a function calledby a trigger
From
Adrian Klaver
Date:
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 > > -- Adrian Klaver adrian.klaver@aklaver.com
Re: Detecting which columns a query will modify in a function calledby a trigger
From
Pavel Stehule
Date:
po 2. 3. 2020 v 19:59 odesílatel stan <stanb@panix.com> napsal:
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?
You can compare NEW and OLD record. It is pretty hard in PLpgSQL, but easy with hstore or jsonb, or with PLPerl or PLPythonu
Regards
Pavel
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
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? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Detecting which columns a query will modify in a function calledby a trigger
From
"David G. Johnston"
Date:
On Mon, Mar 2, 2020 at 1:28 PM stan <stanb@panix.com> wrote:
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?
On a theory level this design is insufficiently normalized. The fact that you are having issues and challenges working with it suggests you should seriously consider a different design, one that exhibits better normalization properties.
Alternatively you might consider just removing direct access to the table and provide views and/or functions that can use normal permission grants. Add some check constraints to the table to describe and enforce the inter-field relationships that are present.
David J.
Re: Detecting which columns a query will modify in a functioncalled by a trigger
From
Tim Cross
Date:
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
Re: Detecting which columns a query will modify in a function calledby a trigger
From
Adrian Klaver
Date:
On 3/2/20 12:28 PM, stan wrote: > 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. Yeah, I'm going to go with the other commenters and say this design needs work. My feeling is that if there is a division of labor it should be reflected in the tables. To me it seems easier to build a overall look from smaller units, then trying to decompose a larger unit into smaller units of work. > > 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? > -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, Mar 02, 2020 at 01:44:52PM -0700, David G. Johnston wrote:
> On Mon, Mar 2, 2020 at 1:28 PM stan <stanb@panix.com> wrote:
>
> > 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?
> >
>
> On a theory level this design is insufficiently normalized. The fact that
> you are having issues and challenges working with it suggests you should
> seriously consider a different design, one that exhibits better
> normalization properties.
>
> Alternatively you might consider just removing direct access to the table
> and provide views and/or functions that can use normal permission grants.
> Add some check constraints to the table to describe and enforce the
> inter-field relationships that are present.
>
Thanks for the input.
I have, indeed created views that restrict the subset of columns that a
particular job function needs access to to the appropriate ones, but
unfortunately to the best of my knowledge, I cannot INSERT/UPDATE a table
through a view.
Am I suffering from a lack of knowledge here?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
Re: Detecting which columns a query will modify in a function calledby a trigger
From
Adrian Klaver
Date:
On 3/3/20 9:42 AM, stan wrote: > On Mon, Mar 02, 2020 at 01:44:52PM -0700, David G. Johnston wrote: >> On Mon, Mar 2, 2020 at 1:28 PM stan <stanb@panix.com> wrote: >> >>> 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? >>> >> >> On a theory level this design is insufficiently normalized. The fact that >> you are having issues and challenges working with it suggests you should >> seriously consider a different design, one that exhibits better >> normalization properties. >> >> Alternatively you might consider just removing direct access to the table >> and provide views and/or functions that can use normal permission grants. >> Add some check constraints to the table to describe and enforce the >> inter-field relationships that are present. >> > > Thanks for the input. > > I have, indeed created views that restrict the subset of columns that a > particular job function needs access to to the appropriate ones, but > unfortunately to the best of my knowledge, I cannot INSERT/UPDATE a table > through a view. > > Am I suffering from a lack of knowledge here? Yes: https://www.postgresql.org/docs/12/sql-createview.html Updatable Views -- Adrian Klaver adrian.klaver@aklaver.com
Re: Detecting which columns a query will modify in a function calledby a trigger
From
Hellmuth Vargas
Date:
hello
By means of json you can detect what change between NEW and OLD
By means of json you can detect what change between NEW and OLD
example:
select b.*
from
(values (now(),123456,'pepito perez',false)) as old(dato1,dato2,dato3,dato4),json_each_text(row_to_json(old)) as b(text1,text2)
except
select b.*
from
(values (now(),98765,'pepito perez',true)) as new(dato1,dato2,dato3,dato4),json_each_text(row_to_json(new)) as b(text1,text2)
El mar., 3 de mar. de 2020 a la(s) 13:48, Adrian Klaver (adrian.klaver@aklaver.com) escribió:
On 3/3/20 9:42 AM, stan wrote:
> On Mon, Mar 02, 2020 at 01:44:52PM -0700, David G. Johnston wrote:
>> On Mon, Mar 2, 2020 at 1:28 PM stan <stanb@panix.com> wrote:
>>
>>> 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?
>>>
>>
>> On a theory level this design is insufficiently normalized. The fact that
>> you are having issues and challenges working with it suggests you should
>> seriously consider a different design, one that exhibits better
>> normalization properties.
>>
>> Alternatively you might consider just removing direct access to the table
>> and provide views and/or functions that can use normal permission grants.
>> Add some check constraints to the table to describe and enforce the
>> inter-field relationships that are present.
>>
>
> Thanks for the input.
>
> I have, indeed created views that restrict the subset of columns that a
> particular job function needs access to to the appropriate ones, but
> unfortunately to the best of my knowledge, I cannot INSERT/UPDATE a table
> through a view.
>
> Am I suffering from a lack of knowledge here?
Yes:
https://www.postgresql.org/docs/12/sql-createview.html
Updatable Views
--
Adrian Klaver
adrian.klaver@aklaver.com
Cordialmente,
Ing. Hellmuth I. Vargas S.
Ing. Hellmuth I. Vargas S.
Re: Detecting which columns a query will modify in a function calledby a trigger
From
Adrian Klaver
Date:
On 3/3/20 1:32 PM, stan wrote: Please reply to list also. Ccing list > On Tue, Mar 03, 2020 at 10:48:29AM -0800, Adrian Klaver wrote: >> On 3/3/20 9:42 AM, stan wrote: >>> On Mon, Mar 02, 2020 at 01:44:52PM -0700, David G. Johnston wrote: >>>> On Mon, Mar 2, 2020 at 1:28 PM stan <stanb@panix.com> wrote: >>>> >>>>> 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? >>>>> >>>> >>>> On a theory level this design is insufficiently normalized. The fact that >>>> you are having issues and challenges working with it suggests you should >>>> seriously consider a different design, one that exhibits better >>>> normalization properties. >>>> >>>> Alternatively you might consider just removing direct access to the table >>>> and provide views and/or functions that can use normal permission grants. >>>> Add some check constraints to the table to describe and enforce the >>>> inter-field relationships that are present. >>>> >>> >>> Thanks for the input. >>> >>> I have, indeed created views that restrict the subset of columns that a >>> particular job function needs access to to the appropriate ones, but >>> unfortunately to the best of my knowledge, I cannot INSERT/UPDATE a table >>> through a view. >>> >>> Am I suffering from a lack of knowledge here? >> >> Yes: >> >> https://www.postgresql.org/docs/12/sql-createview.html >> >> Updatable Views >> > > OK, looking at that makes me think it is fairly limited. EG an Updatable > view can have on one FROM clause. Thus it by definition cannot use a JOIN. > So if I have a table with may columns, I can create a view that is a subset > of the available columns, which might be a way to address my issue. The link was for automatically updateable views. If you want to do something more involved then see: https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE > > Thanks. > -- Adrian Klaver adrian.klaver@aklaver.com
Re: Detecting which columns a query will modify in a function calledby a trigger
From
"David G. Johnston"
Date:
On Tue, Mar 3, 2020 at 3:48 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
The link was for automatically updateable views. If you want to do
something more involved then see:
https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE
CREATE TRIGGER works with views; I usually see recommendations to start there and avoid rules if at all possible.
David J.
Re: Detecting which columns a query will modify in a function calledby a trigger
From
Adrian Klaver
Date:
On 3/3/20 3:06 PM, David G. Johnston wrote: > On Tue, Mar 3, 2020 at 3:48 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > The link was for automatically updateable views. If you want to do > something more involved then see: > > https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE > > > CREATE TRIGGER works with views; I usually see recommendations to start > there and avoid rules if at all possible. The above suggests triggers then rules. > > David J. -- Adrian Klaver adrian.klaver@aklaver.com
Re: Detecting which columns a query will modify in a function calledby a trigger
From
"David G. Johnston"
Date:
On Tue, Mar 3, 2020 at 4:11 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 3/3/20 3:06 PM, David G. Johnston wrote:
> On Tue, Mar 3, 2020 at 3:48 PM Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
> The link was for automatically updateable views. If you want to do
> something more involved then see:
>
> https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE
>
>
> CREATE TRIGGER works with views; I usually see recommendations to start
> there and avoid rules if at all possible.
The above suggests triggers then rules.
Yeah, I see the buried in there. The link itself and the page itself is something like 95% rules coverage so it still seems worth pointing out even in hindsight.
Maybe add a link to the CREATE TRIGGER section in there...
David J.
Re: Detecting which columns a query will modify in a function called by a trigger
From
"David G. Johnston"
Date:
On Tue, Mar 3, 2020 at 4:19 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Mar 3, 2020 at 4:11 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:On 3/3/20 3:06 PM, David G. Johnston wrote:
> On Tue, Mar 3, 2020 at 3:48 PM Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
> The link was for automatically updateable views. If you want to do
> something more involved then see:
>
> https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE
>
>
> CREATE TRIGGER works with views; I usually see recommendations to start
> there and avoid rules if at all possible.
The above suggests triggers then rules.Yeah, I see the buried in there. The link itself and the page itself is something like 95% rules coverage so it still seems worth pointing out even in hindsight.Maybe add a link to the CREATE TRIGGER section in there...
Minor doc patch for this attached for consideration.
David J.
Attachment
Re: Detecting which columns a query will modify in a function called by a trigger
From
"David G. Johnston"
Date:
On Tue, Mar 3, 2020 at 4:19 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Mar 3, 2020 at 4:11 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:On 3/3/20 3:06 PM, David G. Johnston wrote:
> On Tue, Mar 3, 2020 at 3:48 PM Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
> The link was for automatically updateable views. If you want to do
> something more involved then see:
>
> https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE
>
>
> CREATE TRIGGER works with views; I usually see recommendations to start
> there and avoid rules if at all possible.
The above suggests triggers then rules.Yeah, I see the buried in there. The link itself and the page itself is something like 95% rules coverage so it still seems worth pointing out even in hindsight.Maybe add a link to the CREATE TRIGGER section in there...
Minor doc patch for this attached for consideration.
David J.