Thread: bug in permission handling?
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,
compiledby GCC 2.95.3
(1 row)
test=#
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
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
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
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.
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
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.
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
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 GCCegcs-2.91.66 (1 row) template1=# Gavin
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
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
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