Thread: 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.