View oder Tabelle je nach User

Hallo PG’ler

Ich möchte das wenn sich Leute aus der Rolle x anmelden diese die Tabelle Person sehen. Wenn sich Leute aus Y anmelden sollen sie statt Person die View PersonV sehen.

Kann ich sowas machen? Irgendwie Automatisch? mit Trigger? Script? Transaktion???

Wenn ja wie??

Vielen Dank im vorraus für die Eventuelle Antwort :wink:


Waldi

Gar nicht.

Du kannst über Zugriffsrechte regeln, wer auf welche Tabelle oder welchen View zugreifen darf. Aber das die Tabellen oder Views vorhanden sind, kannst du nicht verstecken.

Vielleicht versteh ich dich flasch.

Also, Du willst, daß User X was anderes als User Y sieht? Falls das so ist:

In der Tabelle speicherst Du noch ein Feld für den aktuellen User. Wer sich anmeldet, setzt damit auch die Variable current_user. Und dann machst Du einen VIEW auf die Tabelle mit der Bedingung, daß Deine Spalte = current_user ist. Dann sieht X was anderes als User Y.
Die originale Tabelle sollte natürlich nicht für alle abfragbar sein…

Andreas

Es geht bei mir in erster Linie Darum:

Ich habe in der Tabelle Person Daten gespeichert die manche User nicht sehen dürfen/sollen. Andere User dürfen das sehen. Klar jetzt kann ich eine View erstellen in der nur die Daten stehen die alle sehen dürfen.

Wie soll ich das aber Programiertechnisch lösen? Die Benutzer loggen sich in meinem Programm ein. Woher soll das Programm samt den ganzen SQL Abfragen wissen das wenn der und der User angemeldet ist die Abfragen auf die View abgesetzt werden und bei den anderen Usern auf die Tabelle?

Daher kommt meine Frage. Kann man das irgendwie anderst lösen, anderst wie ich gedacht habe?? Es wäre ja eigentlich schon damit getan wenn man so eine Regel erstellen könnte Wenn du in dieser Tabelle nichts siehst dann guck doch bei der Tabelle/View

@akretschmer: Ich steig da noch nicht so ganz durch. Was steht denn in der extra Tabellenspalte?? Und dann kann ich alle Abfragen auf die View machen und der eine User bekommt alles von der Tabelle zu sehen und der andere nur ein Teil? Ich kann mir das bei deiner Methode irgendwie nicht so vorstellen.

PG weiß, wer eingeloggt ist. Dieses Wissen kann man nutzen.

@akretschmer: Ich steig da noch nicht so ganz durch. Was steht denn in der extra Tabellenspalte?? Und dann kann ich alle Abfragen auf die View machen und der eine User bekommt alles von der Tabelle zu sehen und der andere nur ein Teil? Ich kann mir das bei deiner Methode irgendwie nicht so vorstellen.


test=# create table geheim (username text, info text);
CREATE TABLE
Time: 148.329 ms
test=*# insert into geheim values ('kretschmer', 'geheim1');
INSERT 0 1
Time: 5.369 ms
test=*# insert into geheim values ('foo', 'geheim2');
INSERT 0 1
Time: 0.864 ms
test=*# commit;
COMMIT
Time: 6.919 ms
test=# create view view_geheim as select * from geheim where username=current_user;
CREATE VIEW
Time: 150.246 ms
test=*# select * from view_geheim ;
  username  |  info
------------+---------
 kretschmer | geheim1
(1 row)

Time: 1.903 ms
test=*# commit;
COMMIT
Time: 47.951 ms
test=# \q
kretschmer@KanotixBox:~$ psql -U foo test
Timing is on.
Welcome to psql 8.1.2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

test=# select * from view_geheim ;
 username |  info
----------+---------
 foo      | geheim2
(1 row)

Andreas

Das ist wunderbar. Es gibt aber mehrere User soll ich dann mehrere Spalten anlegen??

