Thread: Removing Users and Revoking Privileges
To All, I am running Postgres version 7.4.19. One thing I wanted to do and have successfully completed is changing the ownership of a Postgres database and all of its tables to a new owner. I have also successfully granted access privileges to the new owner. What I have been unable to do successfully is to remove two users who should no longer have access to the database or its tables. As super user postgres, I have attempted to REVOKE access privileges for those two users, but to no avail. The REVOKE command appears to work and does not generate an error message, however, upon inspection the two users remain and continue to have all of the same access privileges they had before executing the REVOKE command. I do not know what user originally created the database. I have though attempted to revoke privileges as not only user postgres, but also as the new owner of the data base, as well as both of the two users for which I wish to revoke access privileges. The two users I want to remove or, at least revoke privileges for, are also associated with other databases, so I don't believe I can simply do a DROP USER. If I do that, I may wind up negatively impacting other Postgres databases. Let me know, if you have any ideas. Thanks
Attachment
On Fri, Feb 29, 2008 at 01:12:50PM -0500, Carl McCalla wrote: > What I have been unable to do successfully is to remove two users who > should no longer have access to the database or its tables. As super > user postgres, I have attempted to REVOKE access privileges for those > two users, but to no avail. The REVOKE command appears to work and does > not generate an error message, however, upon inspection the two users > remain and continue to have all of the same access privileges they had > before executing the REVOKE command. Something that some people trip over is that there's still access for PUBLIC or access via a role. Perhaps you could show the \z output for the table in question... Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy
Attachment
Carl McCalla wrote: > To All, > > I am running Postgres version 7.4.19. > > One thing I wanted to do and have successfully completed is changing the > ownership of a Postgres database and all of its tables to a new owner. > I have also successfully granted access privileges to the new owner. FWIW this is all covered in a future Postgres version: if any user has remaining references on any database, the system won't let you drop it, and it will produce a list of the affected objects. That way it is a lot easier to clean the whole thing up. I know it doesn't solve your problem; I'm just mentioning it in case you're looking for reasons to migrate to a newer version. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.