View Issue Details

IDProjectCategoryView StatusLast Update
0003920SymmetricDSImprovementpublic2019-11-01 08:44
ReporterkraynoppAssigned Toelong 
Prioritynormal 
Status closedResolutionfixed 
Product Version3.10.0 
Target Version3.10.5Fixed in Version3.10.5 
Summary0003920: In PostgreSQL trigger function should be SECURITY DEFINER
DescriptionIn 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.
Tagsdialect: postgresql, trigger

Activities

elong

2019-04-17 08:39

developer   ~0001411

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.

kraynopp

2019-04-18 01:34

reporter   ~0001412

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.

kraynopp

2019-10-02 01:42

reporter   ~0001607

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...

elong

2019-10-10 10:48

developer   ~0001614

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?

kraynopp

2019-10-11 02:27

reporter   ~0001615

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.

elong

2019-10-11 12:28

developer   ~0001616

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.

Related Changesets

SymmetricDS: 3.10 4214f8f6

2019-10-11 12:23:34

admin

Details Diff
0003920: In PostgreSQL trigger function should be SECURITY DEFINER
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

Issue History

Date Modified Username Field Change
2019-04-17 02:02 kraynopp New Issue
2019-04-17 08:39 elong Note Added: 0001411
2019-04-18 01:34 kraynopp Note Added: 0001412
2019-04-18 09:57 elong Assigned To => elong
2019-04-18 09:57 elong Status new => assigned
2019-04-18 09:57 elong Fixed in Version => 3.10.1
2019-04-18 09:57 elong Target Version => 3.10.1
2019-04-23 09:46 elong Target Version 3.10.1 => 3.10.2
2019-04-23 11:40 elong Fixed in Version 3.10.1 =>
2019-04-24 12:20 admin Tag Attached: dialect: postgresql
2019-04-24 12:20 admin Tag Attached: trigger
2019-05-08 08:51 admin Target Version 3.10.2 => 3.10.3
2019-06-24 13:45 elong Target Version 3.10.3 => 3.10.4
2019-08-09 15:41 elong Target Version 3.10.4 => 3.10.5
2019-10-02 01:42 kraynopp Note Added: 0001607
2019-10-10 10:48 elong Note Added: 0001614
2019-10-11 02:27 kraynopp Note Added: 0001615
2019-10-11 12:28 elong Status assigned => resolved
2019-10-11 12:28 elong Resolution open => fixed
2019-10-11 12:28 elong Fixed in Version => 3.10.5
2019-10-11 12:28 elong Note Added: 0001616
2019-10-11 13:00 admin Changeset attached => SymmetricDS 3.10 4214f8f6
2019-11-01 08:44 admin Status resolved => closed