Horton

Thread

  1. bug in permission handling?

    Martin Renters <martin@datafax.com> — 2002-01-11T19:10:46Z

    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=#

  2. Re: bug in permission handling?

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-01-14T15:29:01Z

    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

  3. Re: bug in permission handling?

    Martin Renters <martin@datafax.com> — 2002-01-14T16:12:48Z

    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

  4. Re: bug in permission handling?

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-01-14T16:15:06Z

    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

  5. Re: bug in permission handling?

    Matthew T. O'Connor <matthew@zeut.net> — 2002-01-14T20:59:02Z

    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.

  6. Re: bug in permission handling?

    Peter Eisentraut <peter_e@gmx.net> — 2002-01-14T23:42:52Z

    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

  7. Re: bug in permission handling?

    Lincoln Yeoh <lyeoh@pop.jaring.my> — 2002-01-15T02:44:04Z

    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.

  8. Re: bug in permission handling?

    Gavin Sherry <swm@linuxworld.com.au> — 2002-01-15T03:51:21Z

    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

  9. Re: bug in permission handling?

    Gavin Sherry <swm@linuxworld.com.au> — 2002-01-15T04:06:23Z

    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

  10. Re: bug in permission handling?

    Gavin Sherry <swm@linuxworld.com.au> — 2002-01-26T05:27:32Z

    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

  11. Re: bug in permission handling?

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-01-26T06:24:29Z

    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

  12. Re: bug in permission handling?

    Gavin Sherry <swm@linuxworld.com.au> — 2002-01-26T08:09:32Z

    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