Re: Resetting priveleges on a table

Tom Lane wrote:
> Bryan White <bryan@arcamax.com> writes:
>> ec=# \z bulkuploadcfg
>>                              Access privileges for database "ec"
>>   Schema |     Table     |                        Access privileges
>> --------+---------------+------------------------------------------------------------------
>>   public | bulkuploadcfg |
>> {pconner=a*r*w*d*R*x*t*/pconner,=arwdRxt/pconner,=arwdRxt/bryan}
>> (1 row)
>
> Hm, this is 7.4.what exactly?  The above should be an illegal state
> (assuming pconner is the table owner) because there is no grant option
> to bryan allowing him to grant anything to public.

ec=# select version();
                                                  version
---------------------------------------------------------------------------------------------------------
  PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2
20031022 (Red Hat Linux 3.3.2-1)
(1 row)

>
> There was an old bug that would allow you to get into this state if
> bryan was a superuser (the system would allow him to grant privileges
> anyway), but according to the CVS logs we fixed that in 7.4RC1.  This
> table wouldn't happen to be a holdover from a 7.4 beta version would it?

bryan is a super user.
>
> Another possibility is that you did an ALTER TABLE OWNER after assigning
> some initial permissions.  7.4 had that command but it didn't do
> anything about changing the ACL list to match.  I think you could have
> gotten to the above state if pconner were the original table owner and
> had done GRANT ALL TO PUBLIC, and then you altered table ownership to
> bryan and he also did GRANT ALL TO PUBLIC.

That would match the history.  A while ago I changed the owner of all
tables to 'bryan'.  I just noticed the permission strangeness today.  I
had some problems trying to load a dump of this database onto a system
running 8.0.7 with no pconner user defined.  I decided it was time to
clean this stuff up and to do that I had to go back to the source.

> Best solution might be to forcibly set the table's pg_class.relacl field
> to null (resetting all the permissions to default) and then grant what
> you want.

That seems to fix it.  Thanks!!!

--
Bryan