View Revisions: Issue #946
| Summary | 0000946: Cannot delete triggers from tables if SymmetricDS user doesn't own those tables | ||
|---|---|---|---|
| Revision | 2012-12-12 13:53 by chenson | ||
| 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; |
||
| Revision | 2012-12-12 03:30 by chenson | ||
| 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; |
||