Postgres Datanbank Aufbau/Struktur mit Rollen/Rechtesystem

[chris2023]

Hallo zusammen,

ich benötige Unterstützung ... Ich bin kein Guru und bisher mit DB-Abfragen beschäftigt.
Seit 4-6 Wochen bin ich dabei eine Postgres Datenbank für mehrere Applikationen aufzubauen.

Das OS steht und funktioniert. Partitionen passend erstellt, SSH Key und Firewall alles eingerichtet, etc.
Seit 3 Wochen lese, teste und kämpfe ich mich der Einrichtung von Postgres
Meine erste Idee waren mehrer Databases auf einem Cluster. Hab ich dann verworfen und erstelle für jede App einen eigenen Cluster.
Ist mit ... pg_createcluster 16 test_cluster -p 7777... ganz schnell erledigt.

Ich bin dabei eine Vorlage/Musteraufbau für alle Cluster zu erstellen.
Würde ich default alles belassen, könnte ich direkt loslegen, ich will aber etwas mehr Sicherheit rein bringen ...

Nicht jede Applikation braucht alles, z.b. read-onyl etc...
Aber ich baue lieber eine Vorlage mit allen Funktionen und ergänze/reduziere diese passend.

Meine Idee...
- abgespeckter Superuser

und User über Gruppen-Rollen:
- DB-Admin_Gruppe
- Produktiv_Gruppe
- Develop_Gruppe
- read_only_Gruppe


Ich gehe mal "Schritt für Schritt" durch was/wie ich dies bisher benutzt habe ...

frisch aufgesetzter Cluster mit ... pg_createcluster 16 test_cluster -p 7777

psql -p 7777
ALTER USER postgres PASSWORD 'super123';

-- dies sollte mein abgespeckter superuser sein
CREATE ROLE dbadmin WITH CREATEDB CREATEROLE LOGIN PASSWORD 'dbadmin123';

-- wechsel zum abgespeckten Superuser
\c postgres dbadmin


-- erstelle Gruppen Rollen
CREATE ROLE admin_grp WITH NOLOGIN;
CREATE ROLE production_grp WITH NOLOGIN;
CREATE ROLE developer_grp WITH NOLOGIN;
CREATE ROLE read_only_grp WITH NOLOGIN;


-- erstelle User Rollen
CREATE ROLE admin1 WITH LOGIN PASSWORD 'admin1user123';
CREATE ROLE admin2 WITH LOGIN PASSWORD 'admin2user123';
CREATE ROLE app1 WITH LOGIN PASSWORD 'app1user123';
CREATE ROLE app2 WITH LOGIN PASSWORD 'app2user123';
CREATE ROLE dev1 WITH LOGIN PASSWORD 'dev1user123';
CREATE ROLE dev2 WITH LOGIN PASSWORD 'dev2user123';
CREATE ROLE readonly1 WITH LOGIN PASSWORD 'readonly1user123';
CREATE ROLE readonly2 WITH LOGIN PASSWORD 'readonly2user123';

... und jetzt kommt es zu problemen.

Ich würde die Rechtevergabe über Gruppen erledigen.
Die admin1, admin2 haben später die Möglichkeit Tabellen etc. zu erstellen. Dann wäre mal der eine, mal der andere der "Owner".

Gestern habe ich noch eine Funktion/Trigger gefunden, welches automatisch die Owner Rechte bei Erstellung einer Tabelle, der admin_grp geben würde.
Dies hat auch wunderbar funktioniert.
Heute Morgen habe ich noch eine einfachere Möglichkeit gefunden ...

ALTER ROLE admin1 SET ROLE admin_grp;
ALTER ROLE admin2 SET ROLE admin_grp;

... in meinem ersten Test hat dies auch wunderbar funktioniert.

