Re: BUG #1161: User permissions are kept, even if user is

Hi,

> since the dropped user is very unlikely to be resurrected, the correct
> answer would be to remove all dangling permissions on the existing
> objects. Using a sequence would only clutter the system with unused
> 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
are managed withing a database, and you cannot access a database without
connecting to it, and it is not an option to connect to other databases to
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
(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
user when the user is removed, on whatever partition, maybe not even
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
be reused. The dandling permission cost is low.

Also, I would not be happy if deleting a user would mean deleting all
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
query is in the todo list of my pgadvisor stuff (see
http://pg-advisor.projects.postgresql.org/) However I need some non
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/ ?

--
Fabien COELHO _ http://www.cri.ensmp.fr/~coelho _ Fabien(dot)Coelho(at)ensmp(dot)fr
CRI-ENSMP, 35, rue Saint-Honoré, 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 _________
>From pgsql-bugs-owner(at)postgresql(dot)org Tue Jun 8 09:41:43 2004
X-Original-To: pgsql-bugs-postgresql(dot)org(at)localhost(dot)postgresql(dot)org
Received: from localhost (unknown [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id B608BD1B8C4
for <pgsql-bugs-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>; Tue, 8 Jun 2004 09:41:42 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 15742-10
for <pgsql-bugs-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>;
Tue, 8 Jun 2004 12:41:29 +0000 (GMT)
Received: from cri.ensmp.fr (orgenoy.ensmp.fr [193.48.171.195])
by svr1.postgresql.org (Postfix) with ESMTP id AD969D1B33B
for <pgsql-bugs(at)postgresql(dot)org>; Tue, 8 Jun 2004 09:41:10 -0300 (ADT)
Received: from sablons.cri.ensmp.fr (sablons.cri.ensmp.fr [10.2.14.143])
by cri.ensmp.fr (8.11.2/8.11.2/mx-cri-CRI) with ESMTP id i58Cetp28797;
Tue, 8 Jun 2004 14:40:55 +0200 (MEST)
Date: Tue, 8 Jun 2004 14:40:55 +0200 (CEST)
From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
Reply-To: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: Martin <martin(at)4finger(dot)net>
Cc: PostgreSQL Bugs List <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #1161: User permissions are kept, even if user is
In-Reply-To: <20040608112538(dot)GA19740(at)4finger(dot)net>
Message-ID: <Pine(dot)LNX(dot)4(dot)60(dot)0406081433350(dot)27846(at)sablons(dot)cri(dot)ensmp(dot)fr>
References: <20040607193857(dot)0BFD8CF4B00(at)www(dot)postgresql(dot)com>
<Pine(dot)LNX(dot)4(dot)60(dot)0406080902200(dot)27846(at)sablons(dot)cri(dot)ensmp(dot)fr>
<20040608095831(dot)GA13455(at)4finger(dot)net> <Pine(dot)LNX(dot)4(dot)60(dot)0406081209260(dot)27846(at)sablons(dot)cri(dot)ensmp(dot)fr>
<20040608112538(dot)GA19740(at)4finger(dot)net>
MIME-Version: 1.0
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 testsX-Spam-Level:
Content-Type: TEXT/PLAIN; charset=iso-8859-1; format=flowed
Content-Transfer-Encoding: QUOTED-PRINTABLE
X-Archive-Number: 200406/21
X-Sequence-Number: 8445

> 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 permissions
> with the numeric user is a bad idea.

It is only a bad idea if the same numerical user id is reused. If not,
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
> '[0-9]+=' would be sufficient.

Yep. Not with this very regexpr (think of user "tp01"), but something
like that could work, indeed.

--
Fabien COELHO _ http://www.cri.ensmp.fr/~coelho _ Fabien(dot)Coelho(at)ensmp(dot)fr
CRI-ENSMP, 35, rue Saint-Honoré, 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 _________
>From pgsql-bugs-owner(at)postgresql(dot)org Tue Jun 8 12:12:07 2004
X-Original-To: pgsql-bugs-postgresql(dot)org(at)localhost(dot)postgresql(dot)org
Received: from localhost (unknown [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id D9BD0D1B8F0
for <pgsql-bugs-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>; Tue, 8 Jun 2004 11:29:55 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 85221-05
for <pgsql-bugs-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>;
Tue, 8 Jun 2004 14:29:56 +0000 (GMT)
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
by svr1.postgresql.org (Postfix) with ESMTP id 7861ED1B57C
for <pgsql-bugs(at)postgresql(dot)org>; Tue, 8 Jun 2004 11:29:50 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl(at)localhost [127.0.0.1])
by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i58ETjEm001266;
Tue, 8 Jun 2004 10:29:45 -0400 (EDT)
To: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
Cc: Martin <martin(at)4finger(dot)net>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1161: User permissions are kept, even if user is
In-reply-to: <Pine(dot)LNX(dot)4(dot)60(dot)0406080902200(dot)27846(at)sablons(dot)cri(dot)ensmp(dot)fr>
References: <20040607193857(dot)0BFD8CF4B00(at)www(dot)postgresql(dot)com> <Pine(dot)LNX(dot)4(dot)60(dot)0406080902200(dot)27846(at)sablons(dot)cri(dot)ensmp(dot)fr>
Comments: In-reply-to Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
message dated "Tue, 08 Jun 2004 09:12:02 +0200"
Date: Tue, 08 Jun 2004 10:29:44 -0400
Message-ID: <1265(dot)1086704984(at)sss(dot)pgh(dot)pa(dot)us>
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 testsX-Spam-Level:
X-Archive-Number: 200406/22
X-Sequence-Number: 8446

Fabien COELHO <coelho(at)cri(dot)ensmp(dot)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