Isn't pg_statistic a security hole - Solution Proposal - Mailing list pgsql-hackers

The recent discussions on pg_statistic got me started thinking about how to
implement a secure form of the view. Based on the list discussion, and a
suggestion from Tom, I did some research regarding how SQL92 and some of the
larger commercial database systems allow access to system privilege
information.

I reviewed the ANSI SQL 92 specification, Oracle, MSSQL, and IBM DB2
(documentation only). Here's what I found:

ANSI SQL 92 does not have any functions defined for retrieving privilege
information. It does, however define an "information schema" and "definition
schema" which among other things includes a TABLE_PRIVILEGES view.

With this view available, it is possible to discern what privileges the
current user has using a simple SQL statement. In Oracle, I found this view,
and some other variations. According to the Oracle DBA I work with, there is
no special function, and a SQL statement on the view is how he would gather
this kind of information when needed.

MSSQL Server 7 also has this same view. Additionally, SQL7 has a T-SQL
function called PERMISSIONS with the following description:
"Returns a value containing a bitmap that indicates the statement, object,
or column permissions for the current user.
Syntax PERMISSIONS([objectid [, 'column']])".

I only looked briefly at the IBM DB2 documentation, but could find no
mention of TABLE_PRIVILEGES or any privilege specific function. I imagine
TABLE_PRIVILEGES might be there somewhere since it seems to be standard
SQL92.

Based on all of the above, I concluded that there is nothing compelling in
terms of a specific function to be compatible with. I do think that in the
longer term it makes sense to implement the SQL 92 information schema views
in PostgreSQL.

So, now for the proposal. I created a function (attached) which will allow
any privilege type to be probed, called has_privilege. It is used like this:
 select relname from pg_class where has_privilege(current_user, relname,
'update');

or
 select has_privilege('postgres', 'pg_shadow', 'select');

where the first parameter is any valid user name the second parameter can be a table, view, or sequence the third
parameter can be 'select', 'insert', 'update', 'delete', or
 
'rule'

The function is currently implemented as an external c function and designed
to be built under contrib. This function should really be an internal
function. If the proposal is acceptable, I would like to take on the task of
turning the function into an internal one (with guidance, pointers,
suggestions greatly appreciated). This would allow a secure view to be
implemented over pg_statistic as:

create view pg_userstat as (select  s.starelid ,s.staattnum ,s.staop ,s.stanullfrac ,s.stacommonfrac ,s.stacommonval
,s.staloval,s.stahival ,c.relname ,a.attname ,sh.usenamefrom  pg_statistic as s ,pg_class as c ,pg_shadow as sh
,pg_attributeas awhere has_privilege(current_user,c.relname,'select') and sh.usesysid = c.relowner and a.attrelid =
c.oidand c.oid = s.starelid
 
);

Then restrict pg_statistic from public viewing. This view would allow the
current user to view statistics only on relations for which they already
have 'select' granted.

Comments?

Regards,
-- Joe

installation:

place in contrib
tar -xzvf has_priv.tgz
cd has_priv
./install.sh
Note: installs the function into template1 by default. Edit install.sh to
change.