Wenn ich es gerade probiere, kommt es aber zu einem >>> HINWEIS: Berechtigung fehlt, um Rolle »admin_grp« zu setzen <<<
Ich habe einiges probiert, irgendwie hab ich es hin bekommen. Wie weiß ich aber nicht wie ...
Mache ich aktuell ein "ALTER ROLE admin1 SET ROLE admin_grp;" wird dies direkt ohne Fehlermeldung mit "ALTER ROLE" bestätigt.

Bei der zweiten Gruppe "developer_grp" kommt es aber immer noch zu dieser Meldung. "HINWEIS: Berechtigung fehlt, um Rolle »developer_grp« zu setzen"


SELECT current_user, session_user;
current_user | session_user
--------------+--------------
dbadmin | dbadmin

An SET dbadmin; lag es nicht. Ich habe auch schon SET developer_grp; probiert etc.

Was mache ich gerade falsch? Ist es die Reihenfolge?
Als postgres würde es sich natürlich setzen lassen.
Fehlen "dbadmin" noch zusätzliche Rechte, oder mache ich in der Reihenfolge etwas falsch?


Viele Grüße
Chris

 

[dabadepdu]

Nicht sicher, ob ich das richtig verstehe, aber vielleicht möchtest Du gar nicht

alter role .. set role durchführen

sondern
grant role .. to

GRANT role_name [, ...] TO role_specification [, ...]
[ WITH { ADMIN | INHERIT | SET } { OPTION | TRUE | FALSE } ]
[ GRANTED BY role_specification ]

where role_specification can be:

[ GROUP ] role_name
| PUBLIC
| CURRENT_ROLE
| CURRENT_USER
| SESSION_USER

GRANT

GRANT GRANT — define access privileges Synopsis GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | …

www.postgresql.org

22.3. Role Membership

22.3. Role Membership # It is frequently convenient to group users together to ease management of privileges: that way, privileges can …

www.postgresql.org

 

[chris2023]

Hallo davadepdu,

ich musste jetzt eine ganze weile suchen, aber dann habe ich es wieder gefunden...

Postgres Credential Rotation · David Hollenberger

davidhollenberger.com

Im ersten Abschnitt wird dies wiedergegeben. Nach diesem bin ich vorgegangen.

Da habe ich aber gesehen, das "zuvor" die User Rollen mit den Gruppenrollen verbunden werden.
Also dein gemeintes ... GRANT ROLE xxx TO xxx;
Dies hatte ich übersehen.


Ich hab den Cluster resetet und habe nun vorher ...

-- verknüpfe User Rolle mit Gruppen Rolle
GRANT admin_grp TO admin1, admin2;
GRANT production_grp TO app1, app2;
GRANT developer_grp TO dev1, dev2;
GRANT read_only_grp TO readonly1, readonly2;

... eingefügt/ausgeführt.
Aber auch hier kommt es zu dem gleichen Fehler.

ALTER user admin1 SET ROLE = admin_grp;
> HINWEIS:  Berechtigung fehlt, um Rolle »admin_grp« zu setzen <

Gleich danach wäre das erstellen der Datenbank selbst gekommen ...
Da ist mir aufgefallen, dass es auch hier durch/wegen dem "einfachen Superuser" zu folgendem Fehler kommt.

CREATE DATABASE testdb OWNER admin_grp;
FEHLER:  Berechtigung nur für Rollen, die SET ROLE "admin_grp" ausführen können

In meinen vorherigen tests, habe ich dies jeweils mit dem Superuser "postgres" Account ausgeführt und da ist es nicht zu diesen Problemen gekommen.
Daraus schlussfolgere ich, dass ich mit dem erstellen des "dbadmin" irgendwo ein Fehler mache.

Wer mir da auf die Sprünge helfen kann, da wär ich sehr dankbar.

Nach kurzer Recerce ...

SET ROLE

The specified role_name must be a role that the current session user is a member of. (If the session user is a superuser, any role can be selected.)

Hier muss der Wurm begraben sein ...
Spätestens morgen setze ich mich diesem Thema wieder aktiv auseinander.

Viele Grüße
Chris

 

[chris2023]

