Thread: somebody working on: Prevent default re-use of sysids for dropped users and groups?
somebody working on: Prevent default re-use of sysids for dropped users and groups?
From
schmidtm
Date:
Hi *,
is somebody working on these two issues on the TODO-List?
1) Prevent default re-use of sysids for dropped users and groups
Currently, if a user is removed while he still owns objects, a new
user given might begiven their user id and inherit the previous users objects.
2) Prevent dropping user that still owns objects, or auto-drop the
objects
if NOT I like to take care of them. ( At least I like to try it. I need
a special task to get
things started rather to read the code over and over again )
cheers,
Matthias
----------------------------------------------------------------------
Matthias Schmidt
Viehtriftstr. 49
67346 Speyer
Tel.: +49 6232 4867
Fax.: +49 6232 640089
Re: somebody working on: Prevent default re-use of sysids for dropped users and groups?
From
Tom Lane
Date:
schmidtm <schmidtm@mock-software.de> writes:
> is somebody working on these two issues on the TODO-List?
> 1) Prevent default re-use of sysids for dropped users and groups
I don't know of anyone actively working on it, but if you check the
archives you'll find that the preferred solution approach is pretty well
hashed out --- it boils down to creating a shared sequence object and
using that, rather than a MAX(sysid) query, to select default sysids.
The painful part of this is just that bootstrap mode doesn't currently
have any support for creating sequences. I don't think fixing that will
be hugely hard, but it might be a bit tedious.
> 2) Prevent dropping user that still owns objects, or auto-drop the
> objects
No one has any idea how to do this reasonably --- the problem is you
have no visibility into databases other than the one you're connected
to, so you can't tell what the user owns in other databases.
regards, tom lane
On Fri, 3 Dec 2004, Tom Lane wrote: > > 2) Prevent dropping user that still owns objects, or auto-drop the > > objects > > No one has any idea how to do this reasonably --- the problem is you > have no visibility into databases other than the one you're connected > to, so you can't tell what the user owns in other databases. > What about Alvaro's shared dependencies work: http://archives.postgresql.org/pgsql-hackers/2004-10/msg00963.php Kris Jurka
Re: somebody working on: Prevent default re-use of sysids for dropped users and groups?
From
Tom Lane
Date:
Kris Jurka <books@ejurka.com> writes: > On Fri, 3 Dec 2004, Tom Lane wrote: >> No one has any idea how to do this reasonably --- the problem is you >> have no visibility into databases other than the one you're connected >> to, so you can't tell what the user owns in other databases. > What about Alvaro's shared dependencies work: > http://archives.postgresql.org/pgsql-hackers/2004-10/msg00963.php I think the practicality of that is still TBD ... particularly with respect to the question of tracking ACL entries as opposed to just owners. If we could track such things, then the whole notion of sysids for users could probably go away, and we could just use OIDs for them. So I'm not against pursuing the idea, I'm just worried about the overhead and locking implications. regards, tom lane
Kris Jurka wrote: > > > On Fri, 3 Dec 2004, Tom Lane wrote: > > > > 2) Prevent dropping user that still owns objects, or auto-drop the > > > objects > > > > No one has any idea how to do this reasonably --- the problem is you > > have no visibility into databases other than the one you're connected > > to, so you can't tell what the user owns in other databases. > > > > What about Alvaro's shared dependencies work: > > http://archives.postgresql.org/pgsql-hackers/2004-10/msg00963.php That is for allowing comments on global tables like pg_shadow and pg_database. I don't think it relates to finding if someone owns objects in another database. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Fri, 3 Dec 2004, Bruce Momjian wrote: > > What about Alvaro's shared dependencies work: > > > > http://archives.postgresql.org/pgsql-hackers/2004-10/msg00963.php > > That is for allowing comments on global tables like pg_shadow and > pg_database. I don't think it relates to finding if someone owns > objects in another database. > I quote from the first paragraph of the given link: I'm currently playing with implementing a "shared dependency" catalog, to keep track of objects pointing to global objects, currently users and tablespaces. So it is forbidden to drop a user that owns tables (or whatever objects) on other databases. Kris Jurka
Kris Jurka wrote: > > > On Fri, 3 Dec 2004, Bruce Momjian wrote: > > > > What about Alvaro's shared dependencies work: > > > > > > http://archives.postgresql.org/pgsql-hackers/2004-10/msg00963.php > > > > That is for allowing comments on global tables like pg_shadow and > > pg_database. I don't think it relates to finding if someone owns > > objects in another database. > > > > I quote from the first paragraph of the given link: > > I'm currently playing with implementing a "shared dependency" catalog, > to keep track of objects pointing to global objects, currently users and > tablespaces. So it is forbidden to drop a user that owns tables (or > whatever objects) on other databases. Interesting. I didn't realize he was doing dependency between global and db-local objects. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Re: somebody working on: Prevent default re-use of sysids for dropped users and groups?
From
"Riccardo G. Facchini"
Date:
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > schmidtm <schmidtm@mock-software.de> writes: > > is somebody working on these two issues on the TODO-List? > > > 1) Prevent default re-use of sysids for dropped users and groups > > I don't know of anyone actively working on it, but if you check the > archives you'll find that the preferred solution approach is pretty > well > hashed out --- it boils down to creating a shared sequence object and > using that, rather than a MAX(sysid) query, to select default sysids. > The painful part of this is just that bootstrap mode doesn't > currently > have any support for creating sequences. I don't think fixing that > will > be hugely hard, but it might be a bit tedious. > > > 2) Prevent dropping user that still owns objects, or auto-drop the > > objects > > No one has any idea how to do this reasonably --- the problem is you > have no visibility into databases other than the one you're connected > to, so you can't tell what the user owns in other databases. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > Maybe mine is an impossible suggestion, or maybe is just plain stupid... but would be possible to create a "supershema" os sorts, shared by all databases? basically, a schema that is stored by default on all the databases, with a backend process that takes care of the replication. I insist, mine is just a wild suggestion, or just downright stupid... regards, Riccardo
Re: somebody working on: Prevent default re-use of sysids for dropped users and groups?
From
schmidtm
Date:
Hi Tom + *, Am 03.12.2004 um 23:58 schrieb Tom Lane: > schmidtm <schmidtm@mock-software.de> writes: >> is somebody working on these two issues on the TODO-List? > >> 1) Prevent default re-use of sysids for dropped users and groups > > I don't know of anyone actively working on it, but if you check the > archives you'll find that the preferred solution approach is pretty > well > hashed out --- it boils down to creating a shared sequence object and > using that, rather than a MAX(sysid) query, to select default sysids. > The painful part of this is just that bootstrap mode doesn't currently > have any support for creating sequences. I don't think fixing that > will > be hugely hard, but it might be a bit tedious. Do I get that right: the only reason to do max(sysid) or a user-supplied ID in CreateUser() (commands/user.c) is that we don't have the ability to get sequences over the *.BKI/initdb mechanism? If that is true and you think it's possible and worth to have sequences in the bootstrap, I volunteer to do this. > >> 2) Prevent dropping user that still owns objects, or auto-drop the >> objects > > No one has any idea how to do this reasonably --- the problem is you > have no visibility into databases other than the one you're connected > to, so you can't tell what the user owns in other databases. I think it's much better to leave this to somebody with far more understanding of the subject. > > regards, tom lane > > cheers, Matthias ---------------------------------------------------------------------- Matthias Schmidt Viehtriftstr. 49 67346 Speyer Tel.: +49 6232 4867 Fax.: +49 6232 640089
Re: somebody working on: Prevent default re-use of sysids for dropped users and groups?
From
Tom Lane
Date:
schmidtm <schmidtm@mock-software.de> writes:
> Do I get that right: the only reason to do max(sysid) or a
> user-supplied ID in CreateUser() (commands/user.c) is that we don't
> have the ability to get sequences over the *.BKI/initdb mechanism?
No, that's not quite the direction of the problem. The real reason
those facilities are there is so that you can deliberately create a user
having a previously-used sysid. And the only reason why that is needed
is that we don't have dependency tracking for references to users and
groups. If you could be certain that there were no remaining references
to a userid when it is dropped, there would be no need to be able to
resurrect it. And that would mean that we could forget the whole sysid
assignment mess and just use the regular OID generator to create unique
IDs for users and groups.
Using a shared sequence instead of max(sysid) would be merely an
incremental improvement in the existing sysid assignment rules --- it
wouldn't eliminate the entire kluge at one blow.
So if Alvaro's thing works out, the shared-sequence problem becomes moot.
Probably that's a good reason not to spend time on it just yet.
regards, tom lane
Re: somebody working on: Prevent default re-use of sysids for dropped users and groups?
From
Matthias Schmidt
Date:
Am 06.12.2004 um 23:27 schrieb Tom Lane: > schmidtm <schmidtm@mock-software.de> writes: >> Do I get that right: the only reason to do max(sysid) or a >> user-supplied ID in CreateUser() (commands/user.c) is that we don't >> have the ability to get sequences over the *.BKI/initdb mechanism? > > No, that's not quite the direction of the problem. The real reason > those facilities are there is so that you can deliberately create a > user > having a previously-used sysid. And the only reason why that is needed > is that we don't have dependency tracking for references to users and > groups. If you could be certain that there were no remaining > references > to a userid when it is dropped, there would be no need to be able to > resurrect it. And that would mean that we could forget the whole sysid > assignment mess and just use the regular OID generator to create unique > IDs for users and groups. > > Using a shared sequence instead of max(sysid) would be merely an > incremental improvement in the existing sysid assignment rules --- it > wouldn't eliminate the entire kluge at one blow. > > So if Alvaro's thing works out, the shared-sequence problem becomes > moot. > Probably that's a good reason not to spend time on it just yet. > > regards, tom lane > > makes totally sense to me. Let's wait for Alvaro's stuff. By the way: Do you have an idea about a small or medium sized task from the TODO-List for a newbee, which gets me up to speed?(!Win32) The TODO-List is a good starting point, but I cannot figure out who works on what. cheers, Matthias ---------------------------------------------------------------------- Matthias Schmidt Viehtriftstr. 49 67346 Speyer Tel.: +49 6232 4867 Fax.: +49 6232 640089
Re: somebody working on: Prevent default re-use of sysids for dropped users and groups?
From
Matthias Schmidt
Date:
Am 07.12.2004 um 19:24 schrieb Tom Lane: > Matthias Schmidt <schmidtm@mock-software.de> writes: >> By the way: Do you have an idea about a small or >> medium sized task from the TODO-List for a newbee, which gets me up to >> speed?(!Win32) > > A lot of the tasks listed under DATA TYPES are fairly self-contained > problems ... but what draws your interest? I think most of the tasks > that are on TODO are there either because they're hard problems, or > because nobody was very interested in them. Unfortunately they are not > marked as to which is which :-(. Make a list of things you find > interesting, and we can tell you if they're very hard or already > claimed. > > regards, tom lane > > Hi Tom, this is a list of things I'm interested in: 1) Allow administrators to safely terminate individual sessions Right now, SIGTERM will terminate a session, but it is treated as though the postmaster has paniced and shared memorymight not be cleaned up properly. A new signal is needed for safe termination. 2) Add a function that returns the 'uptime' of the postmaster 3) Have SHOW ALL and pg_settings show descriptions for server-side variables to be honest I'm only looking for a particular task to get things started. cheers, Matthias ---------------------------------------------------------------------- Matthias Schmidt Viehtriftstr. 49 67346 Speyer Tel.: +49 6232 4867 Fax.: +49 6232 640089
Re: somebody working on: Prevent default re-use of sysids for dropped users and groups?
From
Tom Lane
Date:
Matthias Schmidt <schmidtm@mock-software.de> writes:
> By the way: Do you have an idea about a small or
> medium sized task from the TODO-List for a newbee, which gets me up to
> speed?(!Win32)
A lot of the tasks listed under DATA TYPES are fairly self-contained
problems ... but what draws your interest? I think most of the tasks
that are on TODO are there either because they're hard problems, or
because nobody was very interested in them. Unfortunately they are not
marked as to which is which :-(. Make a list of things you find
interesting, and we can tell you if they're very hard or already claimed.
regards, tom lane
Re: somebody working on: Prevent default re-use of sysids for dropped users and groups?
From
Alvaro Herrera
Date:
On Mon, Dec 06, 2004 at 05:27:32PM -0500, Tom Lane wrote: Hi, > So if Alvaro's thing works out, the shared-sequence problem becomes moot. > Probably that's a good reason not to spend time on it just yet. I'm still alive, and I've been following this thread. Sorry for not giving an update. I have been working on the patch and it seems now it works as I originally intended it to: dropping users or tablespaces is disallowed unless they own or contain no object. ACL's are not tracked; I'm still unsure if they should be. It should not be a difficult modification though. Now, with the current setup there are 3000-and-some entries per database. This makes it by a wide margin the biggest shared catalog. Is this something to be concerned with? Adding ACL tracking won't make it any more bloated on a freshly initdb'd database, but it will grow much faster. I had thought that maybe we shouldn't track dependencies on the first superuser, on the assumption that it cannot be dropped. I'm not sure if this is entirely true however. I'll post the patch on monday or tuesday for review and testing. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente"
Re: somebody working on: Prevent default re-use of sysids for dropped users and groups?
From
Tom Lane
Date:
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> I have been working on the patch and it seems now it works as I
> originally intended it to: dropping users or tablespaces is disallowed
> unless they own or contain no object. ACL's are not tracked; I'm still
> unsure if they should be.
If we fail to track ACL references then it will still be possible to
drop users/groups for which there exist references in the database.
Which more or less defeats the point AFAICS.
regards, tom lane
Re: somebody working on: Prevent default re-use of sysids for dropped users and groups?
From
Peter Eisentraut
Date:
Alvaro Herrera wrote: > I had thought that maybe we shouldn't track dependencies on the first > superuser, on the assumption that it cannot be dropped. I'm not sure > if this is entirely true however. Well, there are certainly nontrivial provisions for recovering installations where it has been dropped. So why deprive users of that fun? -- Peter Eisentraut http://developer.postgresql.org/~petere/
Matthias Schmidt wrote: > > Am 07.12.2004 um 19:24 schrieb Tom Lane: > > > Matthias Schmidt <schmidtm@mock-software.de> writes: > >> By the way: Do you have an idea about a small or > >> medium sized task from the TODO-List for a newbee, which gets me up to > >> speed?(!Win32) > > > > A lot of the tasks listed under DATA TYPES are fairly self-contained > > problems ... but what draws your interest? I think most of the tasks > > that are on TODO are there either because they're hard problems, or > > because nobody was very interested in them. Unfortunately they are not > > marked as to which is which :-(. Make a list of things you find > > interesting, and we can tell you if they're very hard or already > > claimed. > > > > regards, tom lane > > > > > > Hi Tom, > > this is a list of things I'm interested in: > > 1) Allow administrators to safely terminate individual sessions > > Right now, SIGTERM will terminate a session, but it is treated as > though the postmaster has paniced and shared memory might not be > cleaned up properly. A new signal is needed for safe termination. This one seems hard. > 2) Add a function that returns the 'uptime' of the postmaster Seems like a good starting point. > 3) Have SHOW ALL and pg_settings show descriptions for server-side > variables Also a good one for you. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Re: somebody working on: Prevent default re-use of sysids for dropped users and groups?
From
Matthias Schmidt
Date:
Hi Tom, after beeing offline because of a chrashed box, I able to mail again. I would like to volunteer for the uptime() function. Is that OK? cheers, Matthias Am 13.12.2004 um 03:31 schrieb Bruce Momjian: > Matthias Schmidt wrote: >> >> Am 07.12.2004 um 19:24 schrieb Tom Lane: >> >>> Matthias Schmidt <schmidtm@mock-software.de> writes: >>>> By the way: Do you have an idea about a small or >>>> medium sized task from the TODO-List for a newbee, which gets me up >>>> to >>>> speed?(!Win32) >>> >>> A lot of the tasks listed under DATA TYPES are fairly self-contained >>> problems ... but what draws your interest? I think most of the tasks >>> that are on TODO are there either because they're hard problems, or >>> because nobody was very interested in them. Unfortunately they are >>> not >>> marked as to which is which :-(. Make a list of things you find >>> interesting, and we can tell you if they're very hard or already >>> claimed. >>> >>> regards, tom lane >>> >>> >> >> Hi Tom, >> >> this is a list of things I'm interested in: >> >> 1) Allow administrators to safely terminate individual sessions >> >> Right now, SIGTERM will terminate a session, but it is treated as >> though the postmaster has paniced and shared memory might not be >> cleaned up properly. A new signal is needed for safe termination. > > This one seems hard. > >> 2) Add a function that returns the 'uptime' of the postmaster > > Seems like a good starting point. > >> 3) Have SHOW ALL and pg_settings show descriptions for server-side >> variables > > Also a good one for you. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania > 19073 > > ---------------------------------------------------------------------- Matthias Schmidt Viehtriftstr. 49 67346 Speyer Tel.: +49 6232 4867 Fax.: +49 6232 640089
Matthias Schmidt wrote: > Hi Tom, > > after beeing offline because of a chrashed box, I able to mail again. > I would like to volunteer for the uptime() function. Is that OK? Sure. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073