Datensätze User zuordnen

[anon69225898]

Hi all, ich bin ein Postgresql-Neuling und möchte für ein OpenSource-Programm, das ich entwickle, Postgresql einsetzen (nachdem mir MySQL mittlerweile tierisch auf den Keks geht, seitdem ich mich mal intensiver mit SQL beschäftige).

Das Ganze wird ein Geocaching-Programm sein (damit User die gefundenen Caches, Photos dazu, etc. verwalten). Für einen einzigen User ist natürlich ein Datenbankserver Overkill, im “Single-User-Modus” wird das Programm (in Java) Derby verwenden, reicht ja völlig aus.

Ich möchte jedoch auch einen Modus machen, wo verschiedene User ihre Daten gemeinsam in einer Datenbank (hier: Postgresql) verwalten; daraus lassen sich dann auch wunderbar mit PHP , JSP/Servlets oder was auch immer Webseiten/Geoblogs erzeugen (nach dem Motto: “Ich habe den und den Cache dann und dann gefunden…”).

Damit sich die Leute nicht ins Gehege kommen, müssen nicht einzelne Tabellen an sich per User geschützt werden, sondern die Datensätze in den Tabellen selbst. Die Frage ist: wie mache ich das am besten? Meine Idee wäre den Datensätzen ein ownerOID zuzuordnen (und den Zugriff auf die Tabellen nur per views zuzulassen), ich habe aber keine Funktion gefunden, die die OID des current_users der aktuellen Verbindung zurückgibt. Den current_user als varchar() in der Tabelle zu speichern, halte ich für Platzverschwendung. Abgesehen davon scheint mir die OID eher intern zu sein.

Alternativ dachte ich mir eine Hilfstabelle einzurichten: userID als serial, username (der identisch mit current_user ist), und ownerID auf den Tabellen als Foreign Key auf die userID. Hat auch den Vorteil, dass bei Löschen des Users automatisch alle Datensätze, die dem User gehören, gelöscht werden (bei on delete cascade).

Gibt es einen anderen, eleganteren Weg?

Danke + Gruß,
Mark

[akretschmer]

@Carnosaurus

Damit sich die Leute nicht ins Gehege kommen, müssen nicht einzelne Tabellen an sich per User geschützt werden, sondern die Datensätze in den Tabellen selbst. Die Frage ist: wie mache ich das am besten? Meine Idee wäre den Datensätzen ein ownerOID zuzuordnen (und den Zugriff auf die Tabellen nur per views zuzulassen), ich habe aber keine Funktion gefunden, die die OID des current_users der aktuellen Verbindung zurückgibt. Den current_user als varchar() in der Tabelle zu speichern, halte ich für Platzverschwendung. Abgesehen davon scheint mir die OID eher intern zu sein.

test=*# select usesysid from pg_user where usename=current_user;
 usesysid
----------
    16711
(1 row)

Du hast aber keine Kontrolle darüber, wenn der User gelöscht wird, einen Trigger auf pg_user kannst Du nicht machen. Diese Tabelle pg_user ist auch nur ein View auf einen anderen View (pg_shadow, pg_authid), was letztendlich auf pg_authid verweist, welche aber keinen PK hat (nur eine OID).

Andreas

Andreas

[anon69225898]

@akretschmer

test=*# select usesysid from pg_user where usename=current_user;
 usesysid
----------
    16711
(1 row)

Klar - aber das ist eine zusätzliche select-Anweisung.

Du hast aber keine Kontrolle darüber, wenn der User gelöscht wird, einen Trigger auf pg_user kannst Du nicht machen. Diese Tabelle pg_user ist auch nur ein View auf einen anderen View (pg_shadow, pg_authid), was letztendlich auf pg_authid verweist, welche aber keinen PK hat (nur eine OID).

Gut - d.H. meine Idee mit der Hilfstabelle (die eh’ sinnvoll ist - da können ja Benutzerinfos gespeichert werden wie Nickname, URL, was auch immer) ist im Prinzip sinnvoll: primary key auf ein serial, die Datensätze foreign key darauf (mit on delete cascade), varchar auf den usename (und sinnvollerweise einen Index hier drauf)?

In dem Falle wäre es wahrscheinlich auch sinnvoll, eine oder mehrere Stored Procedures anzulegen: eine zum Anlegen eines neuen Users (neuen DB-User und gleichzeitig einen in die Hilfstabelle), und eine zum Löschen.

Danke erstmal für die Mühe,
Mark