Thread: Strange result from DatabaseMetaData.getTablePrivileges()

Strange result from DatabaseMetaData.getTablePrivileges()

From
Thomas Kellerer
Date:
Hello,

I just noticed that getTablePrivileges() returns the same privilege for the same
user multiple times. Additionally it returns several rows with the privilege
"UNKNOWN".

Is this a bug, or am I simply misunderstanding something?

The following test code:

Class.forName("org.postgresql.Driver");
con = DriverManager.getConnection("jdbc:postgresql://localhost/wbtest",
"thomas", "****");
con.setAutoCommit(true);
stmt = con.createStatement();

stmt.executeUpdate("create table test_table (nr integer)");
stmt.executeUpdate("grant select,insert,update on test_table to postgres");
rs = con.getMetaData().getTablePrivileges(null, "public", "test_table");
while (rs.next())
{
   System.out.print("table_name: " + rs.getString("TABLE_NAME"));
   System.out.print(", grantor: " + rs.getString("GRANTOR"));
   System.out.print(", grantee: " + rs.getString("GRANTEE"));
   System.out.println(", privilege: " + rs.getString("PRIVILEGE"));
}
stmt.executeUpdate("drop table test_table");

produces the following ouput:

table_name: test_table, grantor: thomas, grantee: thomas, privilege: DELETE
table_name: test_table, grantor: thomas, grantee: thomas, privilege: INSERT
table_name: test_table, grantor: thomas, grantee: thomas, privilege: INSERT
table_name: test_table, grantor: thomas, grantee: postgres, privilege: INSERT
table_name: test_table, grantor: thomas, grantee: postgres, privilege: INSERT
table_name: test_table, grantor: thomas, grantee: thomas, privilege: REFERENCES
table_name: test_table, grantor: thomas, grantee: thomas, privilege: SELECT
table_name: test_table, grantor: thomas, grantee: postgres, privilege: SELECT
table_name: test_table, grantor: thomas, grantee: thomas, privilege: TRIGGER
table_name: test_table, grantor: thomas, grantee: thomas, privilege: TRIGGER
table_name: test_table, grantor: thomas, grantee: postgres, privilege: TRIGGER
table_name: test_table, grantor: thomas, grantee: thomas, privilege: UNKNOWN
table_name: test_table, grantor: thomas, grantee: thomas, privilege: UNKNOWN
table_name: test_table, grantor: thomas, grantee: thomas, privilege: UNKNOWN
table_name: test_table, grantor: thomas, grantee: thomas, privilege: UNKNOWN
table_name: test_table, grantor: thomas, grantee: thomas, privilege: UNKNOWN
table_name: test_table, grantor: thomas, grantee: postgres, privilege: UNKNOWN
table_name: test_table, grantor: thomas, grantee: postgres, privilege: UNKNOWN
table_name: test_table, grantor: thomas, grantee: postgres, privilege: UNKNOWN
table_name: test_table, grantor: thomas, grantee: postgres, privilege: UNKNOWN
table_name: test_table, grantor: thomas, grantee: postgres, privilege: UNKNOWN
table_name: test_table, grantor: thomas, grantee: thomas, privilege: UPDATE
table_name: test_table, grantor: thomas, grantee: postgres, privilege: UPDATE

Note that e.g. the INSERT privilege for the user postgres is returned twice,
whereas the SELECT privilege for the user postgres is only returned once (which
is what I would have expected)

And what are all the UNKNOWN entries?

This is with build 504 of the JDBC Driver running on Windows XP against a
Postgres 8.2.1 installed on the same machine using JDK 1.5.

Thomas

Re: Strange result from DatabaseMetaData.getTablePrivileges()

From
Kris Jurka
Date:

On Sun, 28 Jan 2007, Thomas Kellerer wrote:

> I just noticed that getTablePrivileges() returns the same privilege for the
> same user multiple times. Additionally it returns several rows with the
> privilege "UNKNOWN".

This is a known bug.  The ACL parsing code has not been updated since the
7.3 release and the format has changed numerous times.

Kris Jurka