Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- datenbank:postgresql [2026-05-08 08:02:47] – [Benutzer mit reduzierten Rechten, kann nicht einmal Datenbanken anlegen] manfred
+++ datenbank:postgresql [2026-05-15 10:46:00] (aktuell) – manfred
@@ Zeile 23: / Zeile 23: @@
 ===== Anleitungen bzw. Beispiele =====
+  * **[[::Datenbank:PostgreSQL Dump]]**
   * **[[::Datenbank:PostgreSQL Upgrade]]**
+  * **[[::Datenbank:HA-PostgreSQL-Cluster]]**
   * [[::Datenbank:PostgreSQL 12]]
   * [[::Datenbank:PostgreSQL 9.5.2]]
@@ Zeile 204: / Zeile 206: @@
 <code bash>
-> echo 'CREATE DATABASE testdb;' | sudo -u postgres psql
+> echo "CREATE DATABASE testdb;" | psql -h localhost -d postgres -U dbadmin -W
-> echo 'CREATE TABLE testtab (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, code VARCHAR(10));' | sudo -u postgres psql
+Password:
+CREATE DATABASE
-> echo -e '\du\n\l\n\dt' | sudo -u postgres psql
+> echo "GRANT CONNECT ON DATABASE testdb TO dbuser;" | psql -h localhost -d testdb -U dbadmin -W
+> echo "GRANT USAGE, CREATE ON SCHEMA public TO dbuser;" | psql -h localhost -d testdb -U dbadmin -W
+> echo "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO dbuser;" | psql -h localhost -d testdb -U dbadmin -W
+> echo "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dbuser;" | psql -h localhost -d testdb -U dbadmin -W
+> echo "GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO dbuser;" | psql -h localhost -d testdb -U dbadmin -W
+> echo "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO dbuser;" | psql -h localhost -d testdb -U dbadmin -W
+> echo -e '\du\n\l\n' | sudo -u postgres psql
                              List of roles
  Role name |                         Attributes
@@ Zeile 223: / Zeile 233: @@
  template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
            |          |          |                 |         |         |            |           | postgres=CTc/postgres
- testdb    | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =Tc/postgres         +
+ testdb    | dbadmin  | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =Tc/dbadmin          +
-           |          |          |                 |         |         |            |           | postgres=CTc/postgres+
+           |          |          |                 |         |         |            |           | dbadmin=CTc/dbadmin  +
-           |          |          |                 |         |         |            |           | dbadmin=c/postgres   +
+           |          |          |                 |         |         |            |           | dbuser=c/dbadmin
-           |          |          |                 |         |         |            |           | dbuser=c/postgres
 (4 rows)
-          List of relations
- Schema |  Name   | Type  |  Owner
+> echo 'CREATE TABLE testtab (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, code VARCHAR(10));' | psql -h localhost -d testdb -U dbuser -W
---------+---------+-------+----------
+Password:
- public | testtab | table | postgres
+CREATE TABLE
+> echo '\dt' | psql -h localhost -d testdb -U dbuser -W
+Password:
+         List of relations
+ Schema |  Name   | Type  | Owner
+--------+---------+-------+--------
+ public | testtab | table | dbuser
 (1 row)
-> echo "INSERT INTO testtab (code) VALUES ('ABC');" | sudo -u postgres psql
+> echo "INSERT INTO testtab (code) VALUES ('ABC');" | psql -h localhost -d testdb -U dbuser -W
+Password:
 INSERT 0 1
-> echo "SELECT * FROM testtab;" | sudo -u postgres psql
+> echo "SELECT * FROM testtab;" | psql -h localhost -d testdb -U dbuser -W
+Password:
  id | code
 ----+------