Es geht bei mir nicht um zeilenweise beschränkung sondern nur um Spalten. Die einen User sollen alles sehen die anderen Nur Vor und Nachname.

PG weiß, wer eingeloggt ist. Dieses Wissen kann man nutzen.

Auf gut Deutsch ich muss in meinem Programm Schreiben. if (pguser == 1){select view} else {select tabelle};

Ich weis nicht gibts da nicht wirklich was anderes??


Sorry das ich mich anscheinend so undeutlich ausdrücke.

Mal eine ernsthafte Frage: hast du dich einmal ernsthaft mit den Zugriffsbeschränkungen, die PG bereitstellt, auseinandergesetzt?

Du lässt halt niemanden direkt auf die Tabelle sondern stellst einen oder mehrere Views bereit. Je nach erlaubten Rechten kann ein User auf einen oder mehrere Views zugreifen und sieht damit genau die Spalten, die du ihm zu sehen erlaubst.

Wenn das dein Problem nicht löst, verstehe ich wohl dein Problem nicht richtig.



test=# create table geheim (col1 int, col2 int, col3 int);
CREATE TABLE
test=*# create view view_geheim as select col1, case when current_user = 'erlaubt' then col2 else null end, col3 from geheim;
CREATE VIEW
test=*# insert into geheim values (1,2,3);
INSERT 0 1
test=*# select * from geheim ;
 col1 | col2 | col3
------+------+------
    1 |    2 |    3
(1 row)

test=*# select * from view_geheim ;
 col1 | case | col3
------+------+------
    1 |      |    3
(1 row)

mein Login matcht halt grad nicht auf ‘erlaubt’…


Andreas

Mal eine ernsthafte Frage: hast du dich einmal ernsthaft mit den Zugriffsbeschränkungen, die PG bereitstellt, auseinandergesetzt?

Ja das habe ich. Mir ist ja auch klar das ich Rechte auf eine View und Tabelle Legen kann. Das Híer ist mein eigentliche Problem:

Wie soll ich das aber Programiertechnisch lösen? Die Benutzer loggen sich in meinem Programm ein. Woher soll das Programm samt den ganzen SQL Abfragen wissen das wenn der und der User angemeldet ist die Abfragen auf die View abgesetzt werden und bei den anderen Usern auf die Tabelle?

Momentan ist es ja so das ich bei der einen Gruppe auf die View die Abfrage ausführen muss und bei den anderen auf die Tabelle. Das möchte ich halt verhindern, da dies ein sehr großer Implementierungsaufwand benötigen würde. Am einfachsten kann man sich die Sache so vorstellen: Ich mache Select * From Person. Bei der einen Gruppe bekomm ich nur den Vornamen und Namen zurück und bei den anderen eben alles.

@Akretschmer: Dankeschön erstmal. Ich werde das mal versuchen. Wenn ich die ganze Sache geschafft habe Poste ich die Lösung. Aber ich muss die Abfrage hier ja auch entweder auf die View ausführen oder auf die Tabelle und das ist s.o. :frowning:

Du kannst Dir auch eine SRF (Set Returning Function) schreiben, Du mußt eben NICHT mal auf table und mal auf view selecten oder so.

Andreas

Ich hätte noch zwei Fragen: Kann man denn nicht die Regel die du auf die View gelegt hast auch auf die Tabelle legen?? Ich hab das Versucht allerdings scheint er Case nicht zu kennen. Gibts hier ne alternative??

Bei der Regel wird doch immer der current_user() abgefragt. Gibt es hier auch sowas wie current_role()? Current_role() zumindest hat nicht funktioniert.

Das war ein VIEW. Ein VIEW ist an sich nur ein hinterlegtes SELECT. Bei einem SELECT foo FROM myview; wird dies dann auf das dem View myview zugrundeliegende Select angewendet. Ich vermute mal, Du hast mit dem Case nur einen Syntaxfehler gebaut

