Обсуждение: 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
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
"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.321foo | 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.321foo | 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.usenamefrom pg_statistic as s ,pg_class as c ,pg_shadow as sh ,pg_attributeas awhere (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, Pennsylvania19026
> > 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, Pennsylvania19026
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
> 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, Pennsylvania19026
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