Benutzer- und Rechte-Verwaltung

.. |b| image:: ./werbung/kunst-am-bau-stopper.jpg

.. |a| raw:: html

       <div class='hover_img'>
     <a href='#'>Stopper
     <span>
       <img src='./../../../_images/kunst-am-bau-stopper.jpg'
            alt='kunst-am-bau-stopper' />
      </span>
     </a>
    </div>

.. sidebar:: SQL-Kurs

         | Statt der Werbung...
         | |a|
         | Serie: Kunst am Bau

:ref:`« Übersicht: Manage PostgreSQL <managepostgres>`

Benutzer und Rechte zeigen

\\du

Angaben zum aktuellen Benutzer

select user;
-- Alternative Abfragen
-- select current_user;
-- select session_user;
-- select getpgusername();


-- Benuternnamen
-- select usename, passwd from pg_shadow;
select rolname, rolpassword from pg_authid;

Benutzer anlegen

Es gibt für gewöhnlich zwei Nutzergruppen:

  • Superuser (Administratoren)

  • und normale Benutzter.

CREATE USER peter WITH PASSWORD 'myPassword';

Login temporär verweigern

  • kann auch wieder gewährt werden.

ALTER USER peter NOLOGIN;
ALTER USER peter LOGIN;

Nutzer und Rechte weitergeben

Dazu müssen die Rechte an eine andere/n Rolle/Benutzer weitergegeben werden. Dies kann in zwei Schritten geschehen:

ALTER USER praktikant NOLOGIN;
GRANT praktikant TO anderernutzer;

-- eine erweiterte Weitergabe erfolgt mit

REASSIGN OWNED BY praktikant TO anderernutzer;

Superuser anlegen

  • Recht entziehen

  • Recht wieder zuweisen

CREATE USER peter SUPERUSER;
ALTER USER peter NOSUPERUSER;
ALTER USER peter SUPERUSER;

Welche Rechte existieren für eine Tabelle?

\dp irgendeineTabelle

Rechte für Tabelle zuweisen/entziehen

REVOKE ALL ON tabelle FROM benutzer;
GRANT SELECT ON gewaesser TO peter;
GRANT SELECT, UPDATE ON gewaesser TO PUBLIC;

Rollen

Benutzer anlegen

CREATE ROLE forscher;
CREATE ROLE praktikant;
CREATE ROLE schueler;

login erlauben/verbieten

CREATE ROLE praktikant WITH LOGIN;
ALTER ROLE praktikant WITH NOLOGIN;
ALTER ROLE forscher WITH LOGIN;
ALTER ROLE forscher WITH NOLOGIN;

Beispiel: Rollen zuteilen

CREATE ROLE kibitz;
CREATE SCHEMA guest;
create type guest.kraftstoff_arten as enum
  ('diesel', 'normal', 'super');
CREATE TABLE guest.preisvergleich_tankstellen (
  id serial
 ,datum date  default now()
 ,art kraftstoff_arten default 'normal'
 ,preis decimal(4,2));

-- GRANT USAGE ON guest.preisvergleich_tankstellen TO kibitz;

GRANT SELECT, INSERT, UPDATE, DELETE
ON guest.preisvergleich_tankstellen TO kibitz;

GRANT kibitz TO praktikant, schueler;

Rechte auf Tabelle prüfen

\x
\dp preisvergleich_tankstellen

Access privileges
-[ RECORD 1 ]-----+---------------------------
Schema            | guest
Name              | preisvergleich_tankstellen
Type              | table
Access privileges | postgres=arwdDxt/postgres +
              | kibitz=arwd/postgres
Column privileges |
Policies          |

**Bedeutung der Abkürzungen**

rolename=xxxx -- Privilegien einer Role
    =xxxx -- Privilegein für die Rolle: PUBLIC

        r -- SELECT ("read")
        w -- UPDATE ("write")
        a -- INSERT ("append")
        d -- DELETE
        D -- TRUNCATE
        x -- REFERENCES
        t -- TRIGGER
        X -- EXECUTE
        U -- USAGE
        C -- CREATE
        c -- CONNECT
        T -- TEMPORARY
      arwdDxt -- ALL PRIVILEGES (für tablen, unterschiedlich nach Objekttyp)
        * -- grant option für vorgelagerte Privilegien

    /yyyy -- Rolle die das Privilegvergeben hat

Welcher Benutzer hat welche Rolle(n)/Rechte

\du
                                List of roles
 Role name  |                         Attributes                         | Member of
------------+------------------------------------------------------------+------------
 forscher   |                                                            | {}
 kibitz     | Cannot login                                               | {}
 marketing  | Cannot login                                               | {postgres}
 peter      | Create DB                                                  | {sales}
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 praktikant | Cannot login                                               | {kibitz}
 sales      | Cannot login                                               | {}
 schueler   |                                                            | {kibitz}

Funktion für die Abfrage

CREATE OR REPLACE FUNCTION schema_privs(text)
RETURNS table(username text, schemaname name, privieleges text[])
AS
$$
  SELECT $1, c.nspname, array(select privs from unnest(ARRAY[
  (CASE
    WHEN
      has_schema_privilege($1,c.oid,'CREATE')
    THEN
     'CREATE'
    ELSE NULL END),
  (CASE
    WHEN
      has_schema_privilege($1,c.oid,'USAGE')
    THEN 'USAGE'
    ELSE NULL END)])
  foo(privs) WHERE privs IS NOT NULL)
  FROM pg_namespace c
    where has_schema_privilege($1,c.oid,'CREATE,USAGE');
$$ language sql;

Ergebnis des Funktionsaufrufs

select  schema_privs('kibitz');

       schema_privs
-------------------------------------
 (kibitz,pg_catalog,{USAGE})
 (kibitz,public,"{CREATE,USAGE}")
 (kibitz,information_schema,{USAGE})
(3 rows)

Wer darf ein INSERT ausführen?

SELECT rolname
FROM pg_roles
WHERE has_table_privilege( rolname,
                      'preisvergleich_tankstellen',
                      'INSERT');

  rolname
------------
 postgres
 praktikant
 marketing
 kibitz
 schueler
(5 rows)

INSERT für einen Benutzer/die Rolle?

SELECT oid, relname
FROM pg_class
WHERE has_table_privilege('kibitz', oid, 'INSERT');

  oid  |          relname
-------+----------------------------
 26277 | preisvergleich_tankstellen
(1 row)

Listenfunktion für Rollen

CREATE VIEW priv_membership AS
WITH RECURSIVE membership_tree(grpid, userid) AS (
SELECT DISTINCT pg_roles.oid, pg_roles.oid
FROM pg_roles
UNION ALL
SELECT m.roleid, t.userid
FROM pg_auth_members m
JOIN membership_tree t ON m.member = t.grpid
)
SELECT DISTINCT t.userid, r.rolname AS usrname, t.grpid, m.rolname AS grpname
FROM membership_tree t
JOIN pg_roles r ON r.oid = t.userid
JOIN pg_roles m ON m.oid = t.grpid
ORDER BY r.rolname, m.rolname;

Beispielabfragen

select * from priv_membership where usrname='kibitz';
 userid | usrname | grpid | grpname
--------+---------+-------+---------
  26265 | kibitz  | 26265 | kibitz
(1 row)

oracle=# select * from priv_membership where grpname='kibitz';
 userid |  usrname   | grpid | grpname
--------+------------+-------+---------
  26265 | kibitz     | 26265 | kibitz
  26115 | praktikant | 26265 | kibitz
  26283 | schueler   | 26265 | kibitz
(3 rows)