Resetting priveleges on a table

On Mar 14, 2006, at 9:54 AM, Bryan White wrote:

How can I clean this up. Would dropping the user have any effect?

This is on 7.4 if that makes a difference.

dropping the user will leave dangling permissions (ie, Pg will report
them as being granted to user "103" or whatever Id that user happened
to have).

not sure why you're not able to revoke permissions. that seems
curious to me.

Vivek Khera <vivek@khera.org> writes:

not sure why you're not able to revoke permissions. that seems
curious to me.

You need to revoke them as that user, likely. REVOKE really means
"revoke grants I made", not "revoke any grant anybody made".

regards, tom lane

Tom Lane wrote:

You need to revoke them as that user, likely. REVOKE really means
"revoke grants I made", not "revoke any grant anybody made".

Ok I tried logging is as that user. Oddly after the revoke then only
grant that disappeared was one I created.

Maybe it has something to do with 'grant option' permissions which seem
to have been created here.

Transscript: (pconner is the obsolet user, bryan is my account)

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)

ec=# select current_user;
current_user
--------------
pconner
(1 row)

ec=# revoke all on bulkuploadcfg from public;
REVOKE
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}
(1 row)

ec=# revoke all on bulkuploadcfg from pconner;
REVOKE
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}
(1 row)

ec=# revoke grant option for all on bulkuploadcfg from pconner;
REVOKE
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}
(1 row)

--
Bryan

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.

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?

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.

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.

regards, tom lane

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