Обсуждение: BUG #1161: User permissions are kept, even if user is dropped
The following bug has been logged online: 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: Hi, 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. Best regards, Martin
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
Hi, > since the dropped user is very unlikely to be resurrected, the correct=20 > answer would be to remove all dangling permissions on the existing=20 > objects. Using a sequence would only clutter the system with unused=20 > grants. What about ownership? would that mean you want to delete the object? > Since DROP USER is only rarely used, it would be okay if this operation > is expensive. The problem is not the drop being expensive. The problem is that tables=20 are managed withing a database, and you cannot access a database without=20 connecting to it, and it is not an option to connect to other databases to= =20 do such a thing on any command. So when you drop a user, you do not have access to acl so as to fix them=20 (i.e. removing dandling permissions). That may be done on the current database, but that is all. Think of the system. That would mean deleting/fixing all files owned by a= =20 user when the user is removed, on whatever partition, maybe not even=20 mounted on the host. Not really possible, and not a good idea to try... So it looks much simpler to fix the real issue by avoiding the userid to=20 be reused. The dandling permission cost is low. Also, I would not be happy if deleting a user would mean deleting all=20 objects owned by that user, esp. as I cannot know simply what they are. > At least a select statement to gather these dangling permissions > should be available in the documentation. It is a per database stuff: you must do it for every database. This very=20 query is in the todo list of my pgadvisor stuff (see=20 http://pg-advisor.projects.postgresql.org/). However I need some non=20 available support from the backend that was rejected when I submitted a patch (8 lines of code:-). So it is unlikely to be added soon. > PS: Btw: I seem to be unable to locate the TODO-list that should be > referenced before posting a bug-report. Any hints? simply follow "bug reporting guidelines" on http://www.postgresql.org/ ? --=20 Fabien COELHO _ http://www.cri.ensmp.fr/~coelho _ Fabien.Coelho@ensmp.fr CRI-ENSMP, 35, rue Saint-Honor=E9, 77305 Fontainebleau cedex, France phone: (+33|0) 1 64 69 {voice: 48 52, fax: 47 09, standard: 47 08} ________ All opinions expressed here are mine _________
> I just want the system to remove the 102, since it is of no use. I understood that. > As I said, I haven't thought about it in-depth, but keeping the permissio= ns > with the numeric user is a bad idea. It is only a bad idea if the same numerical user id is reused. If not,=20 this is not really a problem. It is not beautiful, but it does not harm. >> So it looks much simpler to fix the real issue by avoiding the userid to >> be reused. The dandling permission cost is low. > > Would this keep the old permissions on the objects? Yes, but no user would take it, so that would not be a security issue. > Something like select relname from pg_class where relacl similar to=20 > '[0-9]+=3D' would be sufficient. Yep. Not with this very regexpr (think of user "tp01"), but something like that could work, indeed. --=20 Fabien COELHO _ http://www.cri.ensmp.fr/~coelho _ Fabien.Coelho@ensmp.fr CRI-ENSMP, 35, rue Saint-Honor=E9, 77305 Fontainebleau cedex, France phone: (+33|0) 1 64 69 {voice: 48 52, fax: 47 09, standard: 47 08} ________ All opinions expressed here are mine _________
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> 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.
In the last discussion of this issue, I think we had agreed in principle
that it'd be a good idea to use a cluster-wide sequence generator to
assign default user and group ids. Nobody's got round to making it
happen though. While I don't see any fundamental technical reason why a
sequence object couldn't be relisshared, there's no way to create such a
sequence in the present code --- bringing the thing into being during
initdb would certainly be the major bit of work needed to make it
happen.
(I have some vague recollection that we discussed how to do that during
the last go-round --- if you want to work on this, it'd be a good idea
to look in the archives first.)
regards, tom lane
Dear Tom. > (I have some vague recollection that we discussed how to do that during > the last go-round --- if you want to work on this, it'd be a good idea > to look in the archives first.) Ok. I found a thread initiated by you in january 2003. I read it quickly. From a practical point of view, I wish I could download all messages from this thread so as to do the reading in my mail user agent. Does not seem possible from the web interface I found. I don't like the idea of max(used user sysid) anyway, because if someone creates a user with maxint, then createuser might be broken. Just for the fun, with the current postgres: psql> CREATE USER nobody WITH SYSID 2147483647; -- ((2**31)-1) psql> CREATE USER bla; -- yes, it works... psql> CREATE USER wip; ERROR: duplicate key violates unique constraint "pg_shadow_usesysid_index" psql> SELECT usename, usesysid FROM pg_user; ... nobody | 2147483647 bla | -2147483648 Warf! I think that what is practical is to iterate through the sequence if some user already exists. Collisions are unlikely, so it would not be expensive. So the only problem is to implement system-wide "cluster" sequences... -- Fabien Coelho - coelho@cri.ensmp.fr
On Tue, Jun 08, 2004 at 05:27:31PM +0200, Fabien COELHO wrote: > From a practical point of view, I wish I could download all messages from > this thread so as to do the reading in my mail user agent. Does not seem > possible from the web interface I found. No, there is no interface to get the mboxes of the archived messages. Several people have complained in the past (Neil Conway, me, I think Andrew Dunstan too) and Marc has promised to do it several times but it's still not there :-( IMHO this is a bug in the lists mechanism ... -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Hay dos momentos en la vida de un hombre en los que no debería especular: cuando puede permitírselo y cuando no puede" (Mark Twain)
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> So the only problem is to implement system-wide "cluster" sequences...
We certainly can't extend the regular CREATE SEQUENCE command this way;
you can't create shared objects after initdb.
The "clean" way to handle this would be to extend the bootstrap command
parser to accept a command along the lines of CREATE [SHARED] SEQUENCE
and then add a file in src/include/catalog that defines the shared
user-id-generator sequence. (Look at how pg_shadow is created for
precedent.)
Not having looked recently, I have no idea how much pain is implied by
the preceding paragraph ;-). I'd be willing to accept cruder
compromises if that approach seems impractical, but please look to see
if it can be done nicely first.
regards, tom lane
Dear Tom,
> The "clean" way to handle this would be to extend the bootstrap command
> parser to accept a command along the lines of CREATE [SHARED] SEQUENCE
> and then add a file in src/include/catalog that defines the shared
> user-id-generator sequence. (Look at how pg_shadow is created for
> precedent.)
Ok. I'll look into that to evaluate what is the impact.
> Not having looked recently, I have no idea how much pain is implied by
> the preceding paragraph ;-). I'd be willing to accept cruder
> compromises if that approach seems impractical, but please look to see
> if it can be done nicely first.
Ok.
For crude stuff, I can contribute simple and efficient ideas:
I was thinking that any integer attribute of any tuple in a shared
relation would be ok to store a sequence value. There are only 3 shared
relations, pg_{database,group,shadow}. Thus, something like a speudo
"next_sysid" user/group could store the next value in the relevant shared
relations.
This approach would be light weight from the implementation point of view.
Not very clean, but that would be easy and would not change much the
catalog.
--
Fabien Coelho - coelho@cri.ensmp.fr
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> I was thinking that any integer attribute of any tuple in a shared
> relation would be ok to store a sequence value.
Certainly not --- when there are multiple versions of the tuple because
of MVCC rules, which do you use?
regards, tom lane
Dear Tom, >> I was thinking that any integer attribute of any tuple in a shared >> relation would be ok to store a sequence value. > > Certainly not --- when there are multiple versions of the tuple because > of MVCC rules, which do you use? I understand you concern, but I think the troubles depends on how the value is actually used. I agree it would not be a real sequence with nextval() and so, but the purpose is just to find an non already used sysid. The locking mecanism on the update of this special account would insure that the there is no possible conflict. <begin> SELECT usesysid AS old_sysid_value FROM pg_shadow WHERE usename='next_sysid' FOR UPDATE; // iterate to find some new sysid starting from previous value UPDATE usesysid = new_found_sysid WHERE usename='next_sysid'; INSERT new user with old_sysid_value; <end> So the concern I would see is more on the fact that there is a lock that would block concurrent "create user", especially if done in a long transaction, so IMHO this is performance/contention issue, but there is no real semantical issue. What is lost is the no-lock nature of the sequence update with nextval. I'm not sure the performance would be a big trouble, because create user are not issued that often, and I would not expect them to appear within a large transaction. Anyway I'm planing to hace a look at the real thing first (shared seq). So no worry, and thanks for your question. -- Fabien Coelho - coelho@cri.ensmp.fr
little example of generating usesysid
create user test sysid 2147483647;
CREATE USER
create user test1;
CREATE USER
select * from pg_shadow;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd |
valuntil | useconfig
----------+-------------+-------------+----------+-----------+--------+----------+-----------
postgres | 1 | t | t | t |
| |
test | 2147483647 | f | f | f |
| |
test1 | -2147483648 | f | f | f |
| |
(3 rows)
create user test2;
psql:./tt:5: ERROR: duplicate key violates unique constraint
"pg_shadow_usesysid_index"
And I have still a question:
How to remove privileges of the nonexistent (removed) user?
And the offer:
Can realize removal of privileges of nonexistent users during time vacuum?
PS: Sorry for my ugly english
> And I have still a question: > How to remove privileges of the nonexistent (removed) user? I'm not sure it is even desirable? There are 2 differents concepts: - logins with a user name, password... - sysids which is really an int. a login must have a sysid, but a sysid may or may not correspond to a login. As it is implemented, the privileges belong to the sysids. This give the opportunity (let's call that a feature) to recreate a deleted user that would reclaim its previous status wrt priviliges. > And the offer: > Can realize removal of privileges of nonexistent users during time vacuum? Although that could be done, I'm not sure I would like such a thing to happen. -- Fabien Coelho - coelho@cri.ensmp.fr