Should the permissions of a deleted user get assigned to a new user
as in the example below?

Martin

$ createdb test
CREATE DATABASE
$ psql test
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

test=# create table abc(a int, b int);
CREATE
test=# select relacl from pg_class where relname='abc';
 relacl 
--------
 
(1 row)

test=# create user martin;
CREATE USER
test=# grant all on abc to martin;
GRANT
test=# select relacl from pg_class where relname='abc';
               relacl                
-------------------------------------
 {=,postgres=arwdRxt,martin=arwdRxt}
(1 row)

test=# drop user martin;
DROP USER
test=# select relacl from pg_class where relname='abc';
              relacl              
----------------------------------
 {=,postgres=arwdRxt,101=arwdRxt}
(1 row)

test=# create user tom;
CREATE USER
test=# select relacl from pg_class where relname='abc';
              relacl              
----------------------------------
 {=,postgres=arwdRxt,tom=arwdRxt}
(1 row)

test=# select version();
                               version                               
---------------------------------------------------------------------
 PostgreSQL 7.2b4 on i386-unknown-freebsd4.3, compiled by GCC 2.95.3
(1 row)

test=# 


Fri, 11 Jan 2002 14:10:46 -0500
Martin Renters <martin@datafax.com>
bug in permission handling?
Martin Renters <martin@datafax.com> writes:
> Should the permissions of a deleted user get assigned to a new user
> as in the example below?

That can happen, since the default "usesysid" assignment is "max
existing usesysid + 1".  If you delete the last user then their sysid
becomes a candidate for reassignment.  This is not real good, but fixing
it isn't that high on the priority list (and is difficult to do unless
we take away the option of hand-assigned sysids ... otherwise we could
just have a sequence generator for sysids).

			regards, tom lane

Mon, 14 Jan 2002 10:29:01 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: bug in permission handling? 
On Mon, Jan 14, 2002 at 10:29:01AM -0500, Tom Lane wrote:
> Martin Renters <martin@datafax.com> writes:
> > Should the permissions of a deleted user get assigned to a new user
> > as in the example below?
> 
> That can happen, since the default "usesysid" assignment is "max
> existing usesysid + 1".  If you delete the last user then their sysid
> becomes a candidate for reassignment.  This is not real good, but fixing
> it isn't that high on the priority list (and is difficult to do unless
> we take away the option of hand-assigned sysids ... otherwise we could
> just have a sequence generator for sysids).

Isn't it possible for PostgreSQL to delete permissions on tables when a
user gets deleted?  It seems to be a bit of a security issue when a new
user suddenly inherits permissions he shouldn't have.

Martin

Mon, 14 Jan 2002 11:12:48 -0500
Martin Renters <martin@datafax.com>
Re: bug in permission handling?
Martin Renters <martin@datafax.com> writes:
> Isn't it possible for PostgreSQL to delete permissions on tables when a
> user gets deleted?

Not as long as users span multiple databases.  The deleting backend
can't even get to the other databases in which the user might have
permissions.

			regards, tom lane

Mon, 14 Jan 2002 11:15:06 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: bug in permission handling? 
On Monday 14 January 2002 10:15 am, Tom Lane wrote:
> Martin Renters <martin@datafax.com> writes:
> > Isn't it possible for PostgreSQL to delete permissions on tables when a
> > user gets deleted?
>
> Not as long as users span multiple databases.  The deleting backend
> can't even get to the other databases in which the user might have
> permissions.
>

Could we have it delete all the users permissions in the current database?  
Or at least do this when we have schema support, as I think that there will 
typically be only one database (I think that is what I have heard).

I think that extranious permissions whether they are misassgned to a new 
user, or not assigned to anyone are a bad thing.

Mon, 14 Jan 2002 14:59:02 -0600
"Matthew T. O'Connor" <matthew@zeut.net>
Re: bug in permission handling?
Matthew T. O'Connor writes:

> I think that extranious permissions whether they are misassgned to a new
> user, or not assigned to anyone are a bad thing.

Well, Unix systems have been working like that for decades and no one has
come up with a bright idea how to fix it.

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 14 Jan 2002 18:42:52 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: bug in permission handling?
At 10:29 AM 14-01-2002 -0500, Tom Lane wrote:
>Martin Renters <martin@datafax.com> writes:
>> Should the permissions of a deleted user get assigned to a new user
>> as in the example below?
>
>That can happen, since the default "usesysid" assignment is "max
>existing usesysid + 1".  If you delete the last user then their sysid
>becomes a candidate for reassignment.  This is not real good, but fixing
>it isn't that high on the priority list (and is difficult to do unless
>we take away the option of hand-assigned sysids ... otherwise we could
>just have a sequence generator for sysids).

I think the sequence way is good for now - don't reuse user ids. However
what's the max possible user id? 

Also that way if someone screws up and deletes the wrong user, it might
still be possible to restore the user and permissions.

Cheerio,
Link.


