Re: Permission on insert rules

Hello everybody,

Just a question.
I'm writing some rules to insert/update some data in my database, and I
gave all the privileges on that view to the user, and only select on the
tables.
When that user inserts data using the view, I thought that was user
postgres that will do the rest ! But I got permission denied on those
tables.
The idea was to create a layer, with the views, giving to that user
permission on views to insert and update, and not to tables.
Is this possible ?

Thanks in advance.
Luis Sousa

Luis,

> Just a question.
> I'm writing some rules to insert/update some data in my database, and I
> gave all the privileges on that view to the user, and only select on the
> tables.
> When that user inserts data using the view, I thought that was user
> postgres that will do the rest ! But I got permission denied on those
> tables.
> The idea was to create a layer, with the views, giving to that user
> permission on views to insert and update, and not to tables.
> Is this possible ?

This is a known problem.

I know that permissions for Functions has been addressed in 7.3. However, I
am not sure about permissions for updatable views. Tom, Bruce?

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:
>
> Luis,
>
> > Just a question.
> > I'm writing some rules to insert/update some data in my database, and I
> > gave all the privileges on that view to the user, and only select on the
> > tables.
> > When that user inserts data using the view, I thought that was user
> > postgres that will do the rest ! But I got permission denied on those
> > tables.
> > The idea was to create a layer, with the views, giving to that user
> > permission on views to insert and update, and not to tables.
> > Is this possible ?
>
> This is a known problem.
>
> I know that permissions for Functions has been addressed in 7.3. However, I
> am not sure about permissions for updatable views. Tom, Bruce?

Views have always had their own permissions.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Fri, 2002-11-08 at 21:40, Bruce Momjian wrote:
> Josh Berkus wrote:
> >
> > Luis,
> >
> > > Just a question.
> > > I'm writing some rules to insert/update some data in my database, and I
> > > gave all the privileges on that view to the user, and only select on the
> > > tables.
> > > When that user inserts data using the view, I thought that was user
> > > postgres that will do the rest ! But I got permission denied on those
> > > tables.
> > > The idea was to create a layer, with the views, giving to that user
> > > permission on views to insert and update, and not to tables.
> > > Is this possible ?
> >
> > This is a known problem.
> >
> > I know that permissions for Functions has been addressed in 7.3. However, I
> > am not sure about permissions for updatable views. Tom, Bruce?
>
> Views have always had their own permissions.
>

If the functions can fire as there creator instead of there caller, then
I would think as long as the creator has insert/update views on the base
table, you should be able to do updateable rules and give only
permissions to the view for the caller. (Though maybe you have to use
triggers rather than rules to do this?) Does that sound right?

Robert Treat

Robert,

> If the functions can fire as there creator instead of there caller,
> then
> I would think as long as the creator has insert/update views on the
> base
> table, you should be able to do updateable rules and give only
> permissions to the view for the caller. (Though maybe you have to use
> triggers rather than rules to do this?) Does that sound right?

I don't know. Can you test it?

-Josh

Luis,

> Just a question.
> I'm writing some rules to insert/update some data in my database, and I
> gave all the privileges on that view to the user, and only select on the
> tables.
> When that user inserts data using the view, I thought that was user
> postgres that will do the rest ! But I got permission denied on those
> tables.
> The idea was to create a layer, with the views, giving to that user
> permission on views to insert and update, and not to tables.
> Is this possible ?

I just checked this. It works fine in 7.2.3.

I think that you are missing a step. If you want to have an updatable view,
then you need to define a Rule for updating it, such as:

kitchen=# create rule update_password as on update to user_password
kitchen-# do instead update "user" set "password" = NEW."password"
kitchen-# where user_id = OLD.user_id;

See the online docs, under Server Programming, for how to use the RULES
system.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Bruce Momjian wrote:

>Josh Berkus wrote:
>
>
>>Luis,
>>
>>
>>
>>>Just a question.
>>>I'm writing some rules to insert/update some data in my database, and I
>>>gave all the privileges on that view to the user, and only select on the
>>>tables.
>>>When that user inserts data using the view, I thought that was user
>>>postgres that will do the rest ! But I got permission denied on those
>>>tables.
>>>The idea was to create a layer, with the views, giving to that user
>>>permission on views to insert and update, and not to tables.
>>>Is this possible ?
>>>
>>>
>>This is a known problem.
>>
>>I know that permissions for Functions has been addressed in 7.3. However, I
>>am not sure about permissions for updatable views. Tom, Bruce?
>>
>>
>
>Views have always had their own permissions.
>
>
>
Offcourse, but when I'm giving permissions to insert and update on
views, I have to give those permissions also to the tables !! (those
operations that are executed on rules)