Leider habe ich es in den 15min nicht mehr geschaft es vorher einzufügen ...

PS.
Leider schon im ersten Post vergessen zu erwähnen. Ich nutze die aktuelle Version 16.1.

Dazu habe ich grade auch über eine neue Funktion gelesen ...

createrole_self_grant

So wie ich dies lese und wenn ich nicht ganz falsch liege, ist dies die Lösung zu meinem Problem.

 

[dabadepdu]

Ok, also die Fehlermeldungen sind eindeutig, fehlende Rechte. Ich habe mir nicht die Mühe gemacht, nachzuvollziehen, an welcher Stelle Du was verlierst. Paar Gedanken dazu:
Wenn Du set role aufrufst, verlierst Du u.U. Rechte, die Du brauchst. Vielleicht nur eine Frage der Reihenfolge, was Du wann machst. Ähnlich wie bei der Definition von foreign keys. Eine einzelne Create Anweisung ist richtig, funktioniert aber nur, wenn das andere Objekt schon da ist.
Wenn Du als Superuser (z.B. postgres) alles komplett anlegst und die "fertige" DB dann einem andern Owner gibst und dann den Superuser raus nimmst, ist es vielleicht unkomplizierter.

... und jetzt kommt es zu problemen.

An der Stelle, wo es spannend wird, brichst Du ab.
Du solltest einfach die originalen Befehle und die Ausgaben posten.

 

[chris2023]

Hallo dabadepdu,

ja um aber eben nicht immer mal Superuser als Option rein zu nehmen, war mein Gedanke, dies mit dem abgespecktem User "dbadmin" zu erledigen.
Würde ich für dies und weitere kleinere Optionen, auch wenn nur temporär, jedesmal die Superuser Option brauchen, dann würde dieser abgespeckten User wenig Sinn machen.

An der Stelle, wo es spannend wird, brichst Du ab.
Du solltest einfach die originalen Befehle und die Ausgaben posten.

Weder habe ich abgebrochen, noch etwas weg gelassen. Dies ist im kompletten Auszug unten dann zu sehen...

ALTER ROLE
GRANT xxx_grp
CREATE DATABASE
... kommt in meiner Ausführung nacheinander.
Nur habe ich, als ich es gesehen habe, GRANT mit ALTER vertauscht und als test den nächsten Schritt CREATE probiert.
Wollte rausfinden, ob ich evtl. etwas früher an Rechten setzen/erstellen muss.
Aber alle Schritte und auch Ausgaben, hatte ich mitgegeben ...


Den "Fehler" habe ich gestern Abend noch gefunden.
Habe es bis jetzt gerade nochmals in Ruhe durchgespielt und es funktioniert.
"Mein" Fehler war (siehe Post drüber), für SET ROLE bedarf es folgendes ...

The specified role_name must be a role that the current session user is a member of. (If the session user is a superuser, any role can be selected.)

Auch wenn ich meinen "dbadmin" CREATE ROLE und CREATE DATABASE an Rechten gegeben habe, hat die Zugehörigkeit zu den neu erstellten Gruppen gefehlt, um ein "SET ROLE" auszuführen.


Schlussendlich war mein Fehler, das ich "dbadmin" im GRANT Abschnitt nicht mit angegeben habe.
Jetzt sieht es folgendermaßen aus ...

GRANT admin_grp TO dbadmin, admin1, admin2;

... und Funktioniert wie gewünscht.
Könnte bei "read_only_grp" wegfallen, aber wenn schon gleich komplett, das ich nicht später wieder über dieses Problem stolpere.

Durch das ...
ALTER ROLE admin1 SET ROLE admin_grp;
... sieht es nun folgendermaßen aus, wenn man sich als ein Admin User einloggt ...

\c testdb admin1
 
SELECT current_user, session_user;
current_user | session_user
--------------+--------------
 admin_grp    | admin1

Damit bekommen alle erstellten Objekte die eim admin erzeugt admin_grp als Owner zugewießen.
Zuvor war unter "current_user" der eingeloggte User eingetragen

