Обсуждение: Proper use of Groups and Users (Roles).

On Mon, 15 Feb 2016 12:06:28 -0500
Melvin Davidson <melvin6925@gmail.com> wrote:

> I wrote a short article to explain the proper use of Group and Userss in the database.

Hi Melvin,

Thanks for the explanation, it makes things easy to understand.

One question :

> Although GRANT ALL, at first appears to simplify granting permissions, it is actually a very bad practice that is
oftenmisused. That is because doing so would also allow groups and ordinary users the following additional privileges:
TRUNCATE,REFERENCES & TRIGGER.  

If a user has DELETE rights on a table, I don't see how granting him TRUNCATE makes that much of a difference? Same
couldbe said of the other two, it's not like they are going to cause more damage than the previous rights. 




--
                    Bien à vous, Vincent Veyron

https://marica.fr/
Gestion des contentieux, des dossiers de sinistres assurance et des contrats pour le service juridique

On Tue, 16 Feb 2016 09:14:30 -0500
Melvin Davidson <melvin6925@gmail.com> wrote:

> The problem is TRUNCATE is more of an administrative privilege. Also, it is
> not captured in a DELETE trigger, so you have a security issue with that.

Ha, well, learn something new every day


> Also, REFERENCES & TRIGGER are schema changes which should never be done by
> a normal user.

Sure. I was thinking of the data changes.

Thanks.


--
                    Bien à vous, Vincent Veyron

https://marica.fr/
Gestion des contentieux, des dossiers de sinistres assurance et des contrats pour le service juridique

Hello

I get this error message :
ERROR:  cannot convert relation containing dropped columns to view

I have googled, but find only very old posts that doesn't seem to be the
same situation.

What I have done is that I have a quite big table that I added a column
to for deletion time.

Then I droped that column and added it again with the right type.

After that I cannot create a rule that is returning data.

The reason I have to return data is irrelevant here, but PostgREST
expects that.

To reproduce:
create table foo
(
id serial,
deleted int
);

alter table foo drop column deleted;
alter table foo add column deleted timestamp;

CREATE or replace RULE del_post AS ON DELETE TO foo
DO INSTEAD
update foo set deleted = now()
WHERE id = OLD.id
returning *;

returns:
ERROR:  cannot convert relation containing dropped columns to view


If I don't drop any column (adding the right type at once) it works as
expected.

two questions:
1) is this a bug
2) is there a way to "cean" the table from the deleted columns without
recreating it?

Best Regards

Nicklas Avén

Nicklas =?ISO-8859-1?Q?Av=E9n?= <nicklas.aven@jordogskog.no> writes:
> create table foo
> (
> id serial,
> deleted int
> );

> alter table foo drop column deleted;
> alter table foo add column deleted timestamp;

> CREATE or replace RULE del_post AS ON DELETE TO foo
> DO INSTEAD
> update foo set deleted = now()
> WHERE id = OLD.id
> returning *;

> returns:
> ERROR:  cannot convert relation containing dropped columns to view

Hmm.

> 1) is this a bug

Well, it's an unimplemented feature anyway.  The reason the error message
is like that seems to be that it was correct (that is, that was the only
possible case) when it was introduced, which was in the 2002 patch that
implemented DROP COLUMN to begin with:

+            /*
+             * Disallow dropped columns in the relation.  This won't happen
+             * in the cases we actually care about (namely creating a view
+             * via CREATE TABLE then CREATE RULE).  Trying to cope with it
+             * is much more trouble than it's worth, because we'd have to
+             * modify the rule to insert dummy NULLs at the right positions.
+             */
+            if (attr->attisdropped)
+                elog(ERROR, "cannot convert relation containing dropped columns to view");

When we made rules with RETURNING go through this logic, in 2006, we
don't seem to have revisited the message text, much less thought about
whether we needed to take "more trouble" about dealing with dropped
columns in a real table.

I'm not sure how hard it would be to support the case.  Given that yours
is the first complaint in ten years, and that rules in general are pretty
out of favor, it's probably not going to be very high on the to-do list.
My own inclination would just be to provide a more on-point error message
for this case.

> 2) is there a way to "cean" the table from the deleted columns without
> recreating it?

Nope, sorry.

What I'd suggest is that you consider implementing this behavior without
using rules.  Instead, what you want is something like

create view visible_foo as
  select <desired columns> from foo where deleted is null;

plus INSTEAD OF triggers that redirect inserts/updates/deletes from
visible_foo to foo.  This way is likely to perform better than a rule
and have less-surprising semantics in corner cases.

            regards, tom lane

