Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- datenbank:postgresql [2024-02-24 15:41:17] – [Anleitungen bzw. Beispiele] 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 59: / Zeile 61: @@
 ''SHOW DATABASES'':
   > psql -U postgres -l
-  > echo -e "\l" | psql -U mmroot -d postgres
+  > echo -e "\l" | psql -U username -d postgres
 ''SHOW TABLES'':
@@ Zeile 93: / Zeile 95: @@
 ''SHOW FULL PROCESSLIST'':
-  > echo "SELECT * FROM pg_stat_activity;" | psql -U username
+  > echo "SELECT * FROM pg_stat_activity;" | psql -U postgres
   > echo "SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age FROM pg_stat_activity WHERE state <> 'idle' AND query NOT LIKE '% FROM pg_stat_activity %' ORDER BY age;" | psql -U username -h 10.1.1.10
+==== Netzwerkzugriffe erlauben ====
+<code bash Zugriffe aus allen Netzen erlauben>
+> echo "listen_addresses = '*'" >> /etc/postgresql/16/main/postgresql.conf
+> echo "host    all             all             0.0.0.0/0               scram-sha-256" >> /etc/postgresql/16/main/pg_hba.conf
+> echo "host    all             all             ::/0                    scram-sha-256" >> /etc/postgresql/16/main/pg_hba.conf
+</code>
+<code bash neue CFG laden>
+> service postgresql reload
+</code>
+<code bash offene DB-Ports zeigen>
+> ss -antp | grep -F LISTEN | grep -F postgres
+LISTEN    0      200          0.0.0.0:5432          0.0.0.0:*     users:(("postgres",pid=40606,fd=6))
+LISTEN    0      200             [::]:5432             [::]:*     users:(("postgres",pid=40606,fd=7))
+</code>
+==== Benutzer anlegen ====
+<code bash DB-Benutzer anzeigen>
+> echo '\du' | sudo -u postgres psql
+                             List of roles
+ Role name |                         Attributes
+-----------+------------------------------------------------------------
+ postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
+</code>
+<code bash Datenbanken anzeigen>
+> echo '\l' | sudo -u postgres psql
+                                                   List of databases
+   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges
+-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
+ postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
+ template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
+           |          |          |                 |         |         |            |           | postgres=CTc/postgres
+ template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
+           |          |          |                 |         |         |            |           | postgres=CTc/postgres
+(3 rows)
+</code>
+=== Benutzer mit allen Rechten, jedoch ohne Benutzer anlegen oder verändern zu können ===
+<code sql>
+# Benutzer anlegen
+CREATE ROLE dbadmin
+  LOGIN
+  CREATEDB
+  PASSWORD 'Passwort01';
+# nicht! zuviele Rechte
+#ALTER ROLE dbadmin CREATEROLE;
+#ALTER ROLE dbadmin SUPERUSER;
+# Rechte für eine Datenbank
+GRANT CONNECT ON DATABASE deine_datenbank TO dbadmin;
+GRANT USAGE, CREATE ON SCHEMA public TO dbadmin;
+# Rechte auf eine bestimmte Tabelle erteilen
+GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE deine_tabelle TO dbadmin;
+# Rechte auf alle vorhandenen Tabellen in "public" erteilen
+GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO dbadmin;
+# Rechte auf alle neuen Tabellen in "public" erteilen
+ALTER DEFAULT PRIVILEGES IN SCHEMA public
+GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER
+ON TABLES
+TO dbadmin;
+# (Optional) Rechte auf Sequences (IDs!)
+GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO dbadmin;
+ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO dbadmin;
+</code>
+=== Benutzer mit reduzierten Rechten, kann nicht einmal Datenbanken anlegen ===
+<code sql>
+# Benutzer anlegen
+CREATE ROLE dbuser
+  LOGIN
+  PASSWORD 'Passwort01';
+# Rechte für eine Datenbank
+GRANT CONNECT ON DATABASE deine_datenbank TO dbuser;
+GRANT USAGE, CREATE ON SCHEMA public TO dbuser;
+# Rechte auf eine bestimmte Tabelle erteilen
+GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE deine_tabelle TO dbuser;
+# Rechte auf alle vorhandenen Tabellen in "public" erteilen
+GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO dbuser;
+# Rechte auf alle neuen Tabellen in "public" erteilen
+ALTER DEFAULT PRIVILEGES IN SCHEMA public
+GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER
+ON TABLES
+TO dbuser;
+</code>
+=== Test-DB + Test-Tabelle anlegen ===
+<code bash>
+> echo "CREATE DATABASE testdb;" | psql -h localhost -d postgres -U dbadmin -W
+Password:
+CREATE DATABASE
+> 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
+-----------+------------------------------------------------------------
+ dbadmin   | Create DB
+ dbuser    |
+ postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
+                                                   List of databases
+   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges
+-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
+ postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
+ template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
+           |          |          |                 |         |         |            |           | postgres=CTc/postgres
+ template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
+           |          |          |                 |         |         |            |           | postgres=CTc/postgres
+ testdb    | dbadmin  | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =Tc/dbadmin          +
+           |          |          |                 |         |         |            |           | dbadmin=CTc/dbadmin  +
+           |          |          |                 |         |         |            |           | dbuser=c/dbadmin
+(4 rows)
+> echo 'CREATE TABLE testtab (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, code VARCHAR(10));' | psql -h localhost -d testdb -U dbuser -W
+Password:
+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');" | psql -h localhost -d testdb -U dbuser -W
+Password:
+INSERT 0 1
+> echo "SELECT * FROM testtab;" | psql -h localhost -d testdb -U dbuser -W
+Password:
+ id | code
+----+------
+| ABC
+(1 row)
+</code>
@@ Zeile 479: / Zeile 646: @@
   > echo "SELECT * FROM Tabelle WHERE Spalte ILIKE ('%suchzeichen%');" | psql -U Benutzer -d Datenbank
-  > echo "SELECT * FROM video WHERE dateiname ILIKE ('%otto%');" | psql -U mmroot -d mm_oeffentlich