Postgres Pro
Downloads
Home   >   mailing lists

Thread: dropping user doesn't erase his rights.

dropping user doesn't erase his rights.

From
Hubert depesz Lubaczewski
Date:
Versions tested:
  7.4devel from cvs and 7.2.x (i'm not sure about x since the test was
  done by somebody else).

Description:
  When dropping user his rights stay in database creating possible
security breach.

Sample Code:
  create table xxx (...);
  create user test;
  grant select on xxx to test;
  select relacl from pg_class where relname=3D'czasy';
  drop user test;
  select relacl from pg_class where relname=3D'czasy';

right now it's not even possible to revoke this rights:
# revoke all on xxx from test;
ERROR:  user "test" does not exist
[[local]:5432] [depesz@depesz]
# revoke all on xxx from 102;=20
ERROR:  parser: syntax error at or near "102" at character 24

I belive drop user should automatically drop all user privileges, and
even if not there should be simple syntax to drop all user privileges
from all objects in database (dropping all privileges "by hand" might be
major pain when dealing with > 100 tables with several hundreds of
views, procedures and so on.

depesz

--=20
hubert depesz lubaczewski                          http://www.depesz.pl/
>  wynajm=EA mieszkanie - 60 metr=F3w, 3 pokoje, piast=F3w - od lutego (po =
  <
-=3D>       remoncie) interesuje ci=EA - napisz: depesz@depesz.pl        <=
=3D-

Re: dropping user doesn't erase his rights.

From
Tom Lane
Date:
Hubert depesz Lubaczewski <depesz@depesz.pl> writes:
> I belive drop user should automatically drop all user privileges

Difficult to do, when those privileges might be recorded in databases
you're not even connected to at the time of the drop.

            regards, tom lane

Re: dropping user doesn't erase his rights.

From
Hubert depesz Lubaczewski
Date:
On Thu, Jan 09, 2003 at 11:21:56AM -0500, Tom Lane wrote:
> Difficult to do, when those privileges might be recorded in databases
> you're not even connected to at the time of the drop.

I belive it would be pretty difficult, but leaving it "just like that"
creates ssecurity breach (imagine someone droping user, beliving that
everytinh is o.k.), than someone else creates different user but with
keeping unused sysid (this might be the case with system users and
keeping system user-id with database user-id the same) - which happens
to be "not unused". i'm not sure if i'm clear about it.

depesz

--=20
hubert depesz lubaczewski                          http://www.depesz.pl/
>  wynajm=EA mieszkanie - 60 metr=F3w, 3 pokoje, piast=F3w - od lutego (po =
  <
-=3D>       remoncie) interesuje ci=EA - napisz: depesz@depesz.pl        <=
=3D-

Re: dropping user doesn't erase his rights.

From
Sean Chittenden
Date:
> > Difficult to do, when those privileges might be recorded in
> > databases you're not even connected to at the time of the drop.
>=20
> I belive it would be pretty difficult, but leaving it "just like
> that" creates ssecurity breach (imagine someone droping user,
> beliving that everytinh is o.k.), than someone else creates
> different user but with keeping unused sysid (this might be the case
> with system users and keeping system user-id with database user-id
> the same) - which happens to be "not unused". i'm not sure if i'm
> clear about it.

Wouldn't an ON DELETE trigger on the system catalogs work?  I'd think
it would be possible to select the tables and groups that a user had
privs to and iterate through each calling REVOKE.  -sc

--=20
Sean Chittenden