View Issue Details

IDProjectCategoryView StatusLast Update
0003920SymmetricDSImprovementpublic2019-05-08 08:51
ReporterkraynoppAssigned Toelong 
Prioritynormal 
Status assignedResolutionopen 
Product Version3.10.0 
Target Version3.10.3Fixed in Version 
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.

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