Luis Sousa

Robert Treat wrote:

>On Fri, 2002-11-08 at 21:40, Bruce Momjian wrote:
>
>
>>Josh Berkus wrote:
>>
>>
>>>Luis,
>>>
>>>
>>>
>>>>Just a question.
>>>>I'm writing some rules to insert/update some data in my database, and I
>>>>gave all the privileges on that view to the user, and only select on the
>>>>tables.
>>>>When that user inserts data using the view, I thought that was user
>>>>postgres that will do the rest ! But I got permission denied on those
>>>>tables.
>>>>The idea was to create a layer, with the views, giving to that user
>>>>permission on views to insert and update, and not to tables.
>>>>Is this possible ?
>>>>
>>>>
>>>This is a known problem.
>>>
>>>I know that permissions for Functions has been addressed in 7.3. However, I
>>>am not sure about permissions for updatable views. Tom, Bruce?
>>>
>>>
>>Views have always had their own permissions.
>>
>>
>>
>
>If the functions can fire as there creator instead of there caller, then
>I would think as long as the creator has insert/update views on the base
>table, you should be able to do updateable rules and give only
>permissions to the view for the caller. (Though maybe you have to use
>triggers rather than rules to do this?) Does that sound right?
>
>Robert Treat
>
>
>
>
>
>
Is that the only way to do it ?

Luis Sousa

Josh Berkus wrote:

>Luis,
>
>
>
>>Just a question.
>>I'm writing some rules to insert/update some data in my database, and I
>>gave all the privileges on that view to the user, and only select on the
>>tables.
>>When that user inserts data using the view, I thought that was user
>>postgres that will do the rest ! But I got permission denied on those
>>tables.
>>The idea was to create a layer, with the views, giving to that user
>>permission on views to insert and update, and not to tables.
>>Is this possible ?
>>
>>
>
>I just checked this. It works fine in 7.2.3.
>
>I think that you are missing a step. If you want to have an updatable view,
>then you need to define a Rule for updating it, such as:
>
>kitchen=# create rule update_password as on update to user_password
>kitchen-# do instead update "user" set "password" = NEW."password"
>kitchen-# where user_id = OLD.user_id;
>
>See the online docs, under Server Programming, for how to use the RULES
>system.
>
>
>
That's what I already made. The problem is when I do the update, I
permission denied in all the tables for update and insert. The user
that's making this operation only have select privilege.
Any way, I'm using version 7.2.1-2 for debian.

Luis Sousa

Luis,

> That's what I already made. The problem is when I do the update, I
> permission denied in all the tables for update and insert. The user
> that's making this operation only have select privilege.
> Any way, I'm using version 7.2.1-2 for debian.

I can't reproduce the problem, and permissions did not get fixed
between 7.2.1 and 7.2.3. So I'm pretty sure that you're missing
something, somewhere.

Please post:

1) The table definitions for the tables being updated.
2) The view definition and permissions
3) The Rules statements defined on the view
4) A copy of your database session where your update is denied,
including the exact error message received.

Without that information, no futher help is available.

-Josh Berkus

This should be a test case for what Luis wants, although it works in
7.2.1 so maybe not. Luis, if this isn't what your trying to do, you'll
need to post some code:

create table parent (id int, name text, misc text);

create view child as select id,name from parent;

create rule jammasterjay as on insert to child do instead insert into
parent values (new.id,new.name);

insert into parent values (1,'one','wahad');
insert into parent values (2,'two','ithnain');
insert into parent values (3,'three','thalata');

select * from parent;
select * from child;

insert into child (4,'four');

select * from parent;

create user mellymel;
grant select on child to mellymel;
grant insert on child to mellymel;

** reconnect as mellymel **

select * from parent; (generates error)
select * from child;

insert into child values (5,'five');

select * from child; (has all 5 rows)

Robert Treat

On Tue, 2002-11-12 at 12:29, Josh Berkus wrote:
> Luis,
>
> > That's what I already made. The problem is when I do the update, I
> > permission denied in all the tables for update and insert. The user
> > that's making this operation only have select privilege.
> > Any way, I'm using version 7.2.1-2 for debian.
>
> I can't reproduce the problem, and permissions did not get fixed
> between 7.2.1 and 7.2.3. So I'm pretty sure that you're missing
> something, somewhere.
>
> Please post:
>
> 1) The table definitions for the tables being updated.
> 2) The view definition and permissions
> 3) The Rules statements defined on the view
> 4) A copy of your database session where your update is denied,
> including the exact error message received.
>
> Without that information, no futher help is available.
>
> -Josh Berkus
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

