Detecting which columns a query will modify in a function called by a trigger

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

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

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

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.

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

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

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

hello

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.

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

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.

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

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.

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.