View Issue Details

IDProjectCategoryView StatusLast Update
0000946SymmetricDSBugpublic2014-02-02 20:53
ReporterlkoopsAssigned Tochenson 
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
Product Version3.1.11 
Target Version3.2.0Fixed in Version3.2.0 
Summary0000946: Cannot delete triggers from tables if SymmetricDS user doesn't own those tables
DescriptionI am replicating data from an existing PostgreSQL database.
The database owners do not want the sym tables in their schema, which is not a problem. I have them in a schema called sym. I've got that covered.
But they do not want SymmetricDS to run as the owner of the existing tables either, so I have created a new role, called sym, that has all the access it needs to operate, with one exception that is due to a PostgreSQL quirk.

The sym role can add triggers to a DB, but cannot remove them. Therefore, whenever I make a config change that affects the triggers, SymmetricDS will fail to remove the triggers, and hence the functions on which they depend. I have to manually drop all of the triggers and functions and call sync trigger, which I do via JMX.

Postgres has a quirk, in that triggers belong to a relation, and that you can only drop a trigger if you own the relation. You can create the trigger, just not drop it. There is no access I can grant to the sym role that will change this. I could change ownership of the tables to the sym role, but that is not acceptable to the DB owners either.

Another quirk with Postgres, if you drop a function with cascade it will drop the function and the trigger that depends on it, even if you are not the table owner.

Given this, it would be nice if SymmetricDS, when working with PostgreSQL, would use the cascade option when it drops the function. So, instead of
drop trigger . . .;
drop function . . .;

do this
drop function . . . cascade;
Steps To ReproduceData to be replicated in one schema with tables created by one user.

Configure SymmetricDS to login as a different user.

Configure some triggers for replication in sym_trigger; sym_router; and sym_trigger_router. Triggers will be created by symuser.

Change triggers. SymmetricDS will attempt to delete the triggers and functions, but will fail. with errors like this:

[ca-00001] - TriggerRouterService - About to remove triggers for inactivated table: easm_entrust.GroupTbl
[ca-00001] - PostgreSqlSymmetricDialect - Tried to remove trigger using: drop trigger SYM_ON_I_FOR_GRPTBL_C on easm_entrust.GroupTbl and failed because: ERROR: must be owner of relation grouptbl
[ca-00001] - PostgreSqlSymmetricDialect - Tried to remove function using: drop function easm_entrust.fSYM_ON_I_FOR_GRPTBL_C() and failed because: ERROR: cannot drop function fsym_on_i_for_grptbl_c() b
ecause other objects depend on it
  Hint: Use DROP ... CASCADE to drop the dependent objects too.
[ca-00001] - PostgreSqlSymmetricDialect - Tried to remove trigger using: drop trigger SYM_ON_D_FOR_GRPTBL_C on easm_entrust.GroupTbl and failed because: ERROR: must be owner of relation grouptbl
[ca-00001] - PostgreSqlSymmetricDialect - Tried to remove function using: drop function easm_entrust.fSYM_ON_D_FOR_GRPTBL_C() and failed because: ERROR: cannot drop function fsym_on_d_for_grptbl_c() b
ecause other objects depend on it
  Hint: Use DROP ... CASCADE to drop the dependent objects too.
[ca-00001] - PostgreSqlSymmetricDialect - Tried to remove trigger using: drop trigger SYM_ON_U_FOR_GRPTBL_C on easm_entrust.GroupTbl and failed because: ERROR: must be owner of relation grouptbl
[ca-00001] - PostgreSqlSymmetricDialect - Tried to remove function using: drop function easm_entrust.fSYM_ON_U_FOR_GRPTBL_C() and failed because: ERROR: cannot drop function fsym_on_u_for_grptbl_c() b
ecause other objects depend on it
  Hint: Use DROP ... CASCADE to drop the dependent objects too.
[ca-00001] - TriggerRouterService - There are triggers that have been marked as inactive. Please remove triggers represented by trigger_id=GroupTbl and trigger_hist_id=21
Additional InformationI have not verified this behaviour with PostgreSQL 9.2, but the documentation relating to trigger permissions has not changed since 8.3.
TagsNo tags attached.

Activities

rotten

2012-12-12 10:51

reporter   ~0000100

This is a PostgreSQL issue. (One of the very few things that really bugs me about PostgreSQL.)

"GRANT TRIGGER ON sometable to someuser" only allows that user to create triggers, not delete them. Only the owner of the table can delete them.

It is still true in 9.2.2.

I was forced to give the symmetricds user the role of the tableowner in PostgreSQL. (I didn't have to change the owner of the table.)

I haven't seen a work-around for this. I think GRANT TRIGGER should give you the permission to drop the triggers you create. I have no idea why it doesn't.

chenson

2012-12-16 12:31

administrator   ~0000112

Prepare for release

Related Changesets

SymmetricDS: master 9f613fa3

2012-12-12 08:47:16

chenson

Details Diff
0000946: Cannot delete triggers from tables if SymmetricDS user doesn't own those tables
0000946
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/postgresql/PostgreSqlSymmetricDialect.java Diff File

Issue History

Date Modified Username Field Change
2012-12-11 22:30 lkoops New Issue
2012-12-12 08:53 chenson Assigned To => chenson
2012-12-12 08:53 chenson Status new => assigned
2012-12-12 08:53 chenson Fixed in Version => 3.2.0
2012-12-12 08:53 chenson Target Version => 3.2.0
2012-12-12 08:53 chenson Description Updated View Revisions
2012-12-12 10:51 rotten Note Added: 0000100
2012-12-13 10:16 chenson Status assigned => resolved
2012-12-13 10:16 chenson Resolution open => fixed
2012-12-16 12:31 chenson Note Added: 0000112
2012-12-16 12:31 chenson Status resolved => closed
2014-02-02 20:53 Changeset attached => SymmetricDS trunk r6665
2015-07-30 21:49 chenson Changeset attached => SymmetricDS master 9f613fa3