Filtering DatabaseMetaData to show only the items for which thecurrent user has access - Mailing list pgsql-jdbc

Hello PostgreSQL JDBC Developers,

We have a desire to hide unneeded database objects from our database-challenged users - as they might see in a simple database viewer application - by removing their privileges on such objects.

It appears that many database viewing applications, e.g., DbVisualizer, rely on what is returned by the JDBC driver’s DatabaseMetaData methods to determine which schemas and tables to display to the user:

http://confluence.dbvis.com/display/UG95/Understanding+Database+Profiles

The generic database profile (the only profile available in DbVisualizer Free) displays objects based on what JDBC offers in terms of database information (aka metadata information). DbVisualizer asks the JDBC driver for all schemas, databases, tables and procedures, and then builds the tree based on what the driver returns.

So I’d like for a way for the Postgres JDBC driver to filter out schemas, tables, and other database objects for which the current user has no privileges.

One way might be to filter what metadata is returned in the DatabaseMetaData methods by requiring any values to be present in the corresponding privilege inquiry functions for the current user:

https://www.postgresql.org/docs/devel/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE

Another way would be to:          

1.       Modify the information_schema views for a particular database to only show the current user the objects for which they have privileges.

2.       Then, have the PostgreSQL JDBC driver populate its DatabaseMetatData information only using the contents of the information_schema and not query pg_catalog, pg_class, and the other Postgres-specific system information tables.

If either of these behaviors could be implemented as either a connection parameter or a JDBC driver compile-time option, that would be great.

I believe this filtering behavior is implemented in MySQL out of the box because of the way its backend is implemented:

http://dev.mysql.com/doc/refman/5.7/en/information-schema.html

Privileges Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA.ROUTINES table), users who have insufficient privileges see NULL. These restrictions do not apply for InnoDB tables; you can see them with only the PROCESS privilege. The same privileges apply to selecting information from INFORMATION_SCHEMA and viewing the same information through SHOW statements. In either case, you must have some privilege on an object to see information about it.

If you would accept a patch along these lines, which approach would be preferable?

Thanks for your consideration.

--

Scott Langley

Systems Analyst/Programmer

Statistical Center for HIV/AIDS Research and Prevention (SCHARP)

Fred Hutchinson Cancer Research Center

Seattle, Washington

slangley@scharp.org

(206) 667-5117

Fax (206) 667-4812