Hier die Erklärung der Gruppen...

In meiem Beispiel habe ich 4 Gruppen.

>> admin_grp
haben in der Datenbank volle Rechte haben.


>> production_grp 
darüber wird die app eingebunden.
Aktuell KEINE CREATE, DELETE (nur selektiert) oder TRUNCATE Rechte.
Sollte mal die App außer kontrolle geraten, oder doch mal kompromitiert werden, kann damit feiner granuliert werden, wer was kann/darf.
In meinem fall schreibt die App nur fortlaufend Daten.
Damit werden alle Löschrechte, bis auf bewust gesetzte, entzogen. In meinem Fall könnte ich auch auf ein UPDATE verzichten.
Ist aktuell nur grob aufgebaut, lässt sich aber alles einstellen.


>> developer_grp
Ist für eine weitere Test DB gedacht.
Bis auf das anlegen der Gruppe selbst, bin ich noch nicht weiter gekommen.


>>read_only_grp
Hatte ich zwar bisher nie benötigt, für die aktuelle App perfekt geeignet.
Nur Rechte zum Lesen der Daten. Aktuell auf Alle Tabellen, kann man aber auf spezifizierte Tabellen begrenzen.

Und nun die komplette Vorlage (Schritt für Schritt) ...

pg_createcluster 16 testdb -p 7777  # Erzeuge einen neuen Cluster.
psql -p 7777  # logge mich darauf ein

-- Damit man mit psql einen besseren Überblick hat, auf welcher Datenbank und als welcher User, man sich gerade befindet/arbeitet.
\set PROMPT1 '%n@%m %~%R%# '

-- vergebe postgres ein passwort
ALTER USER postgres PASSWORD 'super123';

-- erstelle einen User ohne Superuser Rechten, für die meiste Arbeit am Cluster.
CREATE ROLE dbadmin WITH CREATEDB CREATEROLE LOGIN PASSWORD 'dbadmin123';


-- wechsel zu neuem Cluster-Admin
\c postgres dbadmin

-- erzeuge Gruppen Rollen
CREATE ROLE admin_grp WITH NOLOGIN;
CREATE ROLE production_grp WITH NOLOGIN;
CREATE ROLE developer_grp WITH NOLOGIN;
CREATE ROLE read_only_grp WITH NOLOGIN;


-- **** ???? **** Macht es Sinn NOINHERIT für alle User Rollen zu setzen? Eine vererbung duch User sollte es im aktuellem Beispiel nicht geben.
-- erzeuge User Rollen
CREATE ROLE admin1 WITH LOGIN PASSWORD 'admin1user123';
CREATE ROLE admin2 WITH LOGIN PASSWORD 'admin2user123';
CREATE ROLE app1 WITH LOGIN PASSWORD 'app1user123';
CREATE ROLE app2 WITH LOGIN PASSWORD 'app2user123';
CREATE ROLE dev1 WITH LOGIN PASSWORD 'dev1user123';
CREATE ROLE dev2 WITH LOGIN PASSWORD 'dev2user123';
CREATE ROLE readonly1 WITH LOGIN PASSWORD 'readonly1user123';
CREATE ROLE readonly2 WITH LOGIN PASSWORD 'readonly2user123';


-- verknüpfe User Rolle mit Gruppen Rolle
GRANT admin_grp TO dbadmin, admin1, admin2;
GRANT production_grp TO dbadmin, app1, app2;
GRANT developer_grp TO dbadmin, dev1, dev2;
GRANT read_only_grp TO dbadmin, readonly1, readonly2;


-- **** ???? **** Macht es evtl. an dieser Stelle Sinn, dies gleich mit allen Gruppen/User zu machen oder spricht etwas dagegen?
-- Gerade habe ich mich nur auf die beiden Gruppen beschränkt, welche auch CREATE Rechte besitzen.
ALTER ROLE admin1 SET ROLE admin_grp;
ALTER ROLE admin2 SET ROLE admin_grp;
ALTER ROLE dev1 SET ROLE developer_grp;
ALTER ROLE dev2 SET ROLE developer_grp;


