Обсуждение: AW: Isn't pg_statistic a security hole?

> > Right now anyone can look in pg_statistic and discover the min/max/most
> > common values of other people's tables.  That's not a lot of info, but
> > it might still be more than you want them to find out.  And the
> > statistical changes that I'm about to commit will allow a couple dozen
> > values to be exposed, not only three values per column.
> > 
> > It seems to me that only superusers should be allowed to read the
> > pg_statistic table.  Or am I overreacting?  Comments?
> 
> You are not overreacting.  Imagine a salary column.  I can imagine
> max/min being quite interesting.
> 
> I doubt it is worth letting non-super users see values in that table. 
> Their only value is in debugging the optimizer, which seems like a
> super-user job anyway.

How about letting them see all statistics where they have select permission 
on the base table (if that is possible with the new permission table) ?

Andreas

Zeugswetter Andreas SB  <ZeugswetterA@wien.spardat.at> writes:
> How about letting them see all statistics where they have select permission 
> on the base table (if that is possible with the new permission table) ?

Yeah, I was thinking the same thing.  If we restrict the view on the
basis of current_user being the owner, then we'd have the annoying
problem that superusers *couldn't* use the view for tables they didn't
own.

To implement this, we'd need a SQL function that answers the question
"does user A have read permission on table B?", which is something that
people have asked for in the past anyway.  (The existing SQL functions
for manipulating ACLs are entirely unhelpful for determining this.)

Someone needs to come up with a spec for such a function --- do we
specify user and table by names or by OIDs, how is the interesting
permission represented, etc.  Is there anything comparable defined by
SQL99 or in other DBMSes?
        regards, tom lane

I can say what oracle does in this regard.  For information like this 
Oracle will generally have three views in the data dictionary:

1) USER_XXX  - shows records where the current user is the owner of the 
item in question
2) ALL_XXX - shows records for all items accessible by the current user
3) DBA_XXX - shows records for all items, only available for DBA's or 
superusers

Where XXX are things like: TABLES, VIEWS, TAB_COL_STATISTICS, INDEXES, 
TRIGGERS, etc (about 120 in all).

thanks,
--Barry

Tom Lane wrote:

> Zeugswetter Andreas SB  <ZeugswetterA@wien.spardat.at> writes:
> 
>> How about letting them see all statistics where they have select permission 
>> on the base table (if that is possible with the new permission table) ?
> 
> 
> Yeah, I was thinking the same thing.  If we restrict the view on the
> basis of current_user being the owner, then we'd have the annoying
> problem that superusers *couldn't* use the view for tables they didn't
> own.
> 
> To implement this, we'd need a SQL function that answers the question
> "does user A have read permission on table B?", which is something that
> people have asked for in the past anyway.  (The existing SQL functions
> for manipulating ACLs are entirely unhelpful for determining this.)
> 
> Someone needs to come up with a spec for such a function --- do we
> specify user and table by names or by OIDs, how is the interesting
> permission represented, etc.  Is there anything comparable defined by
> SQL99 or in other DBMSes?
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl
> 
> 

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.