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