Hi again,

I already know what's the problem. Actually, everything works fine in
the example posted by Robert. Part of my rule is as simple as that
example, but I'm also calling functions inside the rule.
I have a table, whose primary key is a serial, that is connected to a
few tables. In this view, I want to insert data, in the main table, and
also in the "child" tables. My idea was to create a rule, that first
inserts in the parent table, and some functions, that will select the
parent table returning the id created, and will insert some data on
child table (I'm open for sugestions to do this !!!). I don't know
exactly how this works if more than one user at the same time !!!!
When inserting, using the rule, the insert that's defined on the rule
works fine, but the insert defined inside the function, doesn't (that's
the one that gives permssion denied).
Suppose these definitions:

-- Tables definition
CREATE TABLE "pessoal" (
"idPessoal" serial,
"titulo" text default '',
"nome" text NOT NULL,
PRIMARY KEY ("idPessoal")
);

CREATE TABLE "pessoalGabinete" (
"idPessoal" int4,
edificio text,
sala text,
PRIMARY KEY ("idPessoal",edificio,sala),
FOREIGN KEY("idPessoal") REFERENCES pessoal
ON UPDATE CASCADE
);

-- View definition
CREATE VIEW "pessoalInfo_v" AS
SELECT p.titulo, p.nome, pg.edificio, pg.sala
FROM pessoal p LEFT OUTER JOIN "pessoalGabinete" pg USING ("idPessoal");

-- Function definition
CREATE FUNCTION "pessoalInfoGab_f_insert"(text,text)
RETURNS boolean AS '
DECLARE
f_edificio ALIAS FOR $1;
f_sala ALIAS FOR $2;
pessoal RECORD;

BEGIN
SELECT MAX("idPessoal") AS max INTO pessoal
FROM pessoal;

INSERT INTO "pessoalGabinete" ("idPessoal",edificio,sala)
VALUES (pessoal.max,f_edificio,f_sala);

RETURN 1;
END; '
LANGUAGE 'plpgsql';

-- Rule definition
CREATE RULE "pessoalInfo_r_insert" AS ON INSERT TO "pessoalInfo_v"
DO INSTEAD (
INSERT INTO pessoal (titulo,nome)
VALUES (NEW.titulo,NEW.nome);
SELECT "pessoalInfoGab_f_insert"(NEW.edificio,NEW.sala) AS ok;
);

GRANT SELECT,INSERT,UPDATE on "pessoalInfo_v" to nobody;
GRANT INSERT,UPDATE on "pessoal_idPessoal_seq" to nobody;

INSERT INTO "pessoalInfo_v" (titulo,nome,edificio,sala) VALUES
('Dr.','Robert','A',5);

And I got this message:
NOTICE: Error occurred while executing PL/pgSQL function
pessoalInfoGab_f_insert
NOTICE: line 10 at SQL statement
ERROR: pessoalGabinete: Permission denied.

But, suppose that I use this rule instead and that already exists in
table pessoal "idPessoal"=1:
-- Rule definition
CREATE RULE "pessoalInfo_r_insert" AS ON INSERT TO "pessoalInfo_v"
DO INSTEAD (
INSERT INTO pessoal (titulo,nome)
VALUES (NEW.titulo,NEW.nome);
INSERT INTO "pessoalGabinete" ("idPessoal",edificio,sala)
VALUES (1,NEW.edificio,NEW.sala);
);

In this case everything works fine, but this doesn't solve my problem,
because I need to know whats the number created by the sequence in pessoal.
Any ideas ??

Thanks in advance.

Luis Sousa

Robert Treat wrote:

