Table Updatable By Trigger Only
Hi All,
I have a trigger on a PostGIS table (say table A) that automatically
updates another PostGIS table (say table B). Also, users connect to these
tables (table A and B) using QGIS. However, I want the updates to table B
to be done by the trigger only (i.e. I don't want table B to be updated
from QGIS).
I have tried revoking UPDATE permissions on table B, but this prevents the
trigger from updating the table also as the trigger has to work with the
permissions of the user.
*Is there a way of making table B updatable by the trigger only?*
On 20/06/2017 14:43, Osahon Oduware wrote:
Hi All,
I have a trigger on a PostGIS table (say table A) that automatically updates another PostGIS table (say table B). Also, users connect to these tables (table A and B) using QGIS. However, I want the
updates to table B to be done by the trigger only (i.e. I don't want table B to be updated from QGIS).I have tried revoking UPDATE permissions on table B, but this prevents the trigger from updating the table also as the trigger has to work with the permissions of the user.
*Is there a way of making table B updatable by the trigger only?*
Write an ON UPDATE trigger on table B, and inside the code check for pg_trigger_depth() . If this is == 1 (called by user UPDATE) then RAISE an exception. If it is >1 then it is called by the other
trigger,
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
Hi Achilleas,
The pg_trigger_depth() function solved the issue. Thanks a lot, you made my
day.
On Tue, Jun 20, 2017 at 12:58 PM, Achilleas Mantzios <
achill@matrix.gatewaynet.com> wrote:
Show quoted text
On 20/06/2017 14:43, Osahon Oduware wrote:
Hi All,
I have a trigger on a PostGIS table (say table A) that automatically
updates another PostGIS table (say table B). Also, users connect to these
tables (table A and B) using QGIS. However, I want the updates to table B
to be done by the trigger only (i.e. I don't want table B to be updated
from QGIS).I have tried revoking UPDATE permissions on table B, but this prevents the
trigger from updating the table also as the trigger has to work with the
permissions of the user.*Is there a way of making table B updatable by the trigger only?*
Write an ON UPDATE trigger on table B, and inside the code check for
pg_trigger_depth() . If this is == 1 (called by user UPDATE) then RAISE an
exception. If it is >1 then it is called by the other trigger,--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
Osahon Oduware <osahon.gis@gmail.com> writes:
The pg_trigger_depth() function solved the issue. Thanks a lot, you made my
day.
If you start finding that that gets in your way for other purposes, you
might try making table A's trigger function be SECURITY DEFINER and
owned by a role that has permissions to change table B.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi Tom,
Ok, thanks.
On Tue, Jun 20, 2017 at 3:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Osahon Oduware <osahon.gis@gmail.com> writes:
The pg_trigger_depth() function solved the issue. Thanks a lot, you made
my
day.
If you start finding that that gets in your way for other purposes, you
might try making table A's trigger function be SECURITY DEFINER and
owned by a role that has permissions to change table B.regards, tom lane