Tony CHEMIT pushed to branch develop at ultreiaio / ird-observe Commits: 128560c7 by Tony Chemit at 2020-08-10T13:32:06+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/entities/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/entities/ObserveTopiaApplicationContext.java ===================================== @@ -43,6 +43,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.Objects; @@ -330,6 +331,10 @@ public class ObserveTopiaApplicationContext extends AbstractObserveTopiaApplicat } + public BlobIdsIterator newBlobIdsIterator(Path blobIdsPath) { + return new BlobIdsIterator(getMetadataModel(), blobIdsPath, this); + } + private static class TripReplicateTablesPredicate implements TopiaSqlTablesFactory.TopiaSqlTablesPredicate { private final Set<TopiaMetadataEntity> done = new LinkedHashSet<>(); ===================================== services-local/src/main/java/fr/ird/observe/services/local/ObserveSecurityHelper.java ===================================== @@ -25,9 +25,9 @@ package fr.ird.observe.services.local; import com.google.common.collect.ImmutableSet; import fr.ird.observe.dto.ObserveDbRole; import fr.ird.observe.dto.db.ObserveDbUserDto; +import fr.ird.observe.entities.BlobIdsIterator; import fr.ird.observe.entities.Entities; import fr.ird.observe.entities.ObserveTopiaApplicationContext; -import fr.ird.observe.entities.ObserveTopiaConfiguration; import org.apache.commons.lang3.tuple.Pair; import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; @@ -49,6 +49,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; @@ -62,7 +63,11 @@ public class ObserveSecurityHelper { public static final String OBSERVE_SEINE_COMMON_SCHEMA_NAME = "ps_common"; 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;"; @@ -82,11 +87,13 @@ public class ObserveSecurityHelper { 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) { @@ -99,10 +106,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)); @@ -111,7 +119,7 @@ public class ObserveSecurityHelper { } } - private void createSecurityScript(Set<ObserveDbUserDto> users, SqlScriptWriter sqlScriptWriter) { + private void createSecurityScript(Set<ObserveDbUserDto> users, SqlScriptWriter sqlScriptWriter, Path blobIdsPath) throws IOException { Set<String> schemas = ImmutableSet.<String>builder().add(SCHEMA_PUBLIC).addAll(ObserveTopiaApplicationContext.newModelSupport().getMetadataModel().getSchemaNames()).build(); @@ -151,6 +159,7 @@ public class ObserveSecurityHelper { Set<String> referentialEscapedNames = escapedNames(referentialNames); Set<String> unusedEscapedNames = escapedNames(unusedNames); + BlobIdsIterator blobIdsIterator = applicationContext.newBlobIdsIterator(blobIdsPath); // suppression de tous les droits { @@ -165,6 +174,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); } @@ -172,6 +182,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()) { @@ -179,6 +191,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 @@ -187,6 +200,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 @@ -273,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 ===================================== @@ -516,10 +516,8 @@ public class DataSourceServiceLocal extends ObserveServiceLocal implements DataS // pas de securité pour les bases autres que postgresql if (dataSourceConfiguration instanceof ObserveDataSourceConfigurationTopiaPG) { 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/128560c791b0d7baae937372c9... -- View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/-/commit/128560c791b0d7baae937372c9... You're receiving this email because of your account on gitlab.com.