-- erstelle Datenbank und weiße diesem gleich der "admin_grp" zu
CREATE DATABASE testdb OWNER admin_grp;


-- Wechsel auf Datenbank als "admin" User
-- Ohne das zuvor gesetzte ALTER ROLE wäre nach dem wechsel der Datenbank ich nun als admin1 unter "current_user".
-- Mit dem Setzen von ALTER ROLE ... wird jetzt automatisch der "current_user" auf "admin_grp", wenn man sich als ein Admin User anmeldet gesetzt.
-- siehe SELECT current_user, session_user;
\c testdb admin1


-- Bearbeite Public Schema
REVOKE ALL ON DATABASE testdb FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM public;


-- **** Hier entscheidet natürlich jeder für sich selbst, welche Rechte das System/User braucht.

-- \l
-- fügt allen Gruppen-Rollen die Basis Rechte CONNECT und TEMPORARY hinzu.
GRANT CONNECT, TEMPORARY ON DATABASE testdb TO admin_grp, production_grp, developer_grp, read_only_grp;


-- erstelle Schema
CREATE SCHEMA testdb AUTHORIZATION admin_grp;
SET search_path = testdb, "$user", public;


CREATE TABLE testdb.config_rw ( id int, aa varchar(255) );  -- tabelle config MIT löschrechten
CREATE TABLE testdb.data1_rw ( id int, aa varchar(255) );    -- tabelle data OHNE löschrechte
CREATE TABLE testdb.data2_ro ( id int, aa varchar(255) );    -- tabelle data MIT löschrechten



-- Regel für vorhandene Tabellen (admin_grp) ...
GRANT USAGE, CREATE ON SCHEMA testdb TO admin_grp;                        -- \dn+
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA "testdb" TO admin_grp;        -- \z
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA "testdb" TO admin_grp;



-- Regel für vorhandene Tabellen (weiteren Rollen) ...
GRANT USAGE ON SCHEMA testdb TO production_grp, read_only_grp;            -- \dn+

-- Basis Rechte für production_grp
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA "testdb" TO production_grp;  -- \z

-- zusätzlich DELTETE Rechte an beiden _rw Tabellen.
GRANT DELETE ON testdb.config_rw, testdb.data1_rw TO production_grp;

-- Rechte zum lesen(SELECT) auf alle Tabellen für die read_only_grp Gruppe
GRANT SELECT ON ALL TABLES IN SCHEMA "testdb" TO read_only_grp;        -- \z

-- Rechte für SEQUENCES
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA "testdb" TO production_grp, read_only_grp;
GRANT UPDATE ON ALL SEQUENCES IN SCHEMA "testdb" TO production_grp;



-- Regel für alle zukünftige TABLES\SEQUENCES  -- \ddp
ALTER DEFAULT PRIVILEGES FOR ROLE admin_grp IN SCHEMA "testdb" GRANT SELECT, INSERT, UPDATE ON TABLES TO production_grp;  -- OHNE , DELETE
ALTER DEFAULT PRIVILEGES FOR ROLE admin_grp IN SCHEMA "testdb" GRANT SELECT ON TABLES TO read_only_grp;
ALTER DEFAULT PRIVILEGES FOR ROLE admin_grp IN SCHEMA "testdb" GRANT SELECT, USAGE ON SEQUENCES TO production_grp, read_only_grp;
ALTER DEFAULT PRIVILEGES FOR ROLE admin_grp IN SCHEMA "testdb" GRANT UPDATE ON SEQUENCES TO production_grp;

Ich hoffe, damit kann mal irgendwer mal etwas anfangen.
Sicherlich nicht perfekt. Es stehen min. mir zwei noch offene Fragen aus.