---- Tom Lane skrev ----

> Nicklas Avén <nicklas.aven@jordogskog.no> writes:
> > create table foo
> > (
> > id serial,
> > deleted int
> > );
>
> > alter table foo drop column deleted;
> > alter table foo add column deleted timestamp;
>
> > CREATE or replace RULE del_post AS ON DELETE TO foo
> > DO INSTEAD
> > update foo set deleted = now()
> > WHERE id = OLD.id
> > returning *;
>
> > returns:
> > ERROR:  cannot convert relation containing dropped columns to view
>
> Hmm.
>
> > 1) is this a bug
>
> Well, it's an unimplemented feature anyway.  The reason the error message
> is like that seems to be that it was correct (that is, that was the only
> possible case) when it was introduced, which was in the 2002 patch that
> implemented DROP COLUMN to begin with:
> 
> +            /*
> +             * Disallow dropped columns in the relation.  This won't happen
> +             * in the cases we actually care about (namely creating a view
> +             * via CREATE TABLE then CREATE RULE).  Trying to cope with it
> +             * is much more trouble than it's worth, because we'd have to
> +             * modify the rule to insert dummy NULLs at the right positions.
> +             */
> +            if (attr->attisdropped)
> +                elog(ERROR, "cannot convert relation containing dropped columns to view");
>
> When we made rules with RETURNING go through this logic, in 2006, we
> don't seem to have revisited the message text, much less thought about
> whether we needed to take "more trouble" about dealing with dropped
> columns in a real table.
>
> I'm not sure how hard it would be to support the case.  Given that yours
> is the first complaint in ten years, and that rules in general are pretty
> out of favor, it's probably not going to be very high on the to-do list.
> My own inclination would just be to provide a more on-point error message
> for this case.
>
> > 2) is there a way to "cean" the table from the deleted columns without
> > recreating it?
>
> Nope, sorry.
>
> What I'd suggest is that you consider implementing this behavior without
> using rules.  Instead, what you want is something like
>
> create view visible_foo as
>   select <desired columns> from foo where deleted is null;
>
> plus INSTEAD OF triggers that redirect inserts/updates/deletes from
> visible_foo to foo.  This way is likely to perform better than a rule
> and have less-surprising semantics in corner cases.
>
> regards, tom lane

Ok, thank you.
I think you are right about putting this logic on the view instead. I had my reasons for going the rule path, but as you say there is reasons for not do that too.

Thanks a lot for very fast reponse!

Best Regards
Nicklas Avén

I wrote:
> Nicklas =?ISO-8859-1?Q?Av=E9n?= <nicklas.aven@jordogskog.no> writes:
>> ERROR:  cannot convert relation containing dropped columns to view

> When we made rules with RETURNING go through this logic, in 2006, we
> don't seem to have revisited the message text, much less thought about
> whether we needed to take "more trouble" about dealing with dropped
> columns in a real table.

> I'm not sure how hard it would be to support the case.  Given that yours
> is the first complaint in ten years, and that rules in general are pretty
> out of favor, it's probably not going to be very high on the to-do list.
> My own inclination would just be to provide a more on-point error message
> for this case.

I spent half an hour or so trying to make this work, along the lines of
what's suggested in the code comment (inserting dummy NULL entries into
the tlist).  While it's not terribly hard to make checkRuleResultList
itself play along, it turns out that much of the rest of the backend is
not prepared to deal with such entries.  For example, although the
RETURNING list seems to work okay as such:

regression=# delete from foo where id = 2 returning *;
 id |          deleted
----+----------------------------
  2 | 2016-02-29 18:30:04.116309
(1 row)

you soon find that operations like rule decompiling think that the NULL
entry means something:

regression=# \d+ foo
                                                       Table "public.foo"
 Column  |            Type             |                    Modifiers                     | Storage | Stats target |
Description 

---------+-----------------------------+--------------------------------------------------+---------+--------------+-------------
 id      | integer                     | not null default nextval('foo_id_seq'::regclass) | plain   |              |
 deleted | timestamp without time zone |                                                  | plain   |              |
Rules:
    del_post AS
    ON DELETE TO foo DO INSTEAD  UPDATE foo SET deleted = now()
  WHERE foo.id = old.id
  RETURNING foo.id,
    NULL::integer AS "........pg.dropped.2........",
    foo.deleted

Trying to find everyplace that would have to be taught about that seems
like a mess.  It would definitely take a significant amount of work,
and as I said earlier, I doubt anyone wants to invest the work.

So I'm just going to go improve the comment and error message and
leave it at that.

            regards, tom lane