public inbox for [email protected]help / color / mirror / Atom feed
BUG #1610: rewrite rule and sequence 5+ messages / 2 participants [nested] [flat]
* BUG #1610: rewrite rule and sequence @ 2005-04-21 12:19 Olleg Samoylov <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Olleg Samoylov @ 2005-04-21 12:19 UTC (permalink / raw) To: [email protected] The following bug has been logged online: Bug reference: 1610 Logged by: Olleg Samoylov Email address: [email protected] PostgreSQL version: 7.4.7 Operating system: Linux debian-amd64 Description: rewrite rule and sequence Details: Rule on view can't insert in table with "serial" field under not owner. Need grant privilege on sequence explicitly. How reproduce: olleg=> create table f (pk serial, f integer); NOTICE: CREATE TABLE will create implicit sequence "f_pk_seq" for "serial" column "f.pk" CREATE TABLE olleg=> create view v as select * from t; ERROR: relation "t" does not exist olleg=> drop table f; DROP TABLE olleg=> create table t (pk serial, f integer); NOTICE: CREATE TABLE will create implicit sequence "t_pk_seq" for "serial" column "t.pk" CREATE TABLE olleg=> create view v as select * from t; CREATE VIEW olleg=> create rule r as on insert to v do instead insert into t(f) values (new.f); CREATE RULE olleg=> grant select,insert on t to bill; GRANT olleg=> \c - bill You are now connected as new user "bill". olleg=> insert into t (f) values (0); ERROR: permission denied for sequence t_pk_seq ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #1610: rewrite rule and sequence @ 2005-04-21 15:57 Richard Huxton <[email protected]> parent: Olleg Samoylov <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Richard Huxton @ 2005-04-21 15:57 UTC (permalink / raw) To: Olleg Samoylov <[email protected]>; +Cc: [email protected] Olleg Samoylov wrote: > The following bug has been logged online: > > Bug reference: 1610 > Logged by: Olleg Samoylov > Email address: [email protected] > PostgreSQL version: 7.4.7 > Operating system: Linux debian-amd64 > Description: rewrite rule and sequence > Details: > > Rule on view can't insert in table with "serial" field under not owner. Need > grant privilege on sequence explicitly. That's not a bug, it's a feature (as they say). Although you can automatically generate sequences with the serial pseudo -type, they are separate objects. Not only can you create them separately from a table, you can have many columns using nextval('my_sequence') as their default. I suppose you could argue that a sequence only used by one table could inherit that table's permissions by default, but I can see problems when people reorder GRANT statements. HTH -- Richard Huxton Archonet Ltd ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #1610: rewrite rule and sequence @ 2005-04-22 06:33 Olleg Samoylov <[email protected]> parent: Richard Huxton <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Olleg Samoylov @ 2005-04-22 06:33 UTC (permalink / raw) To: Richard Huxton <[email protected]>; +Cc: [email protected] Richard Huxton wrote: > That's not a bug, it's a feature (as they say). I suppose you could > argue that a sequence only used by one table could inherit that > table's permissions by default, but I can see problems when people > reorder GRANT statements. > > HTH It's not feature, it's bug. From postgresql documentation 33.4. Rules and Privileges: <quote> Relations that are used due to rules get checked against the privileges of the rule owner, not the user invoking the rule. This means that a user only needs the required privileges for the tables/views that he names explicitly in his queries.</quote> This dont' true for tables with serial fields. -- Olleg Samoylov Attachments: [application/x-pkcs7-signature] smime.p7s (4.0K, 2-smime.p7s) download ^ permalink raw reply [nested|flat] 5+ messages in thread
* Rules and Permissions docs change (was Re: BUG #1610: rewrite rule and sequence) @ 2005-04-22 08:03 Richard Huxton <[email protected]> parent: Olleg Samoylov <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Richard Huxton @ 2005-04-22 08:03 UTC (permalink / raw) To: Olleg Samoylov <[email protected]>; +Cc: [email protected]; pgsql-docs Olleg Samoylov wrote: > Richard Huxton wrote: > >> That's not a bug, it's a feature (as they say). I suppose you could >> argue that a sequence only used by one table could inherit that >> table's permissions by default, but I can see problems when people >> reorder GRANT statements. > It's not feature, it's bug. From postgresql documentation 33.4. Rules > and Privileges: > <quote> Relations that are used due to rules get checked against the > privileges of the rule owner, not the user invoking the rule. This means > that a user only needs the required privileges for the tables/views that > he names explicitly in his queries.</quote> > > This dont' true for tables with serial fields. Hmm - perhaps the documentation needs expanding. Certainly, if your view references functions you need to make sure permissions are set correctly on those. How about changes along the lines of: Ch 33.4, para 2 "... Relations that are used due to rules get checked against the privileges of the rule owner, not the user invoking the rule. This means that a user only needs the required privileges for the objects[1] that he names explicitly in his queries." then "[1] This includes permissions on tables and views you reference in your view definition. It might also include execute permissions on any functions referenced, and for updates, permissions on any sequences. This includes sequences automatically created by use of the SERIAL type." Perhaps we should also have a reminder to read the rules chapter in the serial description (ch 8.1.4) -- Richard Huxton Archonet Ltd ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #1610: rewrite rule and sequence @ 2005-04-25 10:31 Olleg Samoylov <[email protected]> parent: Richard Huxton <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Olleg Samoylov @ 2005-04-25 10:31 UTC (permalink / raw) To: Richard Huxton <[email protected]>; +Cc: [email protected] Richard Huxton wrote: > Hmm - perhaps the documentation needs expanding. Certainly, if your view > references functions you need to make sure permissions are set correctly > on those. > > How about changes along the lines of: > > Ch 33.4, para 2 > "... Relations that are used due to rules get checked against the > privileges of the rule owner, not the user invoking the rule. This means > that a user only needs the required privileges for the objects[1] that > he names explicitly in his queries." > > then > > "[1] This includes permissions on tables and views you reference in your > view definition. It might also include execute permissions on any > functions referenced, and for updates, permissions on any sequences. > This includes sequences automatically created by use of the SERIAL type." <quote> only needs the required privileges for the objects that he names explicitly in his queries.</quote> Sequence for serial type don't explicitly mentioned in queries. I expect the same behavior for rules as for function with "SECURITY DEFINER" parameter. -- Olleg Samoylov Attachments: [application/x-pkcs7-signature] smime.p7s (4.0K, 2-smime.p7s) download ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-04-25 10:31 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-04-21 12:19 BUG #1610: rewrite rule and sequence Olleg Samoylov <[email protected]> 2005-04-21 15:57 ` Richard Huxton <[email protected]> 2005-04-22 06:33 ` Olleg Samoylov <[email protected]> 2005-04-22 08:03 ` Rules and Permissions docs change (was Re: BUG #1610: rewrite rule and sequence) Richard Huxton <[email protected]> 2005-04-25 10:31 ` Olleg Samoylov <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox