How to list PostgreSQL databases on server via JDBC driver?

Question

I have to make a graphical interface allowing the user to enter an IP / port (and ideally nothing else), and listing all the PostgreSQL databases found at this address.

The pg_hba.conf file on the server would be configured this way:

host    all    all    0.0.0.0/0    md5

Is there a way to list the databases without being connected to one of them?

If I set the server's config this way:

host    all    all    0.0.0.0/0    trust

I can connect to the postgres database with the postgres user, and list the databases, this way:

try (
    Connection conn = DriverManager.getConnection("jdbc:postgresql://<ip>:<port>/postgres", "postgres", null);
    PreparedStatement ps = conn.prepareStatement("SELECT datname FROM pg_database WHERE datistemplate = false;");
    ResultSet rs = ps.executeQuery()) {

    while (rs.next()) {
        System.out.println(rs.getString(1));
    }
}
catch (Exception e) { e.printStackTrace(System.err); }

But the trust option is really unsecured, can't use that. But there is no way to connect to that user with JDBC without giving him a password, right?

Answer 1

No way, as you said: you can't query any database (and listing databases is a kind of query) without connecting to it first.

Hopefully, PostgreSQL has a built-in security system in pg_hba.conf as you saw. You'll have to tune it (on top of your firewall) to allow only:

• specific IPs
• and/or specific users
• with specific authentication mechanisms

Note that PostgreSQL handles SSL so you can trust its connections as much as you can trust any e.g. HTTPS.

See current documentation for more information.