Bei der Regel wird doch immer der current_user() abgefragt. Gibt es hier auch sowas wie current_role()? Current_role() zumindest hat nicht funktioniert.

Naja, eine Rule ist mehr als ein User. Ein User kann z.B. mehreren Rules angehören. Insofern macht es wenig sinn, nach einer current_rule zu fragen. Vielleicht ist dies für Dich aber interessant:

Gruß aus Chemnitz vom PostgreSL-Stand:
http://ads.wars-nicht.de/pictures/v/events/chemnitzer_linuxtage_2007/

Andreas

Das war ein VIEW. Ein VIEW ist an sich nur ein hinterlegtes SELECT. Bei einem SELECT foo FROM myview; wird dies dann auf das dem View myview zugrundeliegende Select angewendet. Ich vermute mal, Du hast mit dem Case nur einen Syntaxfehler gebaut

Die Regel auf der View hat ja einwandfrei funktioniert. Ich wollte die Regel aber nicht auf die View sondern auf die Tabelle legen. Deshalb auch die Frage.

Naja, eine Rule ist mehr als ein User. Ein User kann z.B. mehreren Rules angehören. Insofern macht es wenig sinn, nach einer current_rule zu fragen. Vielleicht ist dies für Dich aber interessant:

Ich meinte nicht eine rUle sondern eine rOle. Eine Gruppe. Aber in PG heist das doch role oder bin ich falsch informiert??

Für mich wäre das schon interressant denn so könnte ich sagen das diese und diese Benutzer die in der Rolle xyz sind alles sehen dürfen. Der Rest darf nur die hälfte sehen. Ansonsten muss ich alle Benutzer angeben die alles sehen dürfen.

Ist es denn so furchtbar was ich verlange. Sowas kommt doch im harten Alltag auch vor das manche Benutzer nicht alle Daten einer Tabelle sehen dürfen. Wie machen denn die des??

Du kannst Dir auch eine SRF (Set Returning Function) schreiben, Du mußt eben NICHT mal auf table und mal auf view selecten oder so.

Aufgrund von dieser Aussage hab ich mich mal mit SRF beschäftigt und binn so weit gekommen. Ich weis nicht ob ich da total auf dem Holzweg bin.

CREATE OR REPLACE FUNCTION personentabelle() RETURNS SETOF date AS $$
        BEGIN
                IF      has_table_privilege(person, 'select') 
                THEN
                        SELECT * FROM person;
		ELSE
			SELECT * FROM view_geheim
                END IF;
        END;
$$ LANGUAGE 'plpgsql';

Er meckert hier das ein Syntax Error bei “END” sei. Ich muss weiterhin einen Return Typ definieren welcher Typ ist das wenn ich, wie in meinem fall eine Tabelle oder View anzeigen lassen will?? Vielleicht kann mir einer Helfen wenigstens ein bisschen auf die richtige Spur zu kommen. Kann man das denn überhaupt so machen? Oder ist das weniger geeignet??

Man kann es so machen, man muß es aber richtig machen. Eine SRF muß ‘return next’ aufrufen, und Du mußt in einer Schleife durch das Reultat ‘latschen’.

Ich geb Dir hier mal ein simples Beispiel:

test=*# select * from foo;
 id | a | b
----+---+---
  1 | 1 | 1
  2 | 1 | 2
  3 | 2 | 2
  4 | 2 | 3
  5 | 2 | 2
(5 rows)

Time: 0.429 ms
test=*# create or replace function rt_foo(OUT i int, OUT j int) returns setof record as $$
declare r record; 
begin 
  for r in select a,b from foo loop 
    i=r.a;
    j=r.b;
    return next; 
  end loop; 
end;
$$language plpgsql;
CREATE FUNCTION
Time: 1.027 ms
test=*# select * from rt_foo();
 i | j
---+---
 1 | 1
 1 | 2
 2 | 2
 2 | 3
 2 | 2
(5 rows)

Andreas

Wunderbar nurn och eine kleine Frage.

