DELETE permission problems on table with long name after upgrade to 7.3.1

Started by Andreas Langeabout 23 years ago2 messagesbugs
Jump to latest
#1Andreas Lange
anlan@ida.liu.se

Hi!

Our admin upgraded PostgreSQL 7.2.3 to 7.3.1 a while ago (dump and
restore).

Previously I had a table with a long name (>32 chars), which at creation
was truncated to proper length. The table was used from a perl-script
(using the full name) doing mostly DELETE's and INSERT's. The user
accessing the table had been granted SELECT, UPDATE and INSERT.

After the upgrade the calls to the table didn't truncate the name any
longer (as expected), and thus didn't find the table. So I renamed the
table to it's full length name, and now the scripts did find the table
again. But - deletes were no longer allowed! Selects are ok, and so are
inserts and updates, but delete gives "permission denied".

I've verified that grants are the same as before. Altering the table
name to the previous short version gives the same result.

Putting away the data in a temp table, dropping the problematic one and
recreating it made the whole thing work again.

Below is the declaration used (before and after the upgrade):

Regards,
Andreas

drop table gradsite_dev_course_classification;
create table gradsite_dev_course_classification (
cid integer
references gradsite_dev_courses (cid)
on delete cascade,
class integer
references gradsite_dev_course_classes (class)
on delete cascade,
primary key (cid, class)
);

create index gradsite_dev_course_classification_cid on
gradsite_course_classification (cid);

create index gradsite_dev_course_classification_class on
gradsite_course_classification (class);

grant select,insert,update,delete on gradsite_dev_course_classification
to gradsite;

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Andreas Lange (#1)
Re: DELETE permission problems on table with long name after upgrade to 7.3.1

Andreas Lange <anlan@ida.liu.se> writes:

After the upgrade the calls to the table didn't truncate the name any
longer (as expected), and thus didn't find the table. So I renamed the
table to it's full length name, and now the scripts did find the table
again. But - deletes were no longer allowed! Selects are ok, and so are
inserts and updates, but delete gives "permission denied".

I think this is probably pilot error --- are you sure you don't have a
table laying about with the truncated name?

The example you give does not work as-is (many references to tables not
included) and when I remove the undefined references, I see no failure.
So it doesn't persuade me that there's any bug.

regards, tom lane