View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000946 | SymmetricDS | Bug | public | 2012-12-12 03:30 | 2014-02-03 01:53 |
| Reporter | lkoops | Assigned To | chenson | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.1.11 | ||||
| Target Version | 3.2.0 | Fixed in Version | 3.2.0 | ||
| Summary | 0000946: Cannot delete triggers from tables if SymmetricDS user doesn't own those tables | ||||
| Description | I 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 Reproduce | Data 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 Information | I have not verified this behaviour with PostgreSQL 9.2, but the documentation relating to trigger permissions has not changed since 8.3. | ||||
| Tags | No tags attached. | ||||
|
|
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. |
|
|
Prepare for release |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2012-12-12 03:30 | lkoops | New Issue | |
| 2012-12-12 13:53 | chenson | Assigned To | => chenson |
| 2012-12-12 13:53 | chenson | Status | new => assigned |
| 2012-12-12 13:53 | chenson | Fixed in Version | => 3.2.0 |
| 2012-12-12 13:53 | chenson | Target Version | => 3.2.0 |
| 2012-12-12 13:53 | chenson | Description Updated | View Revisions |
| 2012-12-12 15:51 | rotten | Note Added: 0000100 | |
| 2012-12-13 15:16 | chenson | Status | assigned => resolved |
| 2012-12-13 15:16 | chenson | Resolution | open => fixed |
| 2012-12-16 17:31 | chenson | Note Added: 0000112 | |
| 2012-12-16 17:31 | chenson | Status | resolved => closed |
| 2014-02-03 01:53 | Changeset attached | => SymmetricDS trunk r6665 | |
| 2015-07-31 01:49 | chenson | Changeset attached | => SymmetricDS master 9f613fa3 |