org.nuiton.topia.persistence.jdbc.JdbcPostgresHelper Maven / Gradle / Ivy
The newest version!
package org.nuiton.topia.persistence.jdbc;
/*-
* #%L
* ToPIA Extension :: API
* %%
* Copyright (C) 2018 - 2022 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
* .
* #L%
*/
import org.apache.commons.lang3.tuple.Pair;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.nuiton.topia.persistence.TopiaException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Objects;
import java.util.Set;
/**
* Created by tchemit on 13/05/2018.
*
* @author Tony Chemit - [email protected]
*/
public class JdbcPostgresHelper extends JdbcHelper2 {
private static final Logger log = LogManager.getLogger(JdbcPostgresHelper.class);
private static final List POSTGIS_TABLES = List.of("geometry_columns", "spatial_ref_sys");
public JdbcPostgresHelper(JdbcConfiguration jdbcConfiguration) {
super(jdbcConfiguration);
}
public Set getTablePrivileges(String schema, String tableName) {
try {
try (Connection connection = openConnection()) {
DatabaseMetaData metaData = connection.getMetaData();
try (ResultSet observerDataPrivilege = metaData.getTablePrivileges(null, schema, tableName)) {
Set tablePrivileges = new LinkedHashSet<>();
while (observerDataPrivilege.next()) {
String security = observerDataPrivilege.getString("PRIVILEGE");
String grantor = observerDataPrivilege.getString("GRANTOR");
String grantee = observerDataPrivilege.getString("GRANTEE");
// Il se peut que le login soit echappe sous la forme \"login\"
grantor = grantor.replaceAll("\\\\\"", "");
grantee = grantee.replaceAll("\\\\\"", "");
log.debug(String.format("(security %s) - grantee (%s)", security, grantee));
if (grantor.equals(jdbcConfiguration.getJdbcConnectionUser())) {
log.debug("for " + tableName + " table " + grantor + '/' + security);
tablePrivileges.add(security);
} else if (grantee.equals(jdbcConfiguration.getJdbcConnectionUser())) {
log.debug("for " + tableName + " table " + grantee + '/' + security);
tablePrivileges.add(security);
}
}
return tablePrivileges;
}
}
} catch (Exception e) {
throw new TopiaException("Can't get postgres roles", e);
}
}
public Set getRoles() {
Set users = new LinkedHashSet<>();
try {
try (Connection connection = openConnection()) {
// la connexion est reussie, on recherche les droits de récupération de la version de la base
try (PreparedStatement preparedStatement = connection.prepareStatement("SELECT rolname FROM pg_catalog.pg_roles;")) {
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
String name = resultSet.getString(1);
users.add(name);
}
}
}
return users;
} catch (Exception e) {
throw new TopiaException(e);
}
}
public List> getTables(Set schemas, Set extraTables) {
List> result = new ArrayList<>();
try (Connection connection = openConnection()) {
DatabaseMetaData data = connection.getMetaData();
try (ResultSet tables = data.getTables(null, null, null, new String[]{"TABLE"})) {
int columnCount = tables.getMetaData().getColumnCount();
if (log.isDebugEnabled()) {
StringBuilder builder = new StringBuilder();
builder.append("\nheader");
for (int i = 1; i <= columnCount; i++) {
String columnName = tables.getMetaData().getColumnName(i);
builder.append("\n [").append(i).append("] :").append(columnName);
}
log.debug(builder.toString());
log.debug("fetchSize : " + tables.getFetchSize());
}
while (tables.next()) {
String schemaName = tables.getString(2);
String tableName = tables.getString(3);
if (log.isDebugEnabled()) {
log.debug(String.format("Discover table named %s", tables));
}
if (!extraTables.contains(tableName)) {
if (POSTGIS_TABLES.contains(tableName)) {
continue;
}
if (schemaName == null || !schemas.contains(schemaName.toLowerCase())) {
continue;
}
}
log.debug(String.format("Keep table: %s", tables));
result.add(Pair.of(schemaName, tableName));
}
}
Collections.sort(result);
return result;
} catch (SQLException e) {
throw new TopiaException(e);
}
}
public Set getPostgisFunctions(String functionPattern) {
String sql = String.format("SELECT ns.nspname::text || '.' || p.proname::text || '(' || oidvectortypes(p.proargtypes)::text || ')'" +
" FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)" +
" WHERE ns.nspname = 'public' AND p.proname ILIKE '%s%%';", functionPattern);
try {
Set result = new LinkedHashSet<>();
try (Connection connection = openConnection()) {
PreparedStatement preparedStatement = connection.prepareStatement(sql);
try (ResultSet set = preparedStatement.executeQuery()) {
while (set.next()) {
String functionPrototype = set.getString(1);
result.add(functionPrototype);
}
}
}
return result;
} catch (SQLException e) {
throw new TopiaException(e);
}
}
public boolean isOwner() {
try (Connection connection = openConnection()) {
try (PreparedStatement preparedStatement = connection.prepareStatement("SELECT pg_catalog.pg_get_userbyid(d.datdba) FROM pg_catalog.pg_database d WHERE d.datname = (SELECT current_database())")) {
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
String name = resultSet.getString(1);
return Objects.equals(jdbcConfiguration.getJdbcConnectionUser(), name);
}
return false;
}
} catch (SQLException e) {
throw new TopiaException(e);
}
}
public boolean isSuperUser() {
try (Connection connection = openConnection()) {
try (PreparedStatement preparedStatement = connection.prepareStatement("SELECT usesuper FROM pg_user WHERE usename = CURRENT_USER")) {
ResultSet resultSet = preparedStatement.executeQuery();
return resultSet.next() && resultSet.getBoolean(1);
}
} catch (SQLException e) {
throw new TopiaException(e);
}
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy