Hallo,
ich bin Anfänger in Sachen PostgreSQL: Eine neue Datenbank “MeineDB” (Owner=Meister) wurde angelegt mit Schemen “SandBox” und “Produktion”. Ich habe zwei Rollen / User angelegt mit:
createuser -a -d -P "Meister"
createuser -P "Geselle"
Wie muss ich nun die Rechte vergeben, so dass
Meister in Sandbox alles Lesen, Schreiben, Löschen, Neu Anlegen
und
Geselle in Sandbox alles Lesen und Schreiben
kann? Ich habe viel gelesen und probiert, aktuell kann Geselle in eine Tabelle nur dann schreiben, wenn kein Autowert-Feld vorhanden ist; deshalb die Bitte um ein “Kochrezept”.
Danke, Michael
--
-- in der pg_hba.conf
--
local demo +demo_mitarbeiter md5
--
-- in einer beliebigen DB Reload der Config
--
test=*# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 Zeile)
--
-- weiter, Anlegen der Nutzer
--
test=*# create role demo_mitarbeiter with login;
CREATE ROLE
test=*# create role demo_ddl in role demo_mitarbeiter;
CREATE ROLE
test=*# create role demo_rw in role demo_mitarbeiter;
CREATE ROLE
test=*# create role demo_read_only in role demo_mitarbeiter;
CREATE ROLE
--
-- anlegen der DB
--
test=# create database demo;
CREATE DATABASE
test=*# grant connect on database demo to demo_mitarbeiter;
GRANT
--
-- alle Rechte wegnehmen
--
test=# revoke all on database demo from public;
REVOKE
--
-- ich verbinde mich als Superuser zur neuen DB
--
test=# \c demo
psql (9.6.2, Server 9.5.6)
Sie sind jetzt verbunden mit der Datenbank »demo« als Benutzer »akretschmer«.
--
-- vom public-schema alles entfernen
--
demo=*# revoke all on schema public from public;
REVOKE
--
-- ich erzeuge ein neues Schema
--
demo=# create schema demo_schema;
CREATE SCHEMA
--
-- alle Mitarbeiter haben Usage-Rechte
--
demo=*# grant usage on schema demo_schema to demo_mitarbeiter;
GRANT
--
-- demo_ddl darf neue Dinge erstellen
--
demo=*# grant create on schema demo_schema to demo_ddl;
GRANT
--
-- Default-Rechte für von demo_ddl erstellte Objekte setzen
--
demo=*# alter default privileges for role demo_ddl grant select on tables to demo_read_only;
ALTER DEFAULT PRIVILEGES
demo=*# alter default privileges for role demo_ddl grant select on tables to demo_rw;
ALTER DEFAULT PRIVILEGES
demo=*# alter default privileges for role demo_ddl grant select on sequences to demo_read_only;
ALTER DEFAULT PRIVILEGES
demo=*# alter default privileges for role demo_ddl grant select on sequences to demo_rw;
ALTER DEFAULT PRIVILEGES
demo=*# alter default privileges for role demo_ddl grant insert, update, delete on tables to demo_rw;
ALTER DEFAULT PRIVILEGES
demo=*# alter default privileges for role demo_ddl grant usage, update on sequences to demo_rw;
ALTER DEFAULT PRIVILEGES
test=# create user admin1 in role demo_ddl password 'admin1';
CREATE ROLE
test=# revoke create on database demo from admin1;
REVOKE
test=# create user rw_user1 in role demo_rw password 'rw_user1';
CREATE ROLE
test=# create user ro_user1 in role demo_read_only password 'rw_user1';
CREATE ROLE
--
-- nun ein paar tests
--
-- als demo_ddl Tabelle anlegen
test=# \c demo demo_ddl
Passwort für Benutzer demo_ddl:
psql (9.6.2, Server 9.5.6)
Sie sind jetzt verbunden mit der Datenbank »demo« als Benutzer »demo_ddl«.
demo=> \dn
Liste der Schemas
Name | Eigentümer
-------------+-------------
demo_schema | akretschmer
public | postgres
(2 Zeilen)
demo=> create table demo_schema.foo (id serial primary key, name text);
CREATE TABLE
demo=*> commit;
COMMIT
demo=> insert into demo_schema.foo values (default, 'Andreas');
INSERT 0 1
demo=*> commit;
COMMIT
-- als demo_rw auch was in die Tabelle schreiben
demo=> \c - demo_rw
Passwort für Benutzer demo_rw:
psql (9.6.2, Server 9.5.6)
Sie sind jetzt verbunden mit der Datenbank »demo« als Benutzer »demo_rw«.
demo=> insert into demo_schema.foo values (default, 'Tamara');
INSERT 0 1
demo=*> commit;
COMMIT
-- als demo_read_only lesen und schreiben
demo=> \c - demo_read_only
Passwort für Benutzer demo_read_only:
psql (9.6.2, Server 9.5.6)
Sie sind jetzt verbunden mit der Datenbank »demo« als Benutzer »demo_read_only«.
demo=> select * from demo_schema.foo;
id | name
----+---------
1 | Andreas
2 | Tamara
(2 Zeilen)
demo=*> insert into demo_schema.foo values (default, 'Max');
FEHLER: keine Berechtigung für Relation foo
demo=*>
=======================================
Also, du solltest da mal nach den Begriffen googeln, man findet da schon einiges, z.B.:
Herzlichen Dank für die Links und besonders die Code-Anleitung; ich bin weiter voran gekommen.
Mein Problem gestern war das AutoWert-Feld und der Zugriff auf CurrVal und NextVal Funktionen; das habe ich mit nach dieser Anleitung in den Griff bekommen mit
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA "SchemaName" TO "BenutzerName";
Die Lese-, Schreib- und Löschzugriffe funktionieren nach Studium Deines Codes für die Benutzer wie definiert.
Dann wollte ich die Rechte für neu angelegte Tabellen vordefinieren:
ALTER DEFAULT PRIVILEGES IN SCHEMA "SchemaName" GRANT ALL ON TABLES TO "BenutzerName";
ALTER DEFAULT PRIVILEGES IN SCHEMA "SchemaName" GRANT USAGE ON SEQUENCES TO "BenutzerName";
Aber bereits beim Zugriffsversuch auf eine neue Tabelle (ohne AutoWert-Feld) erhalte ich wieder den Fehler "SQL-Fehler [42501]: ERROR: permission denied for relation newtable".
Danke! Hoffentlich habe ich jetzt nichts übersehen, der Block nach der Leerzeile ist exakt aus meiner Mitschrift:
$ createuser -a -d -P meister
$ createuser -P "meisterLO"
postgres=# CREATE DATABASE "DatenbankName"; -- meister ist Owner
postgres=# \c "DatenbankName"
DatenbankName=# CREATE SCHEMA "DatenbankName";
DatenbankName=# GRANT CONNECT ON DATABASE "DatenbankName" TO "meisterLO";
DatenbankName=# GRANT USAGE ON SCHEMA "SchemaName" TO "meisterLO";
DatenbankName=# GRANT ALL ON ALL TABLES IN SCHEMA "SchemaName" TO "meisterLO";
DatenbankName=# ALTER DEFAULT PRIVILEGES IN SCHEMA "SchemaName" GRANT ALL ON TABLES TO "meisterLO";
DatenbankName=# ALTER DEFAULT PRIVILEGES IN SCHEMA "SchemaName" GRANT USAGE ON SEQUENCES TO "meisterLO";
DatenbankName=# GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA "SchemaName" TO "meisterLO";