Hackers,

I attach a patch to implement dependencies on shared objects.
As some of you may remember, the purpose of this patch is to record
dependencies on shared objects, such as roles and tablespaces, from
regular database objects. This is done on a new shared system catalog
called pg_shdepend, so that when a backend wants to drop any shared
object, it can easily verify whether it is referenced in other database.

Since the last patch I posted, I changed the following:

- updated for roles
- added a dependency type. There are three types: PIN, same as normal
dependencies; OWNER, for roles that own objects; NORMAL, all the rest
(roles in the Acl and tablespaces).
I needed to separate the OWNER entries to support changing ownership
of objects without having to poke the whole Acl for the object.
- several bugs fixed.


This patch adds pg_shdepend to the "Catalogs" chapter in the
documentation. Also, there is a new, very naive, regression test.

Regression tests pass.

This patch creates the following files:

src/backend/catalog/pg_shdepend.c
src/include/catalog/pg_shdepend.h
src/test/regress/expected/dependency.out
src/test/regress/sql/dependency.sql


Please consider this patch for inclusion into 8.1. With this patch on,
it's finally possible to remove the WITH SYSID clause to the CREATE
USER, GROUP, ROLE commands.


I'm still owing a patch to implement a DROP OWNED command, to drop the
objects owned by a given role; and maybe a REASSIGN OWNED, to "give" the
objects to another role. Please comment on those -- I think they are
required or at least useful. Those are fairly trivial patches, and I
have half of them written already.

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"¿Qué importan los años? Lo que realmente importa es comprobar que
a fin de cuentas la mejor edad de la vida es estar vivo" (Mafalda)


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Alvaro Herrera <alvherre@surnet.cl> writes:
> I attach a patch to implement dependencies on shared objects.
> As some of you may remember, the purpose of this patch is to record
> dependencies on shared objects, such as roles and tablespaces, from
> regular database objects. This is done on a new shared system catalog
> called pg_shdepend, so that when a backend wants to drop any shared
> object, it can easily verify whether it is referenced in other database.

Will work on applying this next.

> - added a dependency type. There are three types: PIN, same as normal
> dependencies; OWNER, for roles that own objects; NORMAL, all the rest
> (roles in the Acl and tablespaces).
> I needed to separate the OWNER entries to support changing ownership
> of objects without having to poke the whole Acl for the object.

Although I don't have any particular objection to the OWNER/NORMAL
distinction, your explanation doesn't seem to make sense. Don't you
have to poke the Acl anyway, if it's non-null? Else the grantor values
will be wrong.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Tue, Jul 05, 2005 at 02:47:15PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@surnet.cl> writes:
> > I attach a patch to implement dependencies on shared objects.
> > As some of you may remember, the purpose of this patch is to record
> > dependencies on shared objects, such as roles and tablespaces, from
> > regular database objects. This is done on a new shared system catalog
> > called pg_shdepend, so that when a backend wants to drop any shared
> > object, it can easily verify whether it is referenced in other database.
>
> Will work on applying this next.

Cool.

> > - added a dependency type. There are three types: PIN, same as normal
> > dependencies; OWNER, for roles that own objects; NORMAL, all the rest
> > (roles in the Acl and tablespaces).
> > I needed to separate the OWNER entries to support changing ownership
> > of objects without having to poke the whole Acl for the object.
>
> Although I don't have any particular objection to the OWNER/NORMAL
> distinction, your explanation doesn't seem to make sense. Don't you
> have to poke the Acl anyway, if it's non-null? Else the grantor values
> will be wrong.

Hum, we don't register a dependency on the owner when registering
dependencies from the Acl -- we actively skip that (because we know the
owner has an entry of the other type already). Is this still an issue?

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"La verdad no siempre es bonita, pero el hambre de ella sí"

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On Tue, Jul 05, 2005 at 02:47:15PM -0400, Tom Lane wrote:
>> Although I don't have any particular objection to the OWNER/NORMAL
>> distinction, your explanation doesn't seem to make sense. Don't you
>> have to poke the Acl anyway, if it's non-null? Else the grantor values
>> will be wrong.

> Hum, we don't register a dependency on the owner when registering
> dependencies from the Acl -- we actively skip that (because we know the
> owner has an entry of the other type already). Is this still an issue?

Not sure. ISTM the distinction we want to capture is more along the
lines of DEPENDENCY_NORMAL vs DEPENDENCY_AUTO --- that is, we should be
willing to auto-drop grants to a user when dropping the user, but not be
willing to auto-drop objects unless the drop is with CASCADE. Also,
grants from the user to someone else (using grant options) probably
shouldn't go away without CASCADE either, though this is maybe
debatable. If you believe the latter then the OWNER/ACL division is
clearly the wrong way to think about it. So I'd be inclined to use
the NORMAL/AUTO terminology instead.

