[SQL] Permissions for foreign keys

[Rick Delaney]

I'm using 7.0 and have noticed that I need to grant SELECT and UPDATE
permissions on any referentially-related tables.  Can/should I get
around this?  A somewhat contrived example:

CREATE TABLE emp (
 id integer PRIMARY KEY,
 salary integer
);
CREATE TABLE proj (
 id integer PRIMARY KEY,
 emp_id integer REFERENCES emp
);
CREATE TABLE bill (
 id integer PRIMARY KEY,
 proj_id integer REFERENCES proj
);
INSERT INTO emp  VALUES (1, 10);
INSERT INTO proj VALUES (1, 1);
INSERT INTO bill VALUES (1, 1);

GRANT ALL ON proj TO someone;

Connect as someone:
=> INSERT INTO proj VALUES (2, 1);
ERROR:  emp: Permission denied.
=> UPDATE proj SET id = 2;
ERROR:  bill: Permission denied.

It appears that I need to grant:
   SELECT,UPDATE on  emp to UPDATE or INSERT into proj.  
   SELECT,UPDATE on bill to UPDATE proj.  

When I grant these permissions, the above statements succeed.

If I don't want users to have UPDATE (or even SELECT) access on the
other tables (bill and emp), but I want referential integrity, what can
I do?

-- 
Rick Delaney

Re: [SQL] Permissions for foreign keys

[Rick Delaney]

"Ross J. Reedstrom" wrote:
> 
> This is a bug that was fixed in 7.1beta for sure, but also 7.0.3,
> I believe. So it's as simple as upgrading.

Thanks, I'll upgrade.  I couldn't find this listed in any changes files
so can you (or anyone) confirm that this is fixed in 7.0.3?  

And does fixed mean that NO permissions are required on the referencing
or referenced tables, or are there still some restrictions?

Also, where can I get the 7.1beta?  I feel silly, but I can't seem to
find it though postgresql.org.

> > Rick Delaney wrote:
> >
> > > I'm using 7.0 and have noticed that I need to grant SELECT and UPDATE
> > > permissions on any referentially-related tables.  Can/should I get
> > > around this?  A somewhat contrived example:


-- 
Rick Delaney

Re: [SQL] Permissions for foreign keys

[Rick Delaney]

Rick Delaney wrote:
> 
> "Ross J. Reedstrom" wrote:
> >
> > This is a bug that was fixed in 7.1beta for sure, but also 7.0.3,
> > I believe. So it's as simple as upgrading.
> 
> Thanks, I'll upgrade.  I couldn't find this listed in any changes files
> so can you (or anyone) confirm that this is fixed in 7.0.3?

For the record, this is NOT fixed in 7.0.3.  I will try the beta which I
finally managed to find.  It is at

ftp://ftp.postgresql.org/pub/dev/

for those like me who constantly get timed out using the search engine
at postgresql.org.

-- 
Rick Delaney