Horton

Thread

  1. dangling permission on tables after drop user.

    Vivek Khera <khera@kcilink.com> — 2004-09-29T20:24:11Z

    I have a database which started on Pg 7.1, moved to 7.2 via 
pg_dump/restore, and ultimately to Pg 7.4 likewise.

While it was in 7.2, I added one user and granted access to various 
tables.  After the 7.4 migration, that user was no longer needed, so 
was removed via "dropuser" command line tool.

Now, when I pg_dump that db using the version 7.4.5 tools, I cannot 
restore because there are still grants in there for this phantom user:

REVOKE ALL ON TABLE partners FROM PUBLIC;
GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE partners TO www;
GRANT ALL ON TABLE partners TO "102";

there is no user with ID 102 in the pg_user view.  pg_restore complains 
about the missing user "102".  And no, the user was not "102" it was 
the name of a (former) employee.

My questions are:

1) did I do something wrong in dropping that user?
2) how do I fix this in my system tables?

The gross hack is to pg_restore to an ascii file and delete those GRANT 
lines, but the compressed dump is over 2Gb for this database.


Vivek Khera, Ph.D.
+1-301-869-4449 x806

  2. Re: dangling permission on tables after drop user.

    Richard Huxton <dev@archonet.com> — 2004-09-29T20:55:14Z

    Vivek Khera wrote:
> 
> there is no user with ID 102 in the pg_user view.  pg_restore complains 
> about the missing user "102".  And no, the user was not "102" it was the 
> name of a (former) employee.

> The gross hack is to pg_restore to an ascii file and delete those GRANT 
> lines, but the compressed dump is over 2Gb for this database.

Am I missing something Vivek, or should the gross hack be "creating a 
user with id=102" ?

-- 
   Richard Huxton
   Archonet Ltd

  3. Re: dangling permission on tables after drop user.

    Vivek Khera <khera@kcilink.com> — 2004-09-29T21:07:38Z

    On Sep 29, 2004, at 4:55 PM, Richard Huxton wrote:

> Vivek Khera wrote:
>> there is no user with ID 102 in the pg_user view.  pg_restore 
>> complains about the missing user "102".  And no, the user was not 
>> "102" it was the name of a (former) employee.
>
>> The gross hack is to pg_restore to an ascii file and delete those 
>> GRANT lines, but the compressed dump is over 2Gb for this database.
>
> Am I missing something Vivek, or should the gross hack be "creating a 
> user with id=102" ?

And how exactly does one accomplish this?  pg_users is a view so you 
can't insert into it.


Vivek Khera, Ph.D.
+1-301-869-4449 x806

  4. Re: dangling permission on tables after drop user.

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-09-29T21:35:58Z

    On Wed, Sep 29, 2004 at 05:07:38PM -0400, Vivek Khera wrote:
> 
> On Sep 29, 2004, at 4:55 PM, Richard Huxton wrote:
> 
> >Vivek Khera wrote:
> >>there is no user with ID 102 in the pg_user view.  pg_restore 
> >>complains about the missing user "102".  And no, the user was not 
> >>"102" it was the name of a (former) employee.
> >
> >>The gross hack is to pg_restore to an ascii file and delete those 
> >>GRANT lines, but the compressed dump is over 2Gb for this database.
> >
> >Am I missing something Vivek, or should the gross hack be "creating a 
> >user with id=102" ?
> 
> And how exactly does one accomplish this?  pg_users is a view so you 
> can't insert into it.

CREATE USER ... WITH SYSID 102;

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Dios hizo a Adán, pero fue Eva quien lo hizo hombre.

  5. Re: dangling permission on tables after drop user.

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-09-29T21:46:17Z

    Vivek Khera <khera@kcilink.com> writes:
> On Sep 29, 2004, at 4:55 PM, Richard Huxton wrote:
>> Am I missing something Vivek, or should the gross hack be "creating a 
>> user with id=102" ?

> And how exactly does one accomplish this?

CREATE USER.

			regards, tom lane

  6. Re: dangling permission on tables after drop user.

    Vivek Khera <khera@kcilink.com> — 2004-09-30T13:32:30Z

    On Sep 29, 2004, at 5:35 PM, Alvaro Herrera wrote:

>>> Am I missing something Vivek, or should the gross hack be "creating a
>>> user with id=102" ?
>>
>> And how exactly does one accomplish this?  pg_users is a view so you
>> can't insert into it.
>
> CREATE USER ... WITH SYSID 102;

Ok. I did that.  So now how do I get rid of that user and all the 
grants?  DROP USER ends up with the dangling GRANTs still hanging 
about.

Is there no way to drop a user and have the necessary grants disappear? 
  How does one drop a user cleanly?

Vivek Khera, Ph.D.
+1-301-869-4449 x806

  7. Re: dangling permission on tables after drop user.

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-09-30T14:03:29Z

    On Thu, Sep 30, 2004 at 09:32:30AM -0400, Vivek Khera wrote:
> 
> On Sep 29, 2004, at 5:35 PM, Alvaro Herrera wrote:
> 
> >>>Am I missing something Vivek, or should the gross hack be "creating a
> >>>user with id=102" ?
> >>
> >>And how exactly does one accomplish this?  pg_users is a view so you
> >>can't insert into it.
> >
> >CREATE USER ... WITH SYSID 102;
> 
> Ok. I did that.  So now how do I get rid of that user and all the 
> grants?  DROP USER ends up with the dangling GRANTs still hanging 
> about.
> 
> Is there no way to drop a user and have the necessary grants disappear? 
>  How does one drop a user cleanly?

I'm afraid you'll have to ALTER TABLE (or whatever) for each of these ... 
I don't think there is a command that would help you do that
automatically.  You can cheat by looking at system catalogs for the
acl column (e.g. pg_class.relacl) and using that in a function.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El sentido de las cosas no viene de las cosas, sino de
las inteligencias que las aplican a sus problemas diarios
en busca del progreso." (Ernesto Hernández-Novich)

  8. Re: dangling permission on tables after drop user.

    Andrew Sullivan <ajs@crankycanuck.ca> — 2004-10-06T18:01:54Z

    On Thu, Sep 30, 2004 at 10:03:29AM -0400, Alvaro Herrera wrote:
> 
> I'm afraid you'll have to ALTER TABLE (or whatever) for each of these ... 
> I don't think there is a command that would help you do that
> automatically.  You can cheat by looking at system catalogs for the
> acl column (e.g. pg_class.relacl) and using that in a function.

Andrew Hammond is about to (has?) post some helper code he has for
managing ACLs more easily.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what 
you told them to.  That actually seems sort of quaint now.
		--J.D. Baldwin