469,628 Members | 1,001 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,628 developers. It's quick & easy.

dangling permission on tables after drop user.

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #1
6 1360
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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #2

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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #3
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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #4
Vivek Khera <kh***@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #5
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)
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #6
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 | aj*@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Wayne Wengert | last post: by
reply views Thread by Engwar | last post: by
13 posts views Thread by Aravind | last post: by
20 posts views Thread by __PPS__ | last post: by
10 posts views Thread by Anthony Best | last post: by
2 posts views Thread by C G | last post: by
1 post views Thread by Philippe Lang | last post: by
2 posts views Thread by masri999 | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.