>This should be a test case for what Luis wants, although it works in
>7.2.1 so maybe not. Luis, if this isn't what your trying to do, you'll
>need to post some code:
>
>create table parent (id int, name text, misc text);
>
>create view child as select id,name from parent;
>
>create rule jammasterjay as on insert to child do instead insert into
>parent values (new.id,new.name);
>
>insert into parent values (1,'one','wahad');
>insert into parent values (2,'two','ithnain');
>insert into parent values (3,'three','thalata');
>
>select * from parent;
>select * from child;
>
>insert into child (4,'four');
>
>select * from parent;
>
>create user mellymel;
>grant select on child to mellymel;
>grant insert on child to mellymel;
>
>** reconnect as mellymel **
>
>select * from parent; (generates error)
>select * from child;
>
>insert into child values (5,'five');
>
>select * from child; (has all 5 rows)
>
>
>Robert Treat
>
>On Tue, 2002-11-12 at 12:29, Josh Berkus wrote:
>
>
>>Luis,
>>
>>
>>
>>>That's what I already made. The problem is when I do the update, I
>>>permission denied in all the tables for update and insert. The user
>>>that's making this operation only have select privilege.
>>>Any way, I'm using version 7.2.1-2 for debian.
>>>
>>>
>>I can't reproduce the problem, and permissions did not get fixed
>>between 7.2.1 and 7.2.3. So I'm pretty sure that you're missing
>>something, somewhere.
>>
>>Please post:
>>
>>1) The table definitions for the tables being updated.
>>2) The view definition and permissions
>>3) The Rules statements defined on the view
>>4) A copy of your database session where your update is denied,
>>including the exact error message received.
>>
>>Without that information, no futher help is available.
>>
>>-Josh Berkus
>>
>>
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>>
>>
>
>
>
>
>
>
>

Hi everyone,

I have a problem with adding a column to an existing table. I want to add
a column named modified which is of datatype TIMESTAMP and has a DEFAULT
CURRENT_TIMESTAMP as it's initial value. I cannot do this with ALTER
TABLE, it tells me to user ALTER TABLE SET DEFAULT instead but I cannot
figure out the syntax with the help of the manuals.

My ALTER TABLE looked like this:

alter table decks add column modified timestamp default current_timestamp;

and the error I get is this:

ERROR: Adding columns with defaults is not implemented.
Add the column, then use ALTER TABLE SET DEFAULT.

Thanks in advance,

Archie

On Wed, 13 Nov 2002, Archibald Zimonyi wrote:

>
> Hi everyone,
>
> I have a problem with adding a column to an existing table. I want to add
> a column named modified which is of datatype TIMESTAMP and has a DEFAULT
> CURRENT_TIMESTAMP as it's initial value. I cannot do this with ALTER
> TABLE, it tells me to user ALTER TABLE SET DEFAULT instead but I cannot
> figure out the syntax with the help of the manuals.
>
> My ALTER TABLE looked like this:
>
> alter table decks add column modified timestamp default current_timestamp;

ALTER TABLE decks alter column modified SET DEFAULT current_timestamp;

>
> and the error I get is this:
>
> ERROR: Adding columns with defaults is not implemented.
> Add the column, then use ALTER TABLE SET DEFAULT.
>
> Thanks in advance,
>
> Archie
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr

Luis Sousa <llsousa(at)ualg(dot)pt> writes:
> When inserting, using the rule, the insert that's defined on the rule
> works fine, but the insert defined inside the function, doesn't (that's
> the one that gives permssion denied).

Right. As of 7.3 you can fix this by making the function "setuid" (ie,
it runs with the permissions of the function owner, not the caller).

A rule's permission effects only extend as far as access rights to the
tables explicitly named in the rule. Evaluation of functions appearing
in the text of the rule is done normally --- ie, as the calling user
(unless you use the new setuid-function feature). There's been past
discussion about whether that's a good idea, but it would be quite
difficult to change it.

regards, tom lane

Tom Lane wrote:

>Luis Sousa <llsousa(at)ualg(dot)pt> writes:
>
>
>>When inserting, using the rule, the insert that's defined on the rule
>>works fine, but the insert defined inside the function, doesn't (that's
>>the one that gives permssion denied).
>>
>>
>
>Right. As of 7.3 you can fix this by making the function "setuid" (ie,
>it runs with the permissions of the function owner, not the caller).
>
>
There's any way to insert data inside the tables, using the functions,
called by the rules, without giving direct access to the user ?
I don't know, using a trigger or any kind of structure !!??

Regards,
Luis Sousa

Luis,

> There's any way to insert data inside the tables, using the
> functions, called by the rules, without giving direct access to the
> user ?
> I don't know, using a trigger or any kind of structure !!??

Not until 7.3. Which is due out soon ... a couple of weeks, likely.

-Josh Berkus

On Wed, Nov 13, 2002 at 10:44:19 +0000,
Luis Sousa <llsousa(at)ualg(dot)pt> wrote:
>
> I have a table, whose primary key is a serial, that is connected to a
> few tables. In this view, I want to insert data, in the main table, and
> also in the "child" tables. My idea was to create a rule, that first
> inserts in the parent table, and some functions, that will select the
> parent table returning the id created, and will insert some data on
> child table (I'm open for sugestions to do this !!!). I don't know
> exactly how this works if more than one user at the same time !!!!

You can use currval to retrieve the last value assigned to a specified
sequence in the current session. This is transaction safe.