Thread

1. insert into select from

   Jan Branbergen <jan@entrisphere.com> — 2001-05-03T23:46:03Z

   ============================================================================
                           POSTGRESQL BUG REPORT TEMPLATE
   ============================================================================
   
   
   Your name               : Jan Branbergen
   Your email address      : jan@entrisphere.com
   
   
   System Configuration
   ---------------------
     Architecture (example: Intel Pentium)         : Intel PII
   
     Operating System (example: Linux 2.0.26 ELF)  : NetBSD
   
     PostgreSQL version (example: PostgreSQL-7.0):   PostgreSQL-7.0.3
   
     Compiler used (example:  gcc 2.8.0)           :
   
   
   Please enter a FULL description of your problem:
   ------------------------------------------------
   When i do a "NSERT INTO screen_filter_field( screen_filter_id,
   screen_field_id, seqno, visible, orderno ) SELECT 182, id, seqno, visible,
   orderno FROM screen_field WHERE screen_id = 1" i get a permission denied on
   table screen_field; i have select, insert, update & delete access to table
   screen_filter_field, and select access to table b. when i add update access
   to table b, the statement executes fine.
   
   
   
   
   Please describe a way to repeat the problem.   Please try to provide a
   concise reproducible example, if at all possible:
   ----------------------------------------------------------------------
   
   
   
   
   
   If you know how this problem might be fixed, list the solution below:
   ---------------------------------------------------------------------
   
   no idea
   
   
   
   

2. Re: insert into select from

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-04T19:52:54Z

   "Jan Branbergen" <jan@entrisphere.com> writes:
   > When i do a "NSERT INTO screen_filter_field( screen_filter_id,
   > screen_field_id, seqno, visible, orderno ) SELECT 182, id, seqno, visible,
   > orderno FROM screen_field WHERE screen_id = 1" i get a permission denied on
   > table screen_field; i have select, insert, update & delete access to table
   > screen_filter_field, and select access to table b. when i add update access
   > to table b, the statement executes fine.
   
   I can't duplicate this.  Perhaps you have rules or triggers you didn't
   tell us about?
   
   			regards, tom lane
   
   

3. Re: insert into select from

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-04T20:05:20Z

   "Jan Branbergen" <jan@entrisphere.com> writes:
   > i have constraints defined on the tables to force ref integrity, but nothing
   > else. i can send my schema if you would like.
   
   If you have a foreign key reference from screen_filter_field to
   screen_filter, that would explain it --- but I'd expect you'd be
   seeing the permissions issue on any insert into screen_filter_field,
   not only an INSERT/SELECT.
   
   See past discussions about why the foreign key triggers use SELECT FOR
   UPDATE rather than plain SELECT.  I am not sure whether this is any
   different in 7.1 ...
   
   			regards, tom lane