This is an automated email from the git hooks/post-receive script. New commit to branch develop in repository observe. See http://git.codelutin.com/observe.git commit 2ed1ee0248282976994144e1e410ab25ebd23847 Author: Tony CHEMIT <chemit@codelutin.com> Date: Mon Aug 10 22:30:26 2015 +0200 Ajout des droits sur les fonctions postgis utilisées dans les scripts (refs #7269) --- .../java/fr/ird/observe/db/util/PGInstall.java | 65 +++++++++++++++++++++- 1 file changed, 62 insertions(+), 3 deletions(-) diff --git a/observe-business/src/main/java/fr/ird/observe/db/util/PGInstall.java b/observe-business/src/main/java/fr/ird/observe/db/util/PGInstall.java index d57a514..9350fab 100644 --- a/observe-business/src/main/java/fr/ird/observe/db/util/PGInstall.java +++ b/observe-business/src/main/java/fr/ird/observe/db/util/PGInstall.java @@ -44,6 +44,7 @@ import java.util.Arrays; import java.util.Collection; import java.util.Collections; import java.util.HashSet; +import java.util.LinkedHashSet; import java.util.List; import java.util.Set; @@ -74,19 +75,30 @@ public abstract class PGInstall { protected static final String GRANT_ON_TABLE_ALL_PATTERN = "GRANT ALL ON %s.%s TO %s;\n"; + protected static final String GRANT_ON_FUNCTION_PATTERN = "GRANT EXECUTE ON FUNCTION %s TO %s;\n"; + protected static final String REVOKE_ON_SCHEMA_ALL_PATTERN = "REVOKE ALL ON SCHEMA %s FROM %s CASCADE;\n"; + protected static final String REVOKE_ON_FUNCTIONS_PATTERN = "REVOKE EXECUTE ON FUNCTION %s FROM %s CASCADE;\n"; + protected static final String GRANT_ON_SCHEMA_ALL_PATTERN = "GRANT USAGE ON SCHEMA %s TO %s;\n"; protected static final Set<String> EXTRA_TABLES = Sets.newHashSet(TMSVersionDAO.TABLE_NAME, TMSVersionDAO.LEGACY_TABLE_NAME); + protected static final Set<String> FUNCTION_NAMES_PREFIXS = Sets.newHashSet("ST_MakePoint", + "ST_SetSRID", + "sync_", + "tr_sync", + "ot_enhanced_school_type", + "observe_"); + protected static final String SCHEMA_PUBLIC = "public"; protected static final Set<String> SCHEMAS = Sets.newHashSet(SCHEMA_PUBLIC, - "OBSERVE_COMMON", - "OBSERVE_SEINE", - "OBSERVE_LONGLINE"); + "OBSERVE_COMMON", + "OBSERVE_SEINE", + "OBSERVE_LONGLINE"); protected static final Set<String> POSTGIS_TABLES = Sets.newHashSet("geometry_columns", "spatial_ref_sys"); @@ -257,6 +269,12 @@ public abstract class PGInstall { getDataTables(tables, referentielTables); + Set<String> allPostgisFunctions = new LinkedHashSet<String>(); + for (String postgisFunction : FUNCTION_NAMES_PREFIXS) { + Set<String> postgisFunctions = getPostgisFunctions(conn, postgisFunction); + allPostgisFunctions.addAll(postgisFunctions); + } + StringBuilder builder = new StringBuilder(); // suppression de tous les droits @@ -271,17 +289,21 @@ public abstract class PGInstall { addOnTablesForRole(REVOKE_ON_TABLE_ALL_PATTERN, builder, tables, roles); addOnSchemaForRole(REVOKE_ON_SCHEMA_ALL_PATTERN, builder, SCHEMAS, roles); + addOnFunctionForRole(REVOKE_ON_FUNCTIONS_PATTERN, builder, allPostgisFunctions, roles); + } // ajout propriétaire addOnTablesForRole(SET_ON_TABLE_OWNER_PATTERN, builder, tables, securityModel.getEscapedAdministrateur()); addOnSchemaForRole(GRANT_ON_SCHEMA_ALL_PATTERN, builder, SCHEMAS, securityModel.getEscapedAdministrateur()); + addOnSchemaForRole(GRANT_ON_FUNCTION_PATTERN, builder, allPostgisFunctions, securityModel.getEscapedAdministrateur()); // ajout administrateurs if (CollectionUtils.isNotEmpty(securityModel.getEscapedTechnicien())) { String roles = StringUtil.join(securityModel.getEscapedTechnicien(), ",", true); addOnTablesForRole(GRANT_ON_TABLE_ALL_PATTERN, builder, tables, roles); addOnSchemaForRole(GRANT_ON_SCHEMA_ALL_PATTERN, builder, SCHEMAS, roles); + addOnSchemaForRole(GRANT_ON_FUNCTION_PATTERN, builder, allPostgisFunctions, roles); } // ajout utilisateur @@ -289,6 +311,7 @@ public abstract class PGInstall { String roles = StringUtil.join(securityModel.getEscapedUtilisateur(), ",", true); addOnTablesForRole(GRANT_ON_TABLE_READ_PATTERN, builder, tables, roles); addOnSchemaForRole(GRANT_ON_SCHEMA_ALL_PATTERN, builder, SCHEMAS, roles); + addOnSchemaForRole(GRANT_ON_FUNCTION_PATTERN, builder, allPostgisFunctions, roles); } // ajout referentiel @@ -296,7 +319,9 @@ public abstract class PGInstall { String roles = StringUtil.join(securityModel.getEscapedReferentiel(), ",", true); addOnTablesForRole(GRANT_ON_TABLE_READ_PATTERN, builder, referentielTables, roles); addOnSchemaForRole(GRANT_ON_SCHEMA_ALL_PATTERN, builder, SCHEMAS, roles); + addOnSchemaForRole(GRANT_ON_FUNCTION_PATTERN, builder, allPostgisFunctions, roles); } + String result = builder.toString(); if (log.isInfoEnabled()) { @@ -321,6 +346,30 @@ public abstract class PGInstall { return result; } + protected Set<String> getPostgisFunctions(Connection conn, String functionPattern) throws SQLException { + + final Set<String> result = new LinkedHashSet<String>(); + String sql = String.format("SELECT format('%%I.%%I(%%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes))" + + " FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)" + + " WHERE ns.nspname = 'public' AND p.proname ILIKE '%s%%';", functionPattern); + PreparedStatement sta = + conn.prepareStatement(sql); + try { + ResultSet set = sta.executeQuery(); + + while (set.next()) { + String functionPrototype = set.getString(1); + result.add(functionPrototype); + } + + } finally { + + sta.close(); + } + return result; + + } + protected List<Pair<String, String>> getDataTables(Collection<Pair<String, String>> tables, Collection<Pair<String, String>> referentielTables) { List<Pair<String, String>> result = new ArrayList<Pair<String, String>>(tables); @@ -419,6 +468,16 @@ public abstract class PGInstall { } } + protected void addOnFunctionForRole(String pattern, + StringBuilder builder, + Set<String> functions, + String role) { + + for (String t : functions) { + builder.append(String.format(pattern, t, role)); + } + } + protected List<Pair<String, String>> getTables(Connection conn, Set<String> schemas, Set<String> extraTables) throws SQLException { List<Pair<String, String>> result = new ArrayList<Pair<String, String>>(); -- To stop receiving notification emails like this one, please contact codelutin.com SCM administrator <admin+scm@list.forge.codelutin.com>.