Auch habe ich eine grundsätzliche Frage zu der Option ... ALTER ROLE admin1 SET ROLE admin_grp;
Mit welchem Befehl, bzw. wo kann man überhaupt sehen ob/wie diese rechte für einen/alle User überhaupt gesetzt sind?
Dies konnte ich auf die schnelle nicht ausmachen.

Jetzt freue ich mich sehr gerne über Feedback, Vorschläge, Kritik, Verbesserungen ...

Viele Grüße
Chris

 

[dabadepdu]

Weder habe ich abgebrochen, noch etwas weg gelassen.

Ok, ich versuche zu erkläre, was ich meine.

CREATE ROLE readonly2 WITH LOGIN PASSWORD 'readonly2user123';
... und jetzt kommt es zu problemen.[/CODE]

Ich würde die Rechtevergabe ...

Die admin1, admin2 haben später ...

Gestern habe ich noch eine Funktion/Trigger gefunden, ...
Dies hat auch wunderbar funktioniert.
Heute Morgen habe ich noch eine einfachere Möglichkeit gefunden ...

ALTER ROLE admin1 SET ROLE admin_grp;
ALTER ROLE admin2 SET ROLE admin_grp;

... in meinem ersten Test hat dies auch wunderbar funktioniert.

Wenn ich es gerade probiere, kommt es aber zu einem >>> HINWEIS: Berechtigung fehlt, um Rolle »admin_grp« zu setzen <<<

Diese Zitate zeigen, wie Du an der Stelle wo zu "Problemen kommt"
- Du Dich selbst unterbrichst,
- in den Konjunktiv wechselst
- von Triggerfunktionen schreibst, die wunderbar funktionieren
- noch einfachere Möglichkeiten findest, alter role .. set role ..
- die auch wunderbar funkionieren (gestern)
- plötzlich aber nicht mehr (heute)
- nun (erst) kommt tatsächlich ein Problem, die Fehlermeldung Berechtigung fehlt, um Rolle »admin_grp« zu setzen

Was tatsächlich geschehen ist, kann niemand nachvollziehen. Mehr als die Aussage der Fehlermeldung hinzunehmen und in Konsequenz das fehlende Recht zu setzen, ist dann kaum möglich.

Was man normaleweise macht, um sein Problem zu zeigen und es auch nachvollziehbar zu machen, eine simple Auflistung der Befehle und Antworten des Servers zu posten, also psql starten und los:

C:\Users\user>psql -p 5438
Passwort für Benutzer user:
psql (16.0)
Geben Sie »help« für Hilfe ein.

user=> create role readonly2 with login password 'readonly2user123';
CREATE ROLE
user=> create role readonly2 with login password 'readonly2user123';
FEHLER:  Rolle »readonly2« existiert bereits
user=> create role admin2 with login password 'adminuser123';
CREATE ROLE
user=> alter role admin2 set admin_grp;
FEHLER:  Syntaxfehler bei »;«
ZEILE 1: alter role admin2 set admin_grp;
                                        ^
user=> create role admin_grp with login;
CREATE ROLE
user=> alter role admin2 set admin_grp;
FEHLER:  Syntaxfehler bei »;«
ZEILE 1: alter role admin2 set admin_grp;
                                        ^
user=> alter role admin2 set role admin_grp;
HINWEIS:  Berechtigung fehlt, um Rolle »admin_grp« zu setzen
ALTER ROLE
user=>

Was Du hier siehst, sind ein paar Schnipsel Deines Codes, so wie ich sie bei mir laufen lassen habe, mit der jeweiligen Antwort des Servers. Das kann jeder nachvollziehen, 1:1

Das einzige Geheimnis an meinem SQL Listing ist wahrscheinlich, welche Privileges mein DB User hat.

Mit welchem Befehl, bzw. wo kann man überhaupt sehen ob/wie diese rechte für einen/alle User überhaupt gesetzt sind?

Es gibt glaub ich nicht so richtig eine einzige Stelle dafür.

