Hello Martin,
> Bug reference: 1161
> Logged by: Martin
> Email address: martin@4finger.net
> PostgreSQL version: 7.4
> Operating system: Linux
> Description: User permissions are kept, even if user is dropped
> Details:
> dropping and creating a user will keep the *old* permission on objects:
> The following statements will grant access to the user foe:
>
> create user friend;
> create table secret (passwd char(30));
> grant all on secret to friend;
> \dp secret;
> drop user friend;
> -- The permissions are still existing
> -- (on a numeric user-id)
> \dp secret;
> create user foe;
> -- The user foe "inherits" the old permissions
> \dp secret;
>
> This is not what I would have expected. If this behaviour is valid, there
> should be a warning in the documentation, that creating a user may inherit
> some "dangling" permissions.
I also noticed this one and was planning to report it some day.
User are managed at the cluster level. A user cannot be dropped if there
is a database owned by that user. However, the system cannot know about
objects owned by the user within databases.
I do not think it is a bad thing to say that objects belong to user ids,
so that objects are kept even if users are dropped.
I do not think it would be a good idea to drop objects, or only maybe with
some "CASCADE" keyword? Hummm... a lot of work for a small issue.
The actual simple fix would be that user ids should NOT be reused by
default. The problem is that I don't think the already used userids are
kept anywhere, even as a sequence. I haven't noticed any sequence in
pg_catalog btw, maybe there is some rational behind.
Another possible hack would be that drop user would not really drop the
user, but make it unusable (impossible name, disactivated access ?).
Well, keeping this noise does not look attractive.
So I think that the sequence would be better, if possible.
Same for groups, BTW.
Have a nice day,
--
Fabien Coelho - coelho@cri.ensmp.fr