CODEWITHSUNDEEP
listpostgresqljdbcdatabase-schema
Tim Autin
Tim Autin

Reputation: 6165

How to list PostgreSQL databases on server via JDBC driver?

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?

Upvotes: 2

Views: 1089

Answers (1)

Matthieu
Matthieu

Reputation: 3097

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.

Upvotes: 1

Related Questions