Zunächst musst Du zwischen Owner und anderen Usern / Rollen unterscheiden. Ein Owner hat automatisch alle Rechte am Objekt. (Daher auch mein Vorschlag, erst alle Objekte zu erzeugen, dann Privileges verteilen und Ownership vergeben / abgeben.)

select 
    nsp.nspname as SchemaName,
    cls.relname as ObjectName, 
    rol.rolname as ObjectOwner,
    case cls.relkind
        when 'r' then 'TABLE'
        when 'm' then 'MATERIALIZED_VIEW'
        when 'i' then 'INDEX'
        when 'S' then 'SEQUENCE'
        when 'v' then 'VIEW'
        when 'c' then 'TYPE'
        else cls.relkind::text
    end as ObjectType
  from pg_class cls
  join pg_roles rol 
    on rol.oid = cls.relowner
  join pg_namespace nsp 
    on nsp.oid = cls.relnamespace
 where nsp.nspname not in ('information_schema', 'pg_catalog')
   and nsp.nspname not like 'pg_toast%'
   and rol.rolname = '<user or role name here'  
 order by nsp.nspname, cls.relname;

Die vergebenen Rollen-Rechte jenseits von Ownership findet man in den System Catalogs

Chapter 53. System Catalogs

Chapter 53. System Catalogs Table of Contents 53.1. Overview 53.2. pg_aggregate 53.3. pg_am 53.4. pg_amop 53.5. pg_amproc 53.6. pg_attrdef 53.7. pg_attribute 53.8. …

www.postgresql.org

Hier ist ein Ansatz um Objektrechte auszulesen:

select oid::regclass,
       (aclexplode(relacl)).grantor,
       (aclexplode(relacl)).grantee,
       (aclexplode(relacl)).privilege_type,
       (aclexplode(relacl)).is_grantable
from pg_class
where relacl is not null;

Was effektiv gerade an Berechtigungen gilt, hängt natürlich von der akut zugewiesenen Rolle ab.

 

[chris2023]

Hi dabadepdu,

...
Was tatsächlich geschehen ist, kann niemand nachvollziehen. Mehr als die Aussage der Fehlermeldung hinzunehmen und in Konsequenz das fehlende Recht zu setzen, ist dann kaum möglich.

Ja, das war mein Fehler das ich "... und jetzt kommt es zu problemen." reingeschrieben habe, ohne den Fehler an dieser Stelle zu belassen.
Ein paar Zeilen unter dem Code Block habe ich aber den Fehler 1:1 wiedergegeben...
>> Wenn ich es gerade probiere, kommt es zu einem >>> HINWEIS: Berechtigung fehlt, um Rolle »admin_grp« zu setzen <<<

Ja, im Nachhinein sicherlich nicht optimal, aber verschwiegen habe ich nichts.
Außer die „normalen“ Return Werte wie, „CREATE ROLE, ALTER ROLE, …“ weggelassen.

Mit dem "gestern" aus meinem ersten Post, war eine Test-DB mit der ich zuvor einiges ausprobiert habe. In diesem Cluster habe auch beide Möglichkeiten, über eine Funktion mit Trigger wie auch über das SET ROLE funktioniert.
In diesem Versuchsaufbau habe ich alles mit dem postgres Superuser gemacht/eingerichtet.

Um eben auf nummer sicher zu gehen, probiere ich neue Sachen immer mit einem frischen Cluster aus.
Erst dabei sind die Probleme aufgetaucht. Bzw. lag es schlußendlich daran, das ich in meinen ersten Versuchen (Trigger/ letztens funktionierte es, jetzt aber nicht) ich die Einrichtung mit dem default postgres (Superuser) gemacht hatte und als ich mein fertiges ergebnis mit einem frisch aufgesetztem Cluster angefangen habe, ich direkt dazu übergegangen war, einen abgespeckten "Superuser" zu nutzen.
Auch hier sagte ich ja, "führe ich dies als Superuser aus, lässt sich dies ohne Probleme setzen".

