pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Permissions for foreign keys
7+ messages / 4 participants
[nested] [flat]

* Permissions for foreign keys
@ 2001-01-31 00:50  Rick Delaney <rick@consumercontact.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Rick Delaney @ 2001-01-31 00:50 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Permissions for foreign keys
@ 2001-01-31 15:42  Mark Volpe <volpe.mark@epamail.epa.gov>
  parent: Rick Delaney <rick@consumercontact.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Mark Volpe @ 2001-01-31 15:42 UTC (permalink / raw)
  To: Rick Delaney <rick@consumercontact.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Permissions for foreign keys
@ 2001-01-31 15:50  Ross J. Reedstrom <reedstrm@rice.edu>
  parent: Najm Hashmi <najm@mondo-live.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Ross J. Reedstrom @ 2001-01-31 15:50 UTC (permalink / raw)
  To: Najm Hashmi <najm@mondo-live.com>; +Cc: Rick Delaney <rick@consumercontact.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Permissions for foreign keys
@ 2001-01-31 18:44  Rick Delaney <rick@consumercontact.com>
  parent: Ross J. Reedstrom <reedstrm@rice.edu>
  0 siblings, 1 reply; 7+ messages in thread

From: Rick Delaney @ 2001-01-31 18:44 UTC (permalink / raw)
  To: Ross J. Reedstrom <reedstrm@rice.edu>; +Cc: pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Permissions for foreign keys
@ 2001-02-01 15:09  Najm Hashmi <najm@mondo-live.com>
  parent: Rick Delaney <rick@consumercontact.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Najm Hashmi @ 2001-02-01 15:09 UTC (permalink / raw)
  To: Rick Delaney <rick@consumercontact.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Permissions for foreign keys
@ 2001-02-01 22:38  Rick Delaney <rick@consumercontact.com>
  parent: Rick Delaney <rick@consumercontact.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Rick Delaney @ 2001-02-01 22:38 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Permissions for foreign keys
@ 2001-02-01 23:59  Ross J. Reedstrom <reedstrm@rice.edu>
  parent: Rick Delaney <rick@consumercontact.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Ross J. Reedstrom @ 2001-02-01 23:59 UTC (permalink / raw)
  To: Rick Delaney <rick@consumercontact.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2001-02-01 23:59 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-31 00:50 Permissions for foreign keys Rick Delaney <rick@consumercontact.com>
2001-01-31 15:42 ` Mark Volpe <volpe.mark@epamail.epa.gov>
2001-02-01 15:09 ` Najm Hashmi <najm@mondo-live.com>
2001-01-31 15:50   ` Ross J. Reedstrom <reedstrm@rice.edu>
2001-01-31 18:44     ` Rick Delaney <rick@consumercontact.com>
2001-02-01 22:38       ` Rick Delaney <rick@consumercontact.com>
2001-02-01 23:59         ` Ross J. Reedstrom <reedstrm@rice.edu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox