Tony CHEMIT pushed to branch develop-7.x at ultreiaio / ird-observe Commits: 2d50c6fb by Tony Chemit at 2020-08-06T21:24:45+02:00 Penser à tester le comportement des large objects sur la V7 - Closes #927 - - - - - 5 changed files: - common-persistence/pom.xml - + common-persistence/src/main/java/fr/ird/observe/entities/BlobIdsIterator.java - persistence/src/main/java/fr/ird/observe/persistence/ObserveTopiaApplicationContext.java - services-local/src/main/java/fr/ird/observe/services/local/ObserveSecurityHelper.java - services-local/src/main/java/fr/ird/observe/services/local/service/DataSourceServiceLocal.java Changes: ===================================== common-persistence/pom.xml ===================================== @@ -47,6 +47,10 @@ <groupId>org.nuiton.topia</groupId> <artifactId>topia-persistence</artifactId> </dependency> + <dependency> + <groupId>io.ultreia.java4all.topia</groupId> + <artifactId>persistence</artifactId> + </dependency> <dependency> <groupId>io.ultreia.java4all.topia</groupId> <artifactId>service-migration</artifactId> ===================================== common-persistence/src/main/java/fr/ird/observe/entities/BlobIdsIterator.java ===================================== @@ -0,0 +1,148 @@ +package fr.ird.observe.entities; + +/*- + * #%L + * ObServe Toolkit :: Common Persistence + * %% + * Copyright (C) 2008 - 2020 IRD, Code Lutin, Ultreia.io + * %% + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU General Public License as + * published by the Free Software Foundation, either version 3 of the + * License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public + * License along with this program. If not, see + * <http://www.gnu.org/licenses/gpl-3.0.html>. + * #L% + */ + +import org.nuiton.topia.persistence.TopiaApplicationContext; +import org.nuiton.topia.persistence.jdbc.JdbcPostgresHelper; +import org.nuiton.topia.persistence.metadata.TopiaMetadataEntity; +import org.nuiton.topia.persistence.metadata.TopiaMetadataModel; + +import java.io.BufferedReader; +import java.io.BufferedWriter; +import java.io.Closeable; +import java.io.IOException; +import java.nio.file.Files; +import java.nio.file.Path; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Iterator; +import java.util.Objects; + +public class BlobIdsIterator implements Iterator<String>, Closeable { + + private final TopiaMetadataModel model; + + private final Path cachePath; + private final JdbcPostgresHelper jdbcHelper; + private FileIterator iterator; + + public BlobIdsIterator(TopiaMetadataModel model, Path cachePath, TopiaApplicationContext<?> applicationContext) { + this.model = Objects.requireNonNull(model); + this.cachePath = Objects.requireNonNull(cachePath); + this.jdbcHelper = new JdbcPostgresHelper(Objects.requireNonNull(applicationContext).getConfiguration()); + } + + @Override + public boolean hasNext() { + return iterator().hasNext(); + } + + @Override + public String next() { + return iterator().next(); + } + + public FileIterator iterator() { + if (iterator == null) { + iterator = new FileIterator(cachePath, model, jdbcHelper); + } + return iterator; + } + + @Override + public void close() throws IOException { + if (iterator != null) { + iterator.close(); + iterator = null; + } + } + + private static class FileIterator implements Iterator<String>, Closeable { + private final Iterator<String> iterator; + private final BufferedReader bufferedReader; + + public FileIterator(Path cachePath, TopiaMetadataModel model, JdbcPostgresHelper jdbcHelper) { + if (Files.notExists(cachePath)) { + try { + createCache(jdbcHelper, model, cachePath); + } catch (IOException e) { + throw new IllegalStateException("can't create cache at " + cachePath, e); + } + } + try { + bufferedReader = Files.newBufferedReader(Objects.requireNonNull(cachePath)); + } catch (IOException e) { + throw new IllegalStateException("can't create reader on " + cachePath, e); + } + iterator = bufferedReader.lines().iterator(); + } + + @Override + public boolean hasNext() { + return iterator.hasNext(); + } + + @Override + public String next() { + return iterator.next(); + } + + @Override + public void close() throws IOException { + bufferedReader.close(); + } + + private void createCache(JdbcPostgresHelper jdbcHelper, TopiaMetadataModel model, Path cachePath) throws IOException { + if (Files.notExists(cachePath.getParent())) { + Files.createDirectory(cachePath.getParent()); + } + try (BufferedWriter writer = Files.newBufferedWriter(cachePath)) { + for (TopiaMetadataEntity metadataEntity : model) { + if (metadataEntity.withBlob()) { + for (String blobProperty : metadataEntity.getBlobProperties()) { + fillCache(jdbcHelper, metadataEntity.getDbSchemaName(), metadataEntity.getDbTableName(), metadataEntity.getDbColumnName(blobProperty), writer); + } + } + } + writer.flush(); + } + } + + private void fillCache(JdbcPostgresHelper jdbcHelper, String dbSchemaName, String dbTableName, String dbColumnName, BufferedWriter writer) { + String sql = String.format("SELECT %1$s FROM %2$s.%3$s WHERE %1$s IS NOT NULL ORDER BY topiaId ASC", dbColumnName, dbSchemaName, dbTableName); + jdbcHelper.consume(c -> { + try (PreparedStatement preparedStatement = c.prepareStatement(sql)) { + try (ResultSet resultSet = preparedStatement.executeQuery()) { + while (resultSet.next()) { + writer.write(resultSet.getString(1)); + writer.newLine(); + } + } + } catch (SQLException | IOException e) { + throw new IllegalStateException(e); + } + }); + } + } +} ===================================== persistence/src/main/java/fr/ird/observe/persistence/ObserveTopiaApplicationContext.java ===================================== @@ -23,6 +23,7 @@ package fr.ird.observe.persistence; */ import com.google.common.collect.ImmutableSet; +import fr.ird.observe.entities.BlobIdsIterator; import fr.ird.observe.entities.referentiel.ObserveReferentialEntity; import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; @@ -46,6 +47,7 @@ import org.nuiton.topia.service.script.table.TopiaSqlTable; import org.nuiton.topia.service.script.table.TopiaSqlTables; import org.nuiton.topia.service.script.table.TopiaSqlTablesFactory; +import java.nio.file.Path; import java.util.EnumSet; import java.util.LinkedHashSet; import java.util.List; @@ -335,6 +337,9 @@ public class ObserveTopiaApplicationContext extends AbstractObserveTopiaApplicat return referentialTables = topiaSqlTablesFactory.newReplicateEntityTables(new TripReplicateTablesPredicate(), entityEnum); } + public BlobIdsIterator newBlobIdsIterator(Path blobIdsPath) { + return new BlobIdsIterator(getMetadataModel(), blobIdsPath, this); + } private static class TripReplicateTablesPredicate implements TopiaSqlTablesFactory.TopiaSqlTablesPredicate { ===================================== services-local/src/main/java/fr/ird/observe/services/local/ObserveSecurityHelper.java ===================================== @@ -26,8 +26,9 @@ import com.google.common.collect.ImmutableSet; import com.google.common.collect.Iterables; import fr.ird.observe.dto.ObserveDbRole; import fr.ird.observe.dto.db.ObserveDbUserDto; +import fr.ird.observe.entities.BlobIdsIterator; import fr.ird.observe.persistence.Entities; -import fr.ird.observe.persistence.ObserveTopiaConfiguration; +import fr.ird.observe.persistence.ObserveTopiaApplicationContext; import org.apache.commons.lang3.tuple.Pair; import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; @@ -49,6 +50,7 @@ import java.util.Collections; import java.util.HashSet; import java.util.LinkedHashSet; import java.util.List; +import java.util.Objects; import java.util.Set; import java.util.function.Function; import java.util.stream.Collectors; @@ -63,7 +65,11 @@ public class ObserveSecurityHelper { private static final String OBSERVE_LONGLINE_SCHEMA_NAME = "observe_longline"; private static final Function<String, String> ESCAPE_STRING = input -> "\"" + input + "\""; private static final String REVOKE_ON_TABLE_ALL_PATTERN = "REVOKE ALL ON %s.%s FROM %s CASCADE;"; + private static final String REVOKE_ON_LARGE_OBJECT_ALL_PATTERN = "REVOKE ALL ON LARGE OBJECT %s FROM %s CASCADE;"; private static final String SET_ON_TABLE_OWNER_PATTERN = "ALTER TABLE %s.%s OWNER TO %s;"; + private static final String SET_ON_LARGE_OBJECT_OWNER_PATTERN = "ALTER LARGE OBJECT %s OWNER TO %s;"; + private static final String GRANT_ON_LARGE_OBJECT_READ_PATTERN = "GRANT SELECT ON LARGE OBJECT %s TO %s;"; + private static final String GRANT_ON_LARGE_OBJECT_ALL_PATTERN = "GRANT ALL ON LARGE OBJECT %s TO %s;"; private static final String GRANT_ON_TABLE_READ_PATTERN = "GRANT SELECT ON %s.%s TO %s;"; private static final String GRANT_ON_TABLE_ALL_PATTERN = "GRANT ALL ON %s.%s TO %s;"; private static final String GRANT_ON_FUNCTION_PATTERN = "GRANT EXECUTE ON FUNCTION %s TO %s;"; @@ -74,24 +80,26 @@ public class ObserveSecurityHelper { TMSVersionHibernateDao.TABLE_NAME, TMSVersionHibernateDao.LEGACY_TABLE_NAME); private static final Set<String> FUNCTION_NAMES_PREFIXS = ImmutableSet.of("ST_MakePoint", - "ST_SetSRID", - "sync_", - "tr_sync", - "ot_enhanced_school_type", - "observe_"); + "ST_SetSRID", + "sync_", + "tr_sync", + "ot_enhanced_school_type", + "observe_"); private static final String SCHEMA_PUBLIC = "public"; private static final Set<String> SCHEMAS = ImmutableSet.of(SCHEMA_PUBLIC, - OBSERVE_COMMON_SCHEMA_NAME, - OBSERVE_SEINE_SCHEMA_NAME, - OBSERVE_LONGLINE_SCHEMA_NAME); + OBSERVE_COMMON_SCHEMA_NAME, + OBSERVE_SEINE_SCHEMA_NAME, + OBSERVE_LONGLINE_SCHEMA_NAME); private static final Logger log = LogManager.getLogger(ObserveSecurityHelper.class); private final JdbcPostgresHelper jdbcHelper; private final Path temporaryDirectory; + private final ObserveTopiaApplicationContext applicationContext; - public ObserveSecurityHelper(ObserveTopiaConfiguration jdbcConfiguration) { - this.jdbcHelper = new JdbcPostgresHelper(jdbcConfiguration); - this.temporaryDirectory = jdbcConfiguration.getTemporaryDirectory(); + public ObserveSecurityHelper(ObserveTopiaApplicationContext applicationContext) { + this.applicationContext = Objects.requireNonNull(applicationContext); + this.jdbcHelper = new JdbcPostgresHelper(applicationContext.getConfiguration()); + this.temporaryDirectory = applicationContext.getConfiguration().getTemporaryDirectory(); } public void applySecurity(Set<ObserveDbUserDto> users) { @@ -104,10 +112,11 @@ public class ObserveSecurityHelper { } catch (IOException e) { throw new IllegalStateException("Can't create temporary path", e); } + Path blobIdsPath = scriptPath.getParent().resolve(scriptPath.toFile().getName().replace(".sql",".blob-ids")); try { try (SqlScriptWriter sqlScriptWriter = SqlScriptWriter.of(scriptPath)) { - createSecurityScript(users, sqlScriptWriter); + createSecurityScript(users, sqlScriptWriter, blobIdsPath); log.info(String.format("Generate security script %d statements(s) at %s", sqlScriptWriter.getStatementCount(), scriptPath)); } jdbcHelper.consume(SqlScriptConsumer.of(scriptPath)); @@ -116,7 +125,7 @@ public class ObserveSecurityHelper { } } - private void createSecurityScript(Set<ObserveDbUserDto> users, SqlScriptWriter sqlScriptWriter) { + private void createSecurityScript(Set<ObserveDbUserDto> users, SqlScriptWriter sqlScriptWriter, Path blobIdsPath) throws IOException { List<Pair<String, String>> tables = jdbcHelper.getTables(SCHEMAS, EXTRA_TABLES); @@ -154,6 +163,7 @@ public class ObserveSecurityHelper { Set<String> referentialEscapedNames = escapedNames(referentialNames); Set<String> unusedEscapedNames = escapedNames(unusedNames); + BlobIdsIterator blobIdsIterator = applicationContext.newBlobIdsIterator(blobIdsPath); // suppression de tous les droits { @@ -168,6 +178,7 @@ public class ObserveSecurityHelper { addOnTablesForRole(REVOKE_ON_TABLE_ALL_PATTERN, sqlScriptWriter, tables, roles); addOnSchemaForRole(REVOKE_ON_SCHEMA_ALL_PATTERN, sqlScriptWriter, SCHEMAS, roles); addOnFunctionForRole(REVOKE_ON_FUNCTIONS_PATTERN, sqlScriptWriter, allPostgisFunctions, roles); + addOnLargeObjectForRole(REVOKE_ON_LARGE_OBJECT_ALL_PATTERN, sqlScriptWriter, blobIdsIterator, roles); } @@ -175,6 +186,8 @@ public class ObserveSecurityHelper { addOnTablesForRole(SET_ON_TABLE_OWNER_PATTERN, sqlScriptWriter, tables, administratorEscapedName); addOnSchemaForRole(GRANT_ON_SCHEMA_ALL_PATTERN, sqlScriptWriter, SCHEMAS, administratorEscapedName); addOnSchemaForRole(GRANT_ON_FUNCTION_PATTERN, sqlScriptWriter, allPostgisFunctions, administratorEscapedName); + addOnLargeObjectForRole(SET_ON_LARGE_OBJECT_OWNER_PATTERN, sqlScriptWriter, blobIdsIterator, administratorEscapedName); + addOnLargeObjectForRole(GRANT_ON_LARGE_OBJECT_ALL_PATTERN, sqlScriptWriter, blobIdsIterator, administratorEscapedName); // ajout administrateurs if (!technicalEscapedNames.isEmpty()) { @@ -182,6 +195,7 @@ public class ObserveSecurityHelper { addOnTablesForRole(GRANT_ON_TABLE_ALL_PATTERN, sqlScriptWriter, tables, roles); addOnSchemaForRole(GRANT_ON_SCHEMA_ALL_PATTERN, sqlScriptWriter, SCHEMAS, roles); addOnSchemaForRole(GRANT_ON_FUNCTION_PATTERN, sqlScriptWriter, allPostgisFunctions, roles); + addOnLargeObjectForRole(GRANT_ON_LARGE_OBJECT_ALL_PATTERN, sqlScriptWriter, blobIdsIterator, roles); } // ajout utilisateur @@ -190,6 +204,7 @@ public class ObserveSecurityHelper { addOnTablesForRole(GRANT_ON_TABLE_READ_PATTERN, sqlScriptWriter, tables, roles); addOnSchemaForRole(GRANT_ON_SCHEMA_ALL_PATTERN, sqlScriptWriter, SCHEMAS, roles); addOnSchemaForRole(GRANT_ON_FUNCTION_PATTERN, sqlScriptWriter, allPostgisFunctions, roles); + addOnLargeObjectForRole(GRANT_ON_LARGE_OBJECT_READ_PATTERN, sqlScriptWriter, blobIdsIterator, roles); } // ajout referentiel @@ -272,6 +287,18 @@ public class ObserveSecurityHelper { } } + private void addOnLargeObjectForRole(String pattern, SqlScriptWriter builder, BlobIdsIterator blobIdsIterator, String role) throws IOException { + try { + while (blobIdsIterator.hasNext()) { + String blobId = blobIdsIterator.next(); + builder.writeSql(String.format(pattern, blobId, role)); + } + } finally { + blobIdsIterator.close(); + } + + } + private void addOnFunctionForRole(String pattern, SqlScriptWriter builder, Set<String> functions, String role) { for (String t : functions) { builder.writeSql(String.format(pattern, t, role)); ===================================== services-local/src/main/java/fr/ird/observe/services/local/service/DataSourceServiceLocal.java ===================================== @@ -582,11 +582,8 @@ public class DataSourceServiceLocal extends ObserveServiceLocal implements DataS ObserveDataSourceConfigurationTopiaPG sourceConfiguration = (ObserveDataSourceConfigurationTopiaPG) dataSourceConfiguration; - ObserveTopiaConfiguration topiaConfiguration; - try (ObserveTopiaApplicationContext optionalTopiaApplicationContext = ObserveTopiaApplicationContextFactory.getOrCreateTopiaApplicationContext(sourceConfiguration)) { - - topiaConfiguration = optionalTopiaApplicationContext.getConfiguration(); - new ObserveSecurityHelper(topiaConfiguration).applySecurity(users); + try (ObserveTopiaApplicationContext topiaApplicationContext = ObserveTopiaApplicationContextFactory.getOrCreateTopiaApplicationContext(sourceConfiguration)) { + new ObserveSecurityHelper(topiaApplicationContext).applySecurity(users); } } View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/-/commit/2d50c6fbb9a495004f77010dc6... -- View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/-/commit/2d50c6fbb9a495004f77010dc6... You're receiving this email because of your account on gitlab.com.