Subject: BUG #1610: rewrite rule and sequence

From: "Olleg Samoylov" <[email-masked]>
Date: Thu, 21 Apr 2005 13:19:44 +0100 (BST)

The following bug has been logged online:

Bug reference:      1610
Logged by:          Olleg Samoylov
Email address:      [email-masked]
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

---

From: Richard Huxton <[email-masked]>
Date: Thu, 21 Apr 2005 16:57:51 +0100

> 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

---

From: Olleg Samoylov <[email-masked]>
Date: Fri, 22 Apr 2005 10:33:10 +0400

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

---

From: Richard Huxton <[email-masked]>
Date: Fri, 22 Apr 2005 09:03:17 +0100

> 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

---

From: Olleg Samoylov <[email-masked]>
Date: Mon, 25 Apr 2005 14:31:19 +0400

> 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