Isn't pg_statistic a security hole?

Being a simple user, I still want
to view the stats from the table,
but it should be limited only
to the stuff I own. I don't wanna
let others see any of my info, however.
The SU's, of course, should be able to read
all the stats.

----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: <pgsql-hackers@postgresql.org>
Sent: Sunday, May 06, 2001 1:14 PM
Subject: [HACKERS] Isn't pg_statistic a security hole?

"Serguei Mokhov" <sa_mokho@alcor.concordia.ca> writes:

Being a simple user, I still want to view the stats from the table,
but it should be limited only to the stuff I own. I don't wanna let
others see any of my info, however. The SU's, of course, should be
able to read all the stats.

This is infeasible since we don't have a concept of per-row permissions.
It's all or nothing.

regards, tom lane

On Sun, 6 May 2001, Tom Lane wrote:

"Serguei Mokhov" <sa_mokho@alcor.concordia.ca> writes:

Being a simple user, I still want to view the stats from the table,
but it should be limited only to the stuff I own. I don't wanna let
others see any of my info, however. The SU's, of course, should be
able to read all the stats.

This is infeasible since we don't have a concept of per-row permissions.
It's all or nothing.

Maybe make statistics readable only by superusers with a view that uses
CURRENT_USER or something like that to only give the objects that
have owners of this user? Might be an ugly view, but...

"Serguei Mokhov" <sa_mokho@alcor.concordia.ca> writes:

Being a simple user, I still want to view the stats from the table,
but it should be limited only to the stuff I own. I don't wanna let
others see any of my info, however. The SU's, of course, should be
able to read all the stats.

This is infeasible since we don't have a concept of per-row permissions.
It's all or nothing.

You can acheive the same effect using a view if the statistics table has the
user name included.

Joe

test=# select version();
version
-----------------------------------------------------------
PostgreSQL 7.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

create table teststat(username name,stat_id int4,stat_val float, primary
key(username,stat_id));
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'teststat_pkey'
for table 'teststat'
CREATE
insert into teststat values('postgres',1,15.321);
INSERT 1007064 1
insert into teststat values('foo',1,12.123);
INSERT 1007065 1
select * from teststat;
username | stat_id | stat_val
----------+---------+----------
postgres | 1 | 15.321
foo | 1 | 12.123
(2 rows)

create view vw_teststat as (select * from teststat where
(username=current_user or current_user='postgres'));
CREATE
select current_user;
current_user
--------------
postgres
(1 row)

select * from vw_teststat;
username | stat_id | stat_val
----------+---------+----------
postgres | 1 | 15.321
foo | 1 | 12.123
(2 rows)

create user foo;
CREATE USER
grant select on vw_teststat to foo;
CHANGE
You are now connected as new user foo.
select current_user;
current_user
--------------
foo
(1 row)

select * from vw_teststat;
username | stat_id | stat_val
----------+---------+----------
foo | 1 | 12.123
(1 row)

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:

This is infeasible since we don't have a concept of per-row permissions.
It's all or nothing.

Maybe make statistics readable only by superusers with a view that uses
CURRENT_USER or something like that to only give the objects that
have owners of this user? Might be an ugly view, but...

Hmm, that would work --- you could join against pg_class to find out the
owner of the relation. While you were at it, maybe look up the
attribute name in pg_attribute as well. Anyone want to propose a
specific view definition?

regards, tom lane

Hmm, that would work --- you could join against pg_class to find out the
owner of the relation. While you were at it, maybe look up the
attribute name in pg_attribute as well. Anyone want to propose a
specific view definition?

How does this work?

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.usename
from
pg_statistic as s
,pg_class as c
,pg_shadow as sh
,pg_attribute as a
where
(sh.usename=current_user or current_user='postgres')
and sh.usesysid = c.relowner
and a.attrelid = c.oid
and c.oid = s.starelid
);

-- Joe

Tom Lane wrote:

"Serguei Mokhov" <sa_mokho@alcor.concordia.ca> writes:

Being a simple user, I still want to view the stats from the table,
but it should be limited only to the stuff I own. I don't wanna let
others see any of my info, however. The SU's, of course, should be
able to read all the stats.

This is infeasible since we don't have a concept of per-row permissions.
It's all or nothing.

Can't we provide a view that shows those rows from
pg_statistics that belong to the tables owned by the current
user?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

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.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

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.

A fine example, indeed ;-)

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.

Well, mumble. I routinely ask people who're complaining of bad plans
for extracts from their pg_statistic table. I don't foresee that need
vanishing any time soon :-(. The idea of a view seemed nice, in part
because it could be set up to give all the useful info with a simple

select * from pg_statview where relname = 'foo';

rather than the messy three-way join you have to type now.

regards, tom lane

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.

Well, mumble. I routinely ask people who're complaining of bad plans
for extracts from their pg_statistic table. I don't foresee that need
vanishing any time soon :-(. The idea of a view seemed nice, in part
because it could be set up to give all the useful info with a simple

select * from pg_statview where relname = 'foo';

rather than the messy three-way join you have to type now.

Sounds fine, but aren't most people who we ask for stats superusers?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Sounds fine, but aren't most people who we ask for stats superusers?

Are they? I don't think we should assume that.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Sounds fine, but aren't most people who we ask for stats superusers?

Are they? I don't think we should assume that.

OK, just asking.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

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:

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.usename
from
pg_statistic as s
,pg_class as c
,pg_shadow as sh
,pg_attribute as a
where
has_privilege(current_user,c.relname,'select')
and sh.usesysid = c.relowner
and a.attrelid = c.oid
and 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.