Thread: Resetting priveleges on a table
I have a database that has a few tables that have privileges granted by a user that no longer works here. I am the owner of these tables and the owner of the database. If I do any granting/revoking on these tables my actions do not seem to affect the privs set by this other user. The privileges I have set show up after the original user privileges in the \z output. How can I clean this up. Would dropping the user have any effect? This is on 7.4 if that makes a difference. -- Bryan White, ArcaMax Publishing Inc. The world ends when your dead. Until then you got more punishment in store. Stand it like a man... And give some back. -- Al Swearengen
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