Examples with PGPoolingDataSource org.postgresql.ds.PGPoolingDataSource used on opensource projects

Example 1 with PGPoolingDataSource

use of org.postgresql.ds.PGPoolingDataSource in project Gatekeeper by FINRAOS.

the class PostgresDBConnection method grantAccess.

public boolean grantAccess(String user, String password, RoleType role, String address, Integer length) throws SQLException {
    PGPoolingDataSource dataSource = null;
    try {
        dataSource = connect(address);
        JdbcTemplate conn = new JdbcTemplate(dataSource);
        String expirationTime = LocalDateTime.now().plusDays(length).format(DateTimeFormatter.ofPattern(EXPIRATION_TIMESTAMP));
        String userWithSuffix = user + "_" + role.getShortSuffix();
        // Try to revoke the user
        logger.info("Removing " + userWithSuffix + " from " + address + " if they exist.");
        revokeUser(conn, userWithSuffix);
        // Update the gk roles on the DB
        createUser(conn, address, userWithSuffix, password, role, expirationTime);
        return true;
    } catch (Exception ex) {
        logger.error("An exception was thrown while trying to grant access to user " + user + "_" + role.getShortSuffix() + " on address " + address, ex);
        return false;
    } finally {
        if (dataSource != null) {
            dataSource.close();
        }
    }
}

Example 2 with PGPoolingDataSource

use of org.postgresql.ds.PGPoolingDataSource in project Gatekeeper by FINRAOS.

the class PostgresDBConnection method connect.

private PGPoolingDataSource connect(String url) throws SQLException {
    logger.info("Getting connection for " + url);
    logger.info("Creating Datasource connection for " + url);
    PGPoolingDataSource dataSource = new PGPoolingDataSource();
    String dbUrl = "jdbc:postgresql://" + url;
    dataSource.setDataSourceName(url);
    dataSource.setUrl(dbUrl);
    dataSource.setUser(gkUserName);
    dataSource.setPassword(gkUserPassword);
    dataSource.setConnectTimeout(connectTimeout);
    dataSource.setSsl(ssl);
    dataSource.setSslMode(sslMode);
    dataSource.setSslRootCert(sslCert);
    // Do not want to keep the connection after execution
    logger.info("Using the following properties with the connection: " + ssl);
    return dataSource;
}

Example 3 with PGPoolingDataSource

use of org.postgresql.ds.PGPoolingDataSource in project Gatekeeper by FINRAOS.

the class PostgresDBConnection method checkDb.

public List<String> checkDb(String address) throws GKUnsupportedDBException {
    String gkUserCreateRoleCheck = "select rolcreaterole from pg_roles where rolname = 'gatekeeper'";
    String gkRoleCheck = "select rolname from pg_roles where rolname in ('gk_datafix','gk_dba','gk_readonly')";
    List<String> issues = new ArrayList<>();
    List<String> gkRoles = new ArrayList<>();
    gkRoles.addAll(Arrays.asList("gk_datafix", "gk_readonly", "gk_dba"));
    PGPoolingDataSource dataSource = null;
    try {
        logger.info("Checking the gatekeeper setup for " + address);
        dataSource = connect(address);
        JdbcTemplate conn = new JdbcTemplate(dataSource);
        Boolean createRolePermCheckResult = conn.queryForObject(gkUserCreateRoleCheck, Boolean.class);
        List<String> roleCheckResult = conn.queryForList(gkRoleCheck, String.class);
        if (!createRolePermCheckResult) {
            issues.add("gatekeeper user missing createrole");
        }
        gkRoles.removeAll(roleCheckResult);
        if (!gkRoles.isEmpty()) {
            issues.add("missing the following roles: " + gkRoles);
        }
    } catch (SQLException e) {
        logger.error("Error running check query", e);
    } catch (CannotGetJdbcConnectionException ex) {
        logger.error("Failed to connect to DB", ex);
        if (ex.getMessage().contains("password")) {
            issues.add("Password authentication failed for gatekeeper user");
        } else {
            issues.add("Unable to connect to DB (Check network configuration)");
        }
    } finally {
        if (dataSource != null) {
            dataSource.close();
        }
    }
    return issues;
}

Example 4 with PGPoolingDataSource

use of org.postgresql.ds.PGPoolingDataSource in project Gatekeeper by FINRAOS.

the class PostgresDBConnection method checkIfUsersHasTables.

/**
 * Check to see if this user is the owner of any tables on the DB
 *
 * @param address - the url for the db
 * @param users - the list of users to look for
 * @return boolean - true if the user still owns tables, false otherwise (they don't exist)
 *
 * @throws SQLException - if there's an issue executing the query on the database
 */
public List<String> checkIfUsersHasTables(String address, List<String> users) throws SQLException {
    PGPoolingDataSource dataSource = null;
    try {
        dataSource = connect(address);
        JdbcTemplate conn = new JdbcTemplate(dataSource);
        StringBuilder sb = new StringBuilder();
        users.forEach(user -> {
            sb.append("?,");
        });
        String query = "SELECT distinct tableowner FROM pg_tables t where t.tableowner in (" + sb.deleteCharAt(sb.length() - 1).toString() + ")";
        List<String> outcome = conn.queryForList(query, users.toArray(), String.class);
        return outcome;
    } catch (SQLException ex) {
        logger.error("An Error occured while checking to see if the user owns any tables on the database", ex);
        return users;
    } finally {
        if (dataSource != null) {
            dataSource.close();
        }
    }
}

Example 5 with PGPoolingDataSource

use of org.postgresql.ds.PGPoolingDataSource in project vertx-openshift-it by cescoffier.

the class ClientCreationTest method handle.

@Override
public void handle(RoutingContext rc) {
    vertx.<PGPoolingDataSource>executeBlocking(fut -> {
        PGPoolingDataSource dataSource = new PGPoolingDataSource();
        dataSource.setUrl(config.getString("url"));
        dataSource.setUser(config.getString("user"));
        dataSource.setPassword(config.getString("password"));
        try {
            dataSource.initialize();
            fut.complete(dataSource);
        } catch (SQLException e) {
            fut.fail(e);
        }
    }, iniRes -> {
        if (iniRes.failed()) {
            fail(rc, iniRes.cause());
            return;
        }
        PGPoolingDataSource dataSource = iniRes.result();
        rc.addBodyEndHandler(v -> vertx.executeBlocking(fut -> {
            dataSource.close();
            fut.complete();
        }, null));
        JDBCClient jdbcClient = JDBCClient.create(vertx, dataSource);
        rc.addBodyEndHandler(v -> jdbcClient.close());
        jdbcClient.getConnection(ar -> {
            if (ar.failed()) {
                fail(rc, ar.cause());
                return;
            }
            ar.result().close();
            rc.response().setStatusCode(200).end();
        });
    });
}