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?

			regards, tom lane

Sun, 06 May 2001 13:14:46 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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?


> 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?
> 
> regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


Sun, 6 May 2001 13:23:03 -0400
"Serguei Mokhov" <sa_mokho@alcor.concordia.ca>
Re: 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

Sun, 06 May 2001 13:27:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Isn't pg_statistic a security hole? 

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



Sun, 6 May 2001 11:03:40 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: 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.
>

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)



Sun, 6 May 2001 11:35:49 -0700
"Joe Conway" <joe@conway-family.com>
Re: Isn't pg_statistic a security hole? 
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

Sun, 06 May 2001 15:12:03 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Isn't pg_statistic a security hole? 
> 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


Sun, 6 May 2001 13:01:58 -0700
"Joe Conway" <joe@conway-family.com>
Re: Isn't pg_statistic a security hole? 
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


Mon, 7 May 2001 12:07:56 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: 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.

-- 
  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

Mon, 7 May 2001 13:37:45 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Isn't pg_statistic a security hole?
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

Mon, 07 May 2001 18:54:21 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Isn't pg_statistic a security hole? 
> > 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

Mon, 7 May 2001 19:02:08 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Isn't pg_statistic a security hole?
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

Mon, 07 May 2001 19:35:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Isn't pg_statistic a security hole? 
> 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

Mon, 7 May 2001 19:36:13 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Isn't pg_statistic a security hole?