Thread

1. Proper use of Groups and Users (Roles).

   Melvin Davidson <melvin6925@gmail.com> — 2016-02-15T17:06:28Z

   Some years ago, while working at Computer Associates as a tech support
   specialist for the Ingres database, I wrote a short article to explain the
   proper use of Group and Userss in the database. I thought it would be
   worthwhile to do the same for PostgreSQL, as I've seen a lot of cases where
   this was not implemented properly. Since I am not found of Wiki's, I've
   attached it here for sharing.
   
   
   -- 
   *Melvin Davidson*
   I reserve the right to fantasize.  Whether or not you
   wish to share my fantasy is entirely up to you.
   

2. Re: Proper use of Groups and Users (Roles).

   Vincent Veyron <vv.lists@wanadoo.fr> — 2016-02-16T10:39:34Z

   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 often misused. 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 could be 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
   
   
   

3. Re: Proper use of Groups and Users (Roles).

   Melvin Davidson <melvin6925@gmail.com> — 2016-02-16T14:14:30Z

   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.
   Also, REFERENCES & TRIGGER are schema changes which should never be done by
   a normal user.
   
   On Tue, Feb 16, 2016 at 5:39 AM, Vincent Veyron <vv.lists@wanadoo.fr> wrote:
   
   > 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 often misused. 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 could be 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
   >
   >
   > --
   > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
   > To make changes to your subscription:
   > http://www.postgresql.org/mailpref/pgsql-general
   >
   
   
   
   -- 
   *Melvin Davidson*
   I reserve the right to fantasize.  Whether or not you
   wish to share my fantasy is entirely up to you.
   

4. Re: Proper use of Groups and Users (Roles).

   Vincent Veyron <vv.lists@wanadoo.fr> — 2016-02-16T16:59:43Z

   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
   
   
   

5. ERROR: cannot convert relation containing dropped columns to view

   Nicklas Avén <nicklas.aven@jordogskog.no> — 2016-02-22T21:30:04Z

   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
   
   
   
   
   

6. Re: ERROR: cannot convert relation containing dropped columns to view

   Tom Lane <tgl@sss.pgh.pa.us> — 2016-02-22T22:21:53Z

   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
   
   
   

7. Re: ERROR: cannot convert relation containing dropped columns to view

   Nicklas Avén <nicklas.aven@jordogskog.no> — 2016-02-22T22:35:18Z

   ---- Tom Lane skrev ----
   
   > 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
   
   
   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
   

8. Re: ERROR: cannot convert relation containing dropped columns to view

   Tom Lane <tgl@sss.pgh.pa.us> — 2016-02-29T23:39:51Z

   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