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