On the other hand: we might need to be able to express that "this
object's ACL depends on that user" as opposed to "this object depends on
that user" --- if you're really using the dependency type as a flag of
this kind, then we might have to keep it. I've not gotten that far
in reading the patch yet.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Another question about this: why bother with dependencies on
tablespaces? That seems to me to be isomorphic with dependencies on
databases --- we don't need those either, because in both cases we
count on the filesystem to provide ground truth about which objects
live inside a database/tablespace.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Tue, Jul 05, 2005 at 04:32:22PM -0400, Tom Lane wrote:
> Another question about this: why bother with dependencies on
> tablespaces? That seems to me to be isomorphic with dependencies on
> databases --- we don't need those either, because in both cases we
> count on the filesystem to provide ground truth about which objects
> live inside a database/tablespace.

Because it appeared ugly to me to require information from the
filesystem for something that should be known internally in the
database (system catalogs). No other reason that I remember.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"You knock on that door or the sun will be shining on places inside you
that the sun doesn't usually shine" (en Death: "The High Cost of Living")

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Tue, Jul 05, 2005 at 03:51:32PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > On Tue, Jul 05, 2005 at 02:47:15PM -0400, Tom Lane wrote:
> >> Although I don't have any particular objection to the OWNER/NORMAL
> >> distinction, your explanation doesn't seem to make sense. Don't you
> >> have to poke the Acl anyway, if it's non-null? Else the grantor values
> >> will be wrong.
>
> > Hum, we don't register a dependency on the owner when registering
> > dependencies from the Acl -- we actively skip that (because we know the
> > owner has an entry of the other type already). Is this still an issue?
>
> Not sure. ISTM the distinction we want to capture is more along the
> lines of DEPENDENCY_NORMAL vs DEPENDENCY_AUTO --- that is, we should be
> willing to auto-drop grants to a user when dropping the user, but not be
> willing to auto-drop objects unless the drop is with CASCADE. Also,
> grants from the user to someone else (using grant options) probably
> shouldn't go away without CASCADE either, though this is maybe
> debatable. If you believe the latter then the OWNER/ACL division is
> clearly the wrong way to think about it. So I'd be inclined to use
> the NORMAL/AUTO terminology instead.

I'm not sure about DROP USER CASCADE. There's an inherent asymmetry in
that command, because as soon as the user has dependencies in some other
database, you cannot do anything, and the cascade will fail. So I'd
rather not offer a DROP USER CASCADE, but just reject the command when
there are any dependencies, and provide a command to drop the owned
objects (DROP OWNED), or to give them to someone else (REASSIGN OWNED --
better wording?).

An important distinction to be made is which dependencies are "auto" and
which ones are "normal". It's clear that OWNER entries cannot be AUTO.
OTOH grantee entries in the ACL can be (have to be) dropped
automatically, but what to do about grantor ones? We may have to do a
distinction. We could drop the grants for which the dropped user is the
grantor; but not automatically, I think.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Postgres is bloatware by design: it was built to house
PhD theses." (Joey Hellerstein, SIGMOD annual conference 2002)

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On Tue, Jul 05, 2005 at 03:51:32PM -0400, Tom Lane wrote:
> An important distinction to be made is which dependencies are "auto" and
> which ones are "normal". It's clear that OWNER entries cannot be AUTO.
> OTOH grantee entries in the ACL can be (have to be) dropped
> automatically, but what to do about grantor ones? We may have to do a
> distinction. We could drop the grants for which the dropped user is the
> grantor; but not automatically, I think.

For the moment I left this alone; you can revisit the question when
you do DROP OWNED or whatever we end up calling it.

I've committed the patch with some revisions --- principally that I took
out tracking of dependencies on tablespaces, as I couldn't see any real
argument that we need to track that separately from the filesystem
structure. (I was also a bit concerned about whether copying a database
to a different default tablespace would work correctly.)

I ended up calling the dependency types OWNER and ACL; there is no
NORMAL at the moment. Obviously we'd need to add more dependency types
to track dependencies on anything but roles. The reason for this is
that getting the ALTER OWNER case right requires removing ACL dependency
entries for the new owner, and I didn't like the idea of issuing a
blanket removal except against a very specifically defined dependency
type. Also, I figured out why you needed the separate OWNER dependency
type: it's so you can tell which entry to update during ALTER OWNER.
This is hopefully a bit better documented now.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org