View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0003920 | SymmetricDS | Improvement | public | 2019-04-17 06:02 | 2019-11-01 12:44 |
| Reporter | kraynopp | Assigned To | elong | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.10.0 | ||||
| Target Version | 3.10.5 | Fixed in Version | 3.10.5 | ||
| Summary | 0003920: In PostgreSQL trigger function should be SECURITY DEFINER | ||||
| Description | In PostgreSQL trigger function should be created as SECURITY DEFINER and placed in schema where other SymmetricDS's objects placed (parameter search_path). It has been realised for Oracle already (all triggers are created in SymmetricDS's schema). This improvement allows to increase transparency of replication process for other users. | ||||
| Tags | dialect: postgresql, trigger | ||||
|
|
You can put SymmetricDS objects in a specific schema by altering the user/role of the SymmetricDS user to give it the search_path you want. What is the advantage of SECURITY DEFINER? It means the function is executed with the privileges of the SymmetricDS user. The only thing I can think of is it eliminates the need to grant the user permissions to sym_data. |
|
|
Yes, I know about search_path and use this parameter, it is described in documentation. The main advantage of SECURITY_DEFINER is the principle of least privilege ( see https://en.wikipedia.org/wiki/Principle_of_least_privilege ). IMHO end users and application users (except SymmetricDS user ) must not have any privileges to SymmetricDS system objects. BTW if you installs symmetric into oracle database, all triggers will be placed in symmetric schema and executed on behalf of SymmetricDS user according to principle of least privilege. |
|
|
Sorry to bother you, but I would like to know if you have any progress. This issue is really annoying (at least for me) because of I have to control all trigger functions manually and change it from SECURITY INVOKER to SECURITY DEFINER after addition of new table, change structure, call sync_triggers and so on... |
|
|
Adding "security definer" would be easy enough. It passes our unit tests. Maybe we add a parameter so a user can change if it's "invoker" or "definer" and make it "definer" as the default behavior going forward. When we create the trigger's function, we use "create or replace function $(schemaName)f$(triggerName)()" and it replaces "$(schemaName)" with the schema where the user's table is. Are you asking to get rid of the "$(schemaName)"? Or use "$(defaultSchema)" which is the equivalent of current_schema()? If you look at the function sym_node_disabled, it gets created without specifying the schema, so does it get created where you wanted it? |
|
|
Thank you for responding. I agree, the default behavior should be "invoker" to avoid breaking existing installations, but this behavior can be changed to "definer" by special parameter. Now trigger functions are created in schema where user's tables are located. IMHO they should be placed in current_schema of symmetricds user, but it is not critical. I do not understand about sym_node_disabled function. Now this function is placed in current_schema of symmetricds user and I think it is right behavior. |
|
|
For this issue, we'll just deal with the security definer then. There will be a new startup parameter that can be set postgres.security.definer=true in the engines file. |
|
SymmetricDS: 3.10 4214f8f6 2019-10-11 12:23:34 Details Diff |
0003920: In PostgreSQL trigger function should be SECURITY DEFINER |
Affected Issues 0003920 |
|
| mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/postgresql/PostgreSqlTriggerTemplate.java | Diff File | ||
| mod - symmetric-core/src/main/java/org/jumpmind/symmetric/common/ParameterConstants.java | Diff File | ||
| mod - symmetric-core/src/main/resources/symmetric-default.properties | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2019-04-17 06:02 | kraynopp | New Issue | |
| 2019-04-17 12:39 | elong | Note Added: 0001411 | |
| 2019-04-18 05:34 | kraynopp | Note Added: 0001412 | |
| 2019-04-18 13:57 | elong | Assigned To | => elong |
| 2019-04-18 13:57 | elong | Status | new => assigned |
| 2019-04-18 13:57 | elong | Fixed in Version | => 3.10.1 |
| 2019-04-18 13:57 | elong | Target Version | => 3.10.1 |
| 2019-04-23 13:46 | elong | Target Version | 3.10.1 => 3.10.2 |
| 2019-04-23 15:40 | elong | Fixed in Version | 3.10.1 => |
| 2019-04-24 16:20 | admin | Tag Attached: dialect: postgresql | |
| 2019-04-24 16:20 | admin | Tag Attached: trigger | |
| 2019-05-08 12:51 | admin | Target Version | 3.10.2 => 3.10.3 |
| 2019-06-24 17:45 | elong | Target Version | 3.10.3 => 3.10.4 |
| 2019-08-09 19:41 | elong | Target Version | 3.10.4 => 3.10.5 |
| 2019-10-02 05:42 | kraynopp | Note Added: 0001607 | |
| 2019-10-10 14:48 | elong | Note Added: 0001614 | |
| 2019-10-11 06:27 | kraynopp | Note Added: 0001615 | |
| 2019-10-11 16:28 | elong | Status | assigned => resolved |
| 2019-10-11 16:28 | elong | Resolution | open => fixed |
| 2019-10-11 16:28 | elong | Fixed in Version | => 3.10.5 |
| 2019-10-11 16:28 | elong | Note Added: 0001616 | |
| 2019-10-11 17:00 | admin | Changeset attached | => SymmetricDS 3.10 4214f8f6 |
| 2019-11-01 12:44 | admin | Status | resolved => closed |