somebody working on: Prevent default re-use of sysids for dropped users and groups?

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

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, Pennsylvania 19073

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, Pennsylvania 19073

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

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

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

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

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

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.

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

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"

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

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, Pennsylvania 19073

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, Pennsylvania 19073