pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedFOR SHARE permissions
4+ messages / 3 participants
[nested] [flat]
* FOR SHARE permissions
@ 2010-12-12 03:47 David Underhill <dound07@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: David Underhill @ 2010-12-12 03:47 UTC (permalink / raw)
To: pgsql-admin
I have two tables. One has a foreign key referencing a serial field in the
other table. I've given INSERT privilege to a role other than the owner,
but I still can't insert into the table containing the foreign key unless I
grant the *owner* of the table UPDATE privilege on the table containing the
referenced field.
I don't quite understand why the *owner* needs to have UPDATE permission in
order for another distinct role (with INSERT privilege) to be able to insert
a row in this case.
This is a little confusing, so I've provided a boiled down example of my
issue.
createuser -U postgres testowner -DIRS --pwprompt
createdb -U postgres -O testowner testdb
createuser -U postgres testupdater -DIRS --pwprompt
psql -d testdb -U testowner
CREATE TABLE a ( id serial PRIMARY KEY );
CREATE TABLE b ( a_id integer REFERENCES a(id) );
GRANT SELECT,INSERT ON ALL TABLES IN SCHEMA public TO testupdater;
GRANT USAGE,UPDATE ON SEQUENCE a_id_seq TO testupdater;
REVOKE INSERT, UPDATE ON ALL TABLES IN SCHEMA public FROM testowner;
INSERT INTO a VALUES (DEFAULT); -- as expected: ERROR: permission denied
for relation a
\q
psql -d testdb -U testupdater
INSERT INTO a VALUES (DEFAULT);
SELECT id FROM a LIMIT 1; -- selects the first id (1)
INSERT INTO b VALUES (1); -- unexpected error:
\q
ERROR: permission denied for relation a
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."a" x WHERE "id"
OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"
However, the above insert works if I give testowner back the UPDATE
privilege (GRANT UPDATE ON a TO testowner;). Why does testowner need UPDATE
in this case?
Thanks,
~ David
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: FOR SHARE permissions
@ 2010-12-12 16:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Underhill <dound07@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2010-12-12 16:11 UTC (permalink / raw)
To: David Underhill <dound07@gmail.com>; +Cc: pgsql-admin
David Underhill <dound07@gmail.com> writes:
> I have two tables. One has a foreign key referencing a serial field in the
> other table. I've given INSERT privilege to a role other than the owner,
> but I still can't insert into the table containing the foreign key unless I
> grant the *owner* of the table UPDATE privilege on the table containing the
> referenced field.
> I don't quite understand why the *owner* needs to have UPDATE permission in
> order for another distinct role (with INSERT privilege) to be able to insert
> a row in this case.
The owner is the one who established the foreign key reference. That
reference is no concern of the third-party user who's been given insert
permission on the referencing table. The design you seem to have in
mind would make management of permissions a lot more complicated, since
instead of just granting insert permission to the third party, you'd
have to think about all the subsidiary permissions that might be needed
to deal with the table's FK linkages.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: FOR SHARE permissions
@ 2010-12-12 16:46 David Underhill <dound07@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: David Underhill @ 2010-12-12 16:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
On Sun, Dec 12, 2010 at 08:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> David Underhill <dound07@gmail.com> writes:
> > I have two tables. One has a foreign key referencing a serial field in
> the
> > other table. I've given INSERT privilege to a role other than the owner,
> > but I still can't insert into the table containing the foreign key unless
> I
> > grant the *owner* of the table UPDATE privilege on the table containing
> the
> > referenced field.
> > I don't quite understand why the *owner* needs to have UPDATE permission
> in
> > order for another distinct role (with INSERT privilege) to be able to
> insert
> > a row in this case.
>
> The owner is the one who established the foreign key reference. That
> reference is no concern of the third-party user who's been given insert
> permission on the referencing table. The design you seem to have in
> mind would make management of permissions a lot more complicated, since
> instead of just granting insert permission to the third party, you'd
> have to think about all the subsidiary permissions that might be needed
> to deal with the table's FK linkages.
>
Thanks Tom. It still seems strange that the role given insert is only able
to insert (in the test case I mentioned) iff the *owner* role has update
privileges.
Anyway, I wanted a role which could just copy info from an external source
into the db. I could have done this with the owner role, but I was trying
to do it with the least permissions possible (just in case I dorked up this
might help limit the maximum damage the role could do). Maybe this is a bit
overly complicated, as you suggest, and not worth much in the end (i.e., I
should just be cautious and use the role as intended ... probably not too
hard to verify since it is a script doing the inserting).
This was also nice though as it allowed the owner to revoke its own
privileges so that the db was read-only for it. Perhaps an easier solution
is to still revoke the owner's privileges by default, but temporarily
re-grant them when it needs to do inserts.
Thanks again for your thoughts.
~ David
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: FOR SHARE permissions
@ 2010-12-13 17:54 Craig James <craig_james@emolecules.com>
parent: David Underhill <dound07@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Craig James @ 2010-12-13 17:54 UTC (permalink / raw)
To: pgsql-admin
On 12/11/10 7:47 PM, David Underhill wrote:
> I have two tables. One has a foreign key referencing a serial
> field in the other table. I've given INSERT privilege to a role
> other than the owner, but I still can't insert into the table
> containing the foreign key unless I grant the /owner/ of the table
> UPDATE privilege on the table containing the referenced field.
>
> I don't quite understand why the /owner/ needs to have UPDATE
> permission in order for another distinct role (with INSERT
> privilege) to be able to insert a row in this case.
I don't know about the specifics of the Postgres implementation, but this makes sense from a security point of view.
When you insert into second table, you're effectively "locking" the referenced row in the referenced (first) table, making it so that the owner of that table can no long delete that row. You ARE updating that table. You're not inserting or deleting data from it, but you are changing what the owner can do to it. In other words, you're updating the owner's ability to delete from and update the referenced table.
Craig
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-12-13 17:54 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-12 03:47 FOR SHARE permissions David Underhill <dound07@gmail.com>
2010-12-12 16:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-12-12 16:46 ` David Underhill <dound07@gmail.com>
2010-12-13 17:54 ` Craig James <craig_james@emolecules.com>
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