Re: Filtering DatabaseMetaData to show only the items for which the current user has access

I think a connection parameter patch might be acceptable.

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 12 September 2016 at 18:54, Langley, Scott E <slangley(at)scharp(dot)org> wrote:

> 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(at)scharp(dot)org
>
> (206) 667-5117
>
> Fax (206) 667-4812
>
>
>