Tue, 15 Jan 2002 10:44:04 +0800
Lincoln Yeoh <lyeoh@pop.jaring.my>
Re: bug in permission handling? 
On Mon, 14 Jan 2002, Tom Lane wrote:

> Martin Renters <martin@datafax.com> writes:
> > Should the permissions of a deleted user get assigned to a new user
> > as in the example below?
> 
> That can happen, since the default "usesysid" assignment is "max
> existing usesysid + 1".  If you delete the last user then their sysid
> becomes a candidate for reassignment.  This is not real good, but fixing
> it isn't that high on the priority list (and is difficult to do unless
> we take away the option of hand-assigned sysids ... otherwise we could
> just have a sequence generator for sysids).

Another slight bug with CreateUser() -- there does not appear to be any
checking for potential overflow of sysid. The function scans pg_shadow to
find the largest usrsysid. Once obtained:

    /* If no sysid given, use max existing id + 1 */
    if (!havesysid)
        sysid = max_id + 1;

Gavin


Tue, 15 Jan 2002 14:51:21 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: bug in permission handling? 
On Tue, 15 Jan 2002, Gavin Sherry wrote:

> On Mon, 14 Jan 2002, Tom Lane wrote:
> 
> > Martin Renters <martin@datafax.com> writes:
> > > Should the permissions of a deleted user get assigned to a new user
> > > as in the example below?
> > 
> > That can happen, since the default "usesysid" assignment is "max
> > existing usesysid + 1".  If you delete the last user then their sysid
> > becomes a candidate for reassignment.  This is not real good, but fixing
> > it isn't that high on the priority list (and is difficult to do unless
> > we take away the option of hand-assigned sysids ... otherwise we could
> > just have a sequence generator for sysids).
> 
> Another slight bug with CreateUser() -- there does not appear to be any
> checking for potential overflow of sysid. The function scans pg_shadow to
> find the largest usrsysid. Once obtained:
> 
>     /* If no sysid given, use max existing id + 1 */
>     if (!havesysid)
>         sysid = max_id + 1;
> 

Left this bit off:

template1=# create user def with sysid 2147483647;
CREATE USER
template1=# create user def2;
CREATE USER
template1=# create user def3;
ERROR:  Cannot insert a duplicate key into unique index 
pg_shadow_usesysid_index
template1=# select usesysid from pg_shadow where usename ~ 'def';
  usesysid
-------------
  2147483647
 -2147483648
(2 rows)

template1=# select version();
                               version
---------------------------------------------------------------------
 PostgreSQL 7.2b4 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)

template1=#

Gavin



Tue, 15 Jan 2002 15:06:23 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: bug in permission handling? 
On Mon, 14 Jan 2002, Peter Eisentraut wrote:

> Matthew T. O'Connor writes:
> 
> > I think that extranious permissions whether they are misassgned to a new
> > user, or not assigned to anyone are a bad thing.
> 
> Well, Unix systems have been working like that for decades and no one has
> come up with a bright idea how to fix it.

Sorry to bring this up again a few weeks later. It occurs to me that this
really isn't an answer. When adding a new user to a UNIX system, the
relevant command would have *at least* to scan the entire file system to
determine if the max(uid + 1) (from /etc/passwd) owned anything. This is
unreasonable. 

In the case of postgres, however, all objects in the system are
necessarily registered in the system tables. One could easily determine a
sysid which owns no objects by scanning the attributes of those relations
which reference objects in the system -- pg_aggregate.aggowner,
pg_class.relowner, etc -- and add one to the maximum sysid found.

I was going to run up a patch for this, but it wold be premature given
the introduction of schemas in 7.3. Once implemented, it would be trivial
to add a test of schema ownership and incorporate this into the idea
above.

Gavin


Sat, 26 Jan 2002 16:27:32 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: bug in permission handling?
Gavin Sherry <swm@linuxworld.com.au> writes:
> I was going to run up a patch for this, but it wold be premature given
> the introduction of schemas in 7.3. Once implemented, it would be trivial
> to add a test of schema ownership and incorporate this into the idea
> above.

Unfortunately, looking into databases you aren't connected to is far
from trivial.  As long as users span multiple databases this problem
is not really soluble...

			regards, tom lane

Sat, 26 Jan 2002 01:24:29 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: bug in permission handling? 
On Sat, 26 Jan 2002, Tom Lane wrote:

> Gavin Sherry <swm@linuxworld.com.au> writes:
> > I was going to run up a patch for this, but it wold be premature given
> > the introduction of schemas in 7.3. Once implemented, it would be trivial
> > to add a test of schema ownership and incorporate this into the idea
> > above.
> 
> Unfortunately, looking into databases you aren't connected to is far
> from trivial.  As long as users span multiple databases this problem
> is not really soluble...

Argh. Of course, my bad.

Gavin


Sat, 26 Jan 2002 19:09:32 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: bug in permission handling? 