Thread: Permissions for foreign keys
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, 100000); 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 UPDATEproj. 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
Hey Rick, I am sure there are more elegant solutions but I have a simple one. Write a trigger that will grant the permissions before insert or update and and revoke all privileges after the insert or update. -Najm 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: > > 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, 100000); > 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
The problem is fixed in the 7.1 beta series. 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: > > 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, 100000); > 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
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. Ross On Thu, Feb 01, 2001 at 10:09:29AM -0500, Najm Hashmi wrote: > Hey Rick, > I am sure there are more elegant solutions but I have a simple > one. Write a trigger that will grant the permissions before insert or > update and and revoke all privileges after the insert or update. > -Najm > > > 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: > > -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
"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
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
Rick - Thanks for checking that. I should've done it myself. Sorry I didn't respond when you asked for confirmation. Ross On Thu, Feb 01, 2001 at 05:38:12PM -0500, Rick Delaney wrote: > 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