Was tatsächlich geschehen ist, kann niemand nachvollziehen. Mehr als die Aussage der Fehlermeldung hinzunehmen und in Konsequenz das fehlende Recht zu setzen, ist dann kaum möglich.

Ja, ich hatte etwas mehr geschrieben was ich so alles probiert hatte. Aber jeder hätte es nachvollziehen können.

Darum sagte ich auch immer, ich fange mit einem frischen Cluster an und an der Stelle habe ich nur die jeweils hier veröffentlichten Zeilen benutzt. Darum hätte dies auch jeder selbst nachvollziehen können.
Mehr als die 15 Befehle (davon die Hälfte nur User angelegt) aus dem ersten Posting, habe ich nicht eingegeben, bis auf das der Befehl Nr. 16 zu dem Fehler geführt hatte.
Also hätte es jeder nachvollziehen können.
Aber ja, ich hätte nicht so viel schreiben sollen was ich zuvor gemacht habe und auch die Ausgabe (direkt der Fehler) hätte besser seien können.
Ich hab es verstanden.

user=> alter role admin2 set admin_grp;
FEHLER: Syntaxfehler bei »;«
ZEILE 1: alter role admin2 set admin_grp;

Ich hoffe dies war absichtlich, zur verdeutlichung, falsch angegeben? Den so habe ich es nie geschrieben. Da fehlt das ROLE nach dem SET.
Falls dies für mich zur veranschaulichung war, was es für Return Werte geben kann, verstanden.

user=> alter role admin2 set role admin_grp;
HINWEIS: Berechtigung fehlt, um Rolle »admin_grp« zu setzen
ALTER ROLE
user=>

Ja genau, diesen Fehler gab es im ersten Post, nur das ich den "Hinweiß" nicht im Code Teil belassen hatte und an der Stelle "... und jetzt kommt es zu problemen." geschrieben hatte. Aber das schrieb ich oben, mein Fehler.
Und zu diesem Problem hatte ich auf Hilfe gehoft.


Und hier die Lösung. Dies habe ich auch in meinem letzten Post erwähnt. Danach ging alles ohne Probleme durch.

Schlussendlich war mein Fehler, das ich "dbadmin" im GRANT Abschnitt nicht mit angegeben habe.
Jetzt sieht es folgendermaßen aus ...

Code:
GRANT admin_grp TO dbadmin, admin1, admin2;

Es gibt glaub ich nicht so richtig eine einzige Stelle dafür.

Hier habe ich mich auch nur auf das "ALTER ROLE xxx SET ROLE xxx" bezogen.
Und ich habe eine Funktion gefunden.
\drds ... oder auch ... select * from pg_user;
Zeigt an, ob, bzw. welche useconfig gesetzt worden sind.

Zunächst musst Du zwischen Owner und anderen Usern / Rollen unterscheiden. Ein Owner hat automatisch alle Rechte am Objekt. (Daher auch mein Vorschlag, erst alle Objekte zu erzeugen, dann Privileges verteilen und Ownership vergeben / abgeben.)

Dies habe ich in meinem letzten Post, wo ich alles Final Schnitt für Schritt wiedergegeben haben, auch berücksichtigt.
Also erst die Grund Settings vorgenommen, dann die Objekte erzeugt und erst dann gehe ich an die Vergabe der Privilegien.


Ja, im ersten Post war nicht alles optimal.
Es ist ja nicht so, dass ich hier alles vorgekaut bekommen möchte.
Habe den Fehler schlussendlich selbst ausfindig machen können und am Schluss was Fertiges präsentiert.
Schade das zu dem Ergebnis kein Feedback gekommen ist. Das wäre interessanter gewesen.

Eine Verbesserung habe ich auch schon, und zwar den dbadmin nicht als User anzulegen, sondern wie alles andere auch als zentralle/Gruppen Rolle. Wenn schon alles so aufgebaut ist, dann auch gleich diese Stelle.

Viele Grüße
Chris