org.dspace.storage.rdbms.PostgresUtils Maven / Gradle / Ivy
Show all versions of dspace-api Show documentation
/**
* The contents of this file are subject to the license and copyright
* detailed in the LICENSE and NOTICE files at the root of the source
* tree and available online at
*
* http://www.dspace.org/license/
*/
package org.dspace.storage.rdbms;
import static org.dspace.storage.rdbms.DatabaseUtils.getSchemaName;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.flywaydb.core.api.FlywayException;
/**
* Database utility class specific to Postgres.
* This class contains tools and methods which are useful in determining
* the status of a PostgreSQL database backend. It's a companion class
* to DatabaseUtils, but PostgreSQL specific.
*
* @author Tim Donohue
*/
public class PostgresUtils {
// PostgreSQL pgcrypto extention name, and required versions of Postgres & pgcrypto
public static final String PGCRYPTO = "pgcrypto";
public static final Double PGCRYPTO_VERSION = 1.1;
public static final Double POSTGRES_VERSION = 9.4;
/**
* Default constructor
*/
private PostgresUtils() { }
/**
* Get version of pgcrypto extension available. The extension is "available"
* if it's been installed via operating system tools/packages. It also
* MUST be installed in the DSpace database (see getPgcryptoInstalled()).
*
* The pgcrypto extension is required for Postgres databases
*
* @param connection database connection
* @return version number or null if not available
*/
protected static Double getPgcryptoAvailableVersion(Connection connection) {
Double version = null;
String checkPgCryptoAvailable = "SELECT default_version AS version FROM pg_available_extensions WHERE name=?";
// Run the query to obtain the version of 'pgcrypto' available
try (PreparedStatement statement = connection.prepareStatement(checkPgCryptoAvailable)) {
statement.setString(1, PGCRYPTO);
try (ResultSet results = statement.executeQuery()) {
if (results.next()) {
version = results.getDouble("version");
}
}
} catch (SQLException e) {
throw new FlywayException("Unable to determine whether 'pgcrypto' extension is available.", e);
}
return version;
}
/**
* Get version of pgcrypto extension installed in the DSpace database.
*
* The pgcrypto extension is required for Postgres databases to support
* UUIDs.
*
* @param connection database connection
* @return version number or null if not installed
*/
protected static Double getPgcryptoInstalledVersion(Connection connection) {
Double version = null;
String checkPgCryptoInstalled = "SELECT extversion AS version FROM pg_extension WHERE extname=?";
// Run the query to obtain the version of 'pgcrypto' installed on this database
try (PreparedStatement statement = connection.prepareStatement(checkPgCryptoInstalled)) {
statement.setString(1, PGCRYPTO);
try (ResultSet results = statement.executeQuery()) {
if (results.next()) {
version = results.getDouble("version");
}
}
} catch (SQLException e) {
throw new FlywayException("Unable to determine whether 'pgcrypto' extension is installed.", e);
}
return version;
}
/**
* Check if the pgcrypto extension is BOTH installed AND up-to-date.
*
* This requirement is only needed for PostgreSQL databases.
* It doesn't matter what schema pgcrypto is installed in, as long as it exists.
*
* @return true if everything is installed and up-to-date. False otherwise.
*/
public static boolean isPgcryptoUpToDate() {
// Get our configured dataSource
DataSource dataSource = DatabaseUtils.getDataSource();
try (Connection connection = dataSource.getConnection()) {
Double pgcryptoInstalled = getPgcryptoInstalledVersion(connection);
// Check if installed & up-to-date in this DSpace database
if (pgcryptoInstalled != null && pgcryptoInstalled.compareTo(PGCRYPTO_VERSION) >= 0) {
return true;
}
return false;
} catch (SQLException e) {
throw new FlywayException("Unable to determine whether 'pgcrypto' extension is up-to-date.", e);
}
}
/**
* Check if the pgcrypto extension is installed into a particular schema
*
* This allows us to check if pgcrypto needs to be REMOVED prior to running
* a 'clean' on this database. If pgcrypto is in the same schema as the
* dspace database, a 'clean' will require removing pgcrypto FIRST.
*
* @param schema name of schema
* @return true if pgcrypto is in this schema. False otherwise.
*/
public static boolean isPgcryptoInSchema(String schema) {
// Get our configured dataSource
DataSource dataSource = DatabaseUtils.getDataSource();
try (Connection connection = dataSource.getConnection()) {
// Check if pgcrypto is installed in the current database schema.
String pgcryptoInstalledInSchema = "SELECT extversion FROM pg_extension,pg_namespace " +
"WHERE pg_extension.extnamespace=pg_namespace.oid " +
"AND extname=? " +
"AND nspname=?;";
Double pgcryptoVersion = null;
try (PreparedStatement statement = connection.prepareStatement(pgcryptoInstalledInSchema)) {
statement.setString(1, PGCRYPTO);
statement.setString(2, schema);
try (ResultSet results = statement.executeQuery()) {
if (results.next()) {
pgcryptoVersion = results.getDouble("extversion");
}
}
}
// If a pgcrypto version returns, it's installed in this schema
if (pgcryptoVersion != null) {
return true;
} else {
return false;
}
} catch (SQLException e) {
throw new FlywayException(
"Unable to determine whether 'pgcrypto' extension is installed in schema '" + schema + "'.", e);
}
}
/**
* Check if the current user has permissions to run a clean on existing
* database.
*
* Mostly this just checks if you need to remove pgcrypto, and if so,
* whether you have permissions to do so.
*
* @param connection database connection
* @return true if permissions valid, false otherwise
*/
protected static boolean checkCleanPermissions(Connection connection) {
try {
// get username of our db user
String username = connection.getMetaData().getUserName();
// Check their permissions. Are they a 'superuser'?
String checkSuperuser = "SELECT rolsuper FROM pg_roles WHERE rolname=?;";
boolean superuser = false;
try (PreparedStatement statement = connection.prepareStatement(checkSuperuser)) {
statement.setString(1, username);
try (ResultSet results = statement.executeQuery()) {
if (results.next()) {
superuser = results.getBoolean("rolsuper");
}
}
} catch (SQLException e) {
throw new FlywayException("Unable to determine if user '" + username + "' is a superuser.", e);
}
// If user is a superuser, then "clean" can be run successfully
if (superuser) {
return true;
} else {
// Otherwise, we'll need to see which schema 'pgcrypto' is installed in
// Get current schema name
String schema = getSchemaName(connection);
// If pgcrypto is installed in this schema, then superuser privileges are needed to remove it
if (isPgcryptoInSchema(schema)) {
return false;
} else {
// otherwise, a 'clean' can be run by anyone
return true;
}
}
} catch (SQLException e) {
throw new FlywayException("Unable to determine if DB user has 'clean' privileges.", e);
}
}
}