postgresql-jdbc.git / summary / log / commit / refs
commit 4f1142e49201c4f3794b02548bfea1cbc536caaa
Author: Sehrope Sarkuni <sehrope@jackdb.com>
Date: Mon Jun 07 15:07:36 2021 +0000
fix: Change metadata getFunctions() and getProcedures() to ignore search_path visibility
Previously if no schema pattern was specified then a default filter was added to getFunctions()
and getProcedures() to restrict the returned set to only those that are visible to the user
via search_path.
This removes that filter so that calling either function with a null or empty schema pattern
will return all functions or procedures.
CHANGELOG.md | 1 +
.../org/postgresql/jdbc/PgDatabaseMetaData.java | 6 -
.../test/jdbc4/DatabaseMetaDataTest.java | 229 ++++++++++++---------
3 files changed, 137 insertions(+), 99 deletions(-)
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 57551e3b..b33724a6 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -12,6 +12,7 @@ The format is based on [Keep a Changelog](http://keepachangelog.com/en/1.0.0/).
### Fixed
- Rework OSGi bundle activator so it does not rely on exception message to check DataSourceFactory presence PR [#507](https://github.com/pgjdbc/pgjdbc/pull/507)
- Fix "Avoid leaking server error details through BatchUpdateException when logServerErrorDetail=false" [PR #2148](https://github.com/pgjdbc/pgjdbc/pull/2148) fixes Issue #2147
+- Fix database metadata getFunctions() and getProcedures() to ignore search_path when no schema pattern is specified [PR #2174](https://github.com/pgjdbc/pgjdbc/pull/2174)
## [42.2.20] (2021-04-19)
diff --git a/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java b/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java
index 4f1b8bb8..38667f9d 100644
--- a/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java
+++ b/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java
@@ -1051,9 +1051,6 @@ public class PgDatabaseMetaData implements DatabaseMetaData {
}
if (schemaPattern != null && !schemaPattern.isEmpty()) {
sql += " AND n.nspname LIKE " + escapeQuotes(schemaPattern);
- } else {
- /* limit to current schema if no schema given */
- sql += "and pg_function_is_visible(p.oid)";
}
if (procedureNamePattern != null && !procedureNamePattern.isEmpty()) {
sql += " AND p.proname LIKE " + escapeQuotes(procedureNamePattern);
@@ -2797,9 +2794,6 @@ public class PgDatabaseMetaData implements DatabaseMetaData {
*/
if (schemaPattern != null && !schemaPattern.isEmpty()) {
sql += " AND n.nspname LIKE " + escapeQuotes(schemaPattern);
- } else {
- /* if no schema is provided then limit the search inside the search_path */
- sql += "and pg_function_is_visible(p.oid)";
}
if (functionNamePattern != null && !functionNamePattern.isEmpty()) {
sql += " AND p.proname LIKE " + escapeQuotes(functionNamePattern);
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc4/DatabaseMetaDataTest.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc4/DatabaseMetaDataTest.java
index b77e25c9..9e082d6f 100644
--- a/pgjdbc/src/test/java/org/postgresql/test/jdbc4/DatabaseMetaDataTest.java
+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc4/DatabaseMetaDataTest.java
@@ -18,6 +18,7 @@ import org.postgresql.test.SlowTests;
import org.postgresql.test.TestUtil;
import org.junit.After;
+import org.junit.Assume;
import org.junit.Before;
import org.junit.Test;
import org.junit.experimental.categories.Category;
@@ -108,125 +109,102 @@ public class DatabaseMetaDataTest {
@Test
public void testGetFunctionsInSchemaForFunctions() throws SQLException {
DatabaseMetaData dbmd = conn.getMetaData();
- ResultSet rs = dbmd.getFunctions("", "hasfunctions","");
- int count = assertGetFunctionRS(rs);
- assertThat( count, is(1));
-
- Statement statement = conn.createStatement();
- statement.execute("set search_path=hasfunctions");
-
- rs = dbmd.getFunctions("", "","addfunction");
- assertThat( assertGetFunctionRS(rs), is(1) );
- statement.execute("set search_path=nofunctions");
-
- rs = dbmd.getFunctions("", "","addfunction");
- assertFalse(rs.next());
-
- statement.execute("reset search_path");
- statement.close();
+ try (ResultSet rs = dbmd.getFunctions("", "hasfunctions","")) {
+ List<CatalogObject> list = assertFunctionRSAndReturnList(rs);
+ assertEquals("There should be one function in the hasfunctions schema", list.size(), 1);
+ assertListContains("getFunctions('', 'hasfunctions', '') must contain addfunction", list, "hasfunctions", "addfunction");
+ }
- rs = dbmd.getFunctions("", "nofunctions",null);
- assertFalse(rs.next());
+ try (ResultSet rs = dbmd.getFunctions("", "hasfunctions", "addfunction")) {
+ List<CatalogObject> list = assertFunctionRSAndReturnList(rs);
+ assertEquals("There should be one function in the hasfunctions schema with name addfunction", list.size(), 1);
+ assertListContains("getFunctions('', 'hasfunctions', 'addfunction') must contain addfunction", list, "hasfunctions", "addfunction");
+ }
+ try (ResultSet rs = dbmd.getFunctions("", "nofunctions","")) {
+ boolean hasFunctions = rs.next();
+ assertFalse("There should be no functions in the nofunctions schema", hasFunctions);
+ }
}
@Test
public void testGetFunctionsInSchemaForProcedures() throws SQLException {
// Due to the introduction of actual stored procedures in PostgreSQL 11, getFunctions should not return procedures for PostgreSQL versions 11+
- if (TestUtil.haveMinimumServerVersion(conn, ServerVersion.v11)) {
-
- DatabaseMetaData dbmd = conn.getMetaData();
- Statement statement = conn.createStatement();
-
- // Search for functions in schema "hasprocedures"
- ResultSet rs = dbmd.getFunctions("", "hasprocedures", null);
- Boolean recordFound = rs.next();
- assertEquals("PostgreSQL11+ should not return procedures from getFunctions", recordFound, false);
-
- // Search for functions in schema "noprocedures" (which should never expect records)
- rs = dbmd.getFunctions("", "noprocedures", null);
- recordFound = rs.next();
- assertFalse(recordFound);
-
- // Search for functions by procedure name "addprocedure" within schema "hasprocedures"
- statement.execute("set search_path=hasprocedures");
- rs = dbmd.getFunctions("", "", "addprocedure");
- recordFound = rs.next();
- assertEquals("PostgreSQL11+ should not return procedures from getFunctions", recordFound, false);
-
- // Search for functions by procedure name "addprocedure" within schema "noprocedures" (which should never expect records)
- statement.execute("set search_path=noprocedures");
- rs = dbmd.getProcedures("", "", "addprocedure");
- recordFound = rs.next();
- assertFalse(recordFound);
+ // On older installation we do not create the procedures so the below schemas should all be empty
+ DatabaseMetaData dbmd = conn.getMetaData();
- statement.close();
+ // Search for functions in schema "hasprocedures"
+ try (ResultSet rs = dbmd.getFunctions("", "hasprocedures", null)) {
+ assertFalse("The hasprocedures schema not return procedures from getFunctions", rs.next());
+ }
+ // Search for functions in schema "noprocedures" (which should never expect records)
+ try (ResultSet rs = dbmd.getFunctions("", "noprocedures", null)) {
+ assertFalse("The noprocedures schema should not have functions", rs.next());
+ }
+ // Search for functions by procedure name "addprocedure"
+ try (ResultSet rs = dbmd.getFunctions("", "hasprocedures", "addprocedure")) {
+ assertFalse("Should not return procedures from getFunctions by schema + name", rs.next());
}
}
@Test
public void testGetProceduresInSchemaForFunctions() throws SQLException {
// Due to the introduction of actual stored procedures in PostgreSQL 11, getProcedures should not return functions for PostgreSQL versions 11+
-
DatabaseMetaData dbmd = conn.getMetaData();
- Statement statement = conn.createStatement();
// Search for procedures in schema "hasfunctions" (which should expect a record only for PostgreSQL < 11)
- ResultSet rs = dbmd.getProcedures("", "hasfunctions",null);
- Boolean recordFound = rs.next();
- if (TestUtil.haveMinimumServerVersion(conn, ServerVersion.v11)) {
- assertEquals("PostgreSQL11+ should not return functions from getProcedures", recordFound, false);
- } else {
- assertEquals("PostgreSQL prior to 11 should return functions from getProcedures", recordFound, true);
+ try (ResultSet rs = dbmd.getProcedures("", "hasfunctions",null)) {
+ boolean found = rs.next();
+ if (TestUtil.haveMinimumServerVersion(conn, ServerVersion.v11)) {
+ assertFalse("PostgreSQL11+ should not return functions from getProcedures", found);
+ } else {
+ assertTrue("PostgreSQL prior to 11 should return functions from getProcedures", found);
+ }
}
// Search for procedures in schema "nofunctions" (which should never expect records)
- rs = dbmd.getProcedures("", "nofunctions",null);
- recordFound = rs.next();
- assertFalse(recordFound);
+ try (ResultSet rs = dbmd.getProcedures("", "nofunctions", null)) {
+ assertFalse("getProcedures(...) should not return procedures for schema nofunctions", rs.next());
+ }
// Search for procedures by function name "addfunction" within schema "hasfunctions" (which should expect a record for PostgreSQL < 11)
- statement.execute("set search_path=hasfunctions");
- rs = dbmd.getProcedures("", "","addfunction");
- recordFound = rs.next();
- if (TestUtil.haveMinimumServerVersion(conn, ServerVersion.v11)) {
- assertEquals("PostgreSQL11+ should not return functions from getProcedures", recordFound, false);
- } else {
- assertEquals("PostgreSQL prior to 11 should return functions from getProcedures", recordFound, true);
+ try (ResultSet rs = dbmd.getProcedures("", "hasfunctions", "addfunction")) {
+ boolean found = rs.next();
+ if (TestUtil.haveMinimumServerVersion(conn, ServerVersion.v11)) {
+ assertFalse("PostgreSQL11+ should not return functions from getProcedures", found);
+ } else {
+ assertTrue("PostgreSQL prior to 11 should return functions from getProcedures", found);
+ }
}
// Search for procedures by function name "addfunction" within schema "nofunctions" (which should never expect records)
- statement.execute("set search_path=nofunctions");
- rs = dbmd.getProcedures("", "","addfunction");
- recordFound = rs.next();
- assertFalse(recordFound);
-
- statement.close();
+ try (ResultSet rs = dbmd.getProcedures("", "nofunctions", "addfunction")) {
+ assertFalse("getProcedures(...) should not return procedures for schema nofunctions + addfunction", rs.next());
+ }
}
@Test
public void testGetProceduresInSchemaForProcedures() throws SQLException {
// Only run this test for PostgreSQL version 11+; assertions for versions prior would be vacuously true as we don't create a procedure in the setup for older versions
- if (TestUtil.haveMinimumServerVersion(conn, ServerVersion.v11)) {
- DatabaseMetaData dbmd = conn.getMetaData();
- Statement statement = conn.createStatement();
+ Assume.assumeTrue(TestUtil.haveMinimumServerVersion(conn, ServerVersion.v11));
- ResultSet rs = dbmd.getProcedures("", "hasprocedures", null);
- assertTrue(rs.next());
+ DatabaseMetaData dbmd = conn.getMetaData();
- rs = dbmd.getProcedures("", "nofunctions", null);
- assertFalse(rs.next());
+ try (ResultSet rs = dbmd.getProcedures("", "hasprocedures", null)) {
+ assertTrue("getProcedures() should be non-empty for the hasprocedures schema", rs.next());
+ }
- statement.execute("set search_path=hasprocedures");
- rs = dbmd.getProcedures("", "", "addprocedure");
- assertTrue(rs.next());
+ try (ResultSet rs = dbmd.getProcedures("", "noprocedures", null)) {
+ assertFalse("getProcedures() should be empty for the hasprocedures schema", rs.next());
+ }
- statement.execute("set search_path=noprocedures");
- rs = dbmd.getProcedures("", "", "addprocedure");
- assertFalse(rs.next());
+ try (ResultSet rs = dbmd.getProcedures("", "hasfunctions", null)) {
+ assertFalse("getProcedures() should be empty for the nofunctions schema", rs.next());
+ }
- statement.close();
+ try (ResultSet rs = dbmd.getProcedures("", "nofunctions", null)) {
+ assertFalse("getProcedures() should be empty for the nofunctions schema", rs.next());
}
}
@@ -235,6 +213,7 @@ public class DatabaseMetaDataTest {
public void testGetFunctionsWithBlankPatterns() throws SQLException {
int minFuncCount = 1000;
DatabaseMetaData dbmd = conn.getMetaData();
+
ResultSet rs = dbmd.getFunctions("", "", "");
int count = assertGetFunctionRS(rs);
assertThat(count > minFuncCount, is(true));
@@ -273,8 +252,68 @@ public class DatabaseMetaDataTest {
rs5.close();
}
+ private static class CatalogObject implements Comparable<CatalogObject> {
+ private final String catalog;
+ private final String schema;
+ private final String name;
+ private final String specificName;
+
+ private CatalogObject(String catalog, String schema, String name, String specificName) {
+ this.catalog = catalog;
+ this.schema = schema;
+ this.name = name;
+ this.specificName = specificName;
+ }
+
+ @Override
+ public int hashCode() {
+ final int prime = 31;
+ int result = 1;
+ result = prime * result + ((catalog == null) ? 0 : catalog.hashCode());
+ result = prime * result + ((name == null) ? 0 : name.hashCode());
+ result = prime * result + ((schema == null) ? 0 : schema.hashCode());
+ result = prime * result + ((specificName == null) ? 0 : specificName.hashCode());
+ return result;
+ }
+
+ @Override
+ public boolean equals(Object obj) {
+ if (obj == null || getClass() != obj.getClass()) {
+ return false;
+ } else if (obj == this) {
+ return true;
+ }
+ return compareTo((CatalogObject)obj) == 0;
+ }
+
+ @Override
+ public int compareTo(CatalogObject other) {
+ int comp = catalog.compareTo(other.catalog);
+ if (comp != 0) {
+ return comp;
+ }
+ comp = schema.compareTo(other.schema);
+ if (comp != 0) {
+ return comp;
+ }
+ comp = name.compareTo(other.name);
+ if (comp != 0) {
+ return comp;
+ }
+ comp = specificName.compareTo(other.specificName);
+ if (comp != 0) {
+ return comp;
+ }
+ return 0;
+ }
+ }
+
/** Assert some basic result from ResultSet of a GetFunctions method. Return the total row count. */
private int assertGetFunctionRS(ResultSet rs) throws SQLException {
+ return assertFunctionRSAndReturnList(rs).size();
+ }
+
+ private List<CatalogObject> assertFunctionRSAndReturnList(ResultSet rs) throws SQLException {
// There should be at least one row
assertThat(rs.next(), is(true));
assertThat(rs.getString("FUNCTION_CAT"), is(System.getProperty("database")));
@@ -294,22 +333,26 @@ public class DatabaseMetaDataTest {
// Get all result and assert they are ordered per javadoc spec:
// FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME and SPECIFIC_NAME
- List<String> result = new ArrayList<String>();
+ List<CatalogObject> result = new ArrayList<>();
do {
- result.add(rs.getString("FUNCTION_CAT")
- + " "
- + rs.getString("FUNCTION_SCHEM")
- + " "
- + rs.getString("FUNCTION_NAME")
- + " "
- + rs.getString("SPECIFIC_NAME"));
+ CatalogObject obj = new CatalogObject(
+ rs.getString("FUNCTION_CAT"),
+ rs.getString("FUNCTION_SCHEM"),
+ rs.getString("FUNCTION_NAME"),
+ rs.getString("SPECIFIC_NAME"));
+ result.add(obj);
} while (rs.next());
- List<String> orderedResult = new ArrayList<String>(result);
+ List<CatalogObject> orderedResult = new ArrayList<>(result);
Collections.sort(orderedResult);
assertThat(result, is(orderedResult));
- return result.size();
+ return result;
+ }
+
+ private void assertListContains(String message, List<CatalogObject> list, String schema, String name) throws SQLException {
+ boolean found = list.stream().anyMatch(item -> item.schema.equals(schema) && item.name.equals(name));
+ assertTrue(message + "; schema=" + schema + " name=" + name, found);
}
@Test
[parent: 45a771fccd77]