rt_foo(OUT i int, OUT j int)

Hier muss ich angeben wie viele Spalten die Rückgabe hat. Ich hab aber mal 2 und mal mehrere kann ich da nicht einfach r zurückgebenlassen?? Oder wie kann ich das sonst machen??

Dann bräuchtest Du mehrere Funktionen, je nach Anzahl der Rückgabewerte. Alternative: gib ein Array zurück (bei gleichen Typen).

Wobei: vielleicht geht es auch mit Record:
To return a composite (row) value, you must write a record or row variable as the expression.

Probier es aus!

Andreas

Also ich habe wie du schon gesagt hast mit 2 Funktionen gearbeitet. Habe die beiden erstellt und solo funktionieren die 1a.

Jetzt hängt es aber hier er meckert mir einen Syntaxfehler beim IF an. Nach dem Motto wieso weshalb warum… Ich frag warum…geht das nicht. Wenn ich den Befehl

SELECT has_table_privilege(person.*, 'select')

alleine ausführe bekomm ich t als Ergebnis.

CREATE OR REPLACE FUNCTION personentabelle()
	IF SELECT has_table_privilege(person.*, 'select')
	THEN
		EXECUTE 'SELECT * FROM getperson()'
	ELSE
		EXECUTE 'SELECT * FROM getpersonview()'
	END IF;
  LANGUAGE 'plpgsql' VOLATILE;

Tja, ist halt syntaktisch flasch. Weder eine Deklaration der Return-Werte noch ein begin, nicht mal ein AS $$ oder ein RETURN NEXT oder so. Ich als Parser würd mich da auch schütteln…

Davon abgesehen: Du willst jetzt ganz bestimmt noch mal in Ruhe die Beispiele von mir und die Doku lesen, stimmts? Daher sage ich nix weiter.

Btw.: der nächste Syntax-Error kommt wohl spätestens bei user.* im Aufruf von has_table_privilege().


Andreas

Liebster Akretschmer. Ich glaube momentan ist es an der Zeit dir einmal ein Dankeschön auszusprechen. Danke das du immer noch mitliest. Dankeschön für die Zeit die du schon geopfert hast und die Gehirnzellen die du für mich verbraten hast.

Es ligt in meiner Natur immer widersprechen zu müssen. Also jetzt auch hier.
Ich habe die Doku schon gewälzt und die Beispiele von dir angeschaut. In der Doku finde ich kein Beispiel das sich auf mich anwenden lässt. Ich finde kein Beispiel wo ein einfaches SELECT nach einer Bedingung ausgeführt wird.

Der grundlegende Unterschied von deinem Beispiel und meinem Problem ist doch das ich gar keine Rückgabe habe sondern nur einen Aufruf.

Mit BEGIN und END habe ich schon experimentiert, das Ergebnis sah so aus:

CREATE OR REPLACE FUNCTION personentabelle()
  RETURNS SETOF record AS
$BODY$
	DECLARE r record; 
        BEGIN
                IF      has_table_privilege('person', 'select') 
                THEN
                        SELECT * FROM getperson();
		ELSE
			SELECT * FROM getpersonview();
                END IF;
        END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

Dies lässt sich ausführen aber das Abfrageergebnis lautet: ERROR: a column definition list is required for functions returning “record”. Sorry da hört mein Latein auf. Ich würd ja mit Rückgaben arbeiten. Aber bitte sag mir doch mal einer was ich als Rückgabe für eine Abfrage angeben muss.

Wenn ich nun widerum durch die Abfrage latschen muss wie du schon oben beschrieben hast, dann ist das alles umsonst weil ich eine unterschiedliche Anzahl von Spalten habe. Und somit würden wir uns immer im Kreis bewegen.

Naja so ich hoffe das jetzt klar ist wo das Problem liegt. Ich komm auf jeden Fall nicht mehr weiter. Wenn du hier angekommen bist: Nochmal dankeschön