====== PostgreSQL 12 ====== In PostgreSQL 9.5 lief die DB als User "pgsql", in PostgreSQL 12.3 läut die DB als User "postgres". ===== PostgreSQL 12.3 (FreeBSD 12.1-RELEASE) ===== > psql -V psql (PostgreSQL) 12.3 > su - postgres > psql -U postgres -l ; echo "\dg" | psql -U postgres postgres ==== Installation von PostgreSQL 12.3 auf FreeBSD 12.1-RELEASE ==== Zeichensatz prüfen, ich möchte die DB mit dem Zeichensatz "de_DE.UTF-8" betreiben, deshalb überprüfe ich, ob es den im System gibt: > locale -a | fgrep de_DE.UTF-8 de_DE.UTF-8 ...wie wir sehen, gibt es ihn im System... :-) Installation: > pkg install databases/postgresql12-server Vorbereitung: > less /usr/local/share/doc/postgresql/README-server > vi /etc/login.conf ### deutsch german|German Users Accounts:\ :charset=UTF-8:\ :lang=de_DE.UTF-8:\ :setenv=LC_COLLATE=C:\ :tc=default: > vi /etc/rc.conf postgresql_enable="YES" # postgresql_data="/usr/local/pgsql/data" postgresql_data="/home/pgsql/data" postgresql_class="german" damit jeder Benutzer aus dem LAN (''192.168.1.0/24'') auf jede Datenbank zugreifen darf: > vi /home/pgsql/data/pg_hba.conf ... # TYPE DATABASE USER ADDRESS METHOD ... host all all 192.168.1.0/24 trust ... damit die Datenbank aus dem LAN (''192.168.1.0/24'') erreichbar ist: > vi /home/pgsql/data/postgresql.conf ... listen_addresses = '*' ... einmalige Initialisierung > /usr/local/etc/rc.d/postgresql initdb [root@freebsd13~]# service postgresql start 2021-04-16 02:53:00.713 MEST [94801] LOG: starting PostgreSQL 12.5 on amd64-portbld-freebsd13.0, compiled by FreeBSD clang version 11.0.1 (git@github.com:llvm/llvm-project.git llvmorg-11.0.1-0-g43ff75f2c3fe), 64-bit 2021-04-16 02:53:00.714 MEST [94801] LOG: erwarte Verbindungen auf IPv6-Adresse »::«, Port 5432 2021-04-16 02:53:00.714 MEST [94801] LOG: erwarte Verbindungen auf IPv4-Adresse »0.0.0.0«, Port 5432 2021-04-16 02:53:00.719 MEST [94801] LOG: erwarte Verbindungen auf Unix-Socket »/tmp/.s.PGSQL.5432« 2021-04-16 02:53:00.748 MEST [94801] LOG: Logausgabe nach stderr endet 2021-04-16 02:53:00.748 MEST [94801] TIPP: Die weitere Logausgabe geht an Logziel »syslog«. [root@freebsd13~]# service postgresql status pg_ctl: server is running (PID: 94801) /usr/local/bin/postgres "-D" "/home/pgsql/data" zum PostgreSQL-root-User werden: > su - postgres Datenbanken anzeigen: $ psql -l Liste der Datenbanken Name | Eigentümer | Kodierung | Sortierfolge | Zeichentyp | Zugriffsprivilegien -----------+------------+-----------+--------------+-------------+----------------------- postgres | postgres | UTF8 | C | de_DE.UTF-8 | template0 | postgres | UTF8 | C | de_DE.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C | de_DE.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (3 Zeilen) Tabellen aus einer Datenbank anzeigen: $ echo "\dt" | psql -U postgres datenbankname Benutzer antzeigen: $ echo "\dg" | psql postgres Liste der Rollen Rollenname | Attribute | Mitglied von ------------+-----------------------------------------------------------------+-------------- postgres | Superuser, Rolle erzeugen, DB erzeugen, Replikation, Bypass RLS | {} User anlegen: $ createuser fritz User anzeigen: $ echo "\dg" | psql postgres Liste der Rollen Rollenname | Attribute | Mitglied von ------------+-----------------------------------------------------------------+-------------- fritz | | {} postgres | Superuser, Rolle erzeugen, DB erzeugen, Replikation, Bypass RLS | {} dem User ein Passwort geben: $ echo "" | psql postgres wieder ausloggen und wieder "ich" werden: $ exit ==== Beispiel ==== Benutzer "mmroot" anlegen: > createuser mmroot Benutzer anzeigen: > su - postgres -c 'echo "\dg+" | psql' Datenbank "mm_oeffentlich" anlegen: > su - postgres -c 'echo "CREATE DATABASE mm_oeffentlich OWNER = mmroot" | psql' CREATE DATABASE Datenbanken anzeigen > su - postgres -c 'psql postgres -l' Tabelle "video" mit [[https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-serial/|Autoincrement]] (''SERIAL'') anlegen //(es ist nur eine einzige Tabelle, eigentlich müsste sie bis zur 3. Normalform normalisiert werden)//: > su - postgres -c 'echo "CREATE TABLE video (id SERIAL, inode INT, quersumme VARCHAR(1000), dateiname VARCHAR(10000));" | psql mm_oeffentlich' CREATE TABLE Tabellen anzeigen: > su - postgres -c "echo \"SELECT tablename FROM pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';\" | psql -d mm_oeffentlich" tablename ----------- video (1 row) Tabelleninhalt anzeigen: > su - postgres -c "echo \"SELECT * FROM video;\" | psql -d mm_oeffentlich" id | inode | quersumme | dateiname ----+-------+-----------+----------- (0 rows) testweise einen Datensatz in die Tabelle eintragen: > su - postgres -c "echo \"INSERT INTO video (inode,quersumme,dateiname) VALUES ('1234567','asdfghjkl','Film.mkv');\" | psql -d mm_oeffentlich" INSERT 0 1 Testdatensatz anzeigen: > su - postgres -c "echo \"SELECT * FROM video;\" | psql -d mm_oeffentlich" id | inode | quersumme | dateiname ----+---------+-----------+----------- 1 | 1234567 | asdfghjkl | Film.mkv (1 row) Tabelleninhalt zählen: > su - postgres -c "echo \"SELECT COUNT(*) FROM video;\" | psql -d mm_oeffentlich" count ------- 1 (1 row) Tabelle "video" löschen: > su - postgres -c 'echo "DROP TABLE video;" | psql mm_oeffentlich' DROP TABLE Tabelle "video" anlegen: > su - postgres -c 'echo "CREATE TABLE video (id SERIAL, inode INT, quersumme VARCHAR(1000), dateiname VARCHAR(10000));" | psql mm_oeffentlich' CREATE TABLE ==== Syntax gegen SQL-Injektion ==== Um SQL-Injektion zu verhindern, dürfen die Variablen nicht durch ''echo'' ausgegeben werden. Um sie dennoch an den Datenbank-Client übergeben zu können, werden die Optionen ''\z'' (plSQL), ''%%--z%%'' oder ''-v'' verwendet. VERZEICHNIS="$(echo "${VERZEICHNIS}" | sed "s/[']/''/g")" DATEINAME="$(echo "${DATEINAME}" | sed "s/[']/''/g")" echo "INSERT INTO video (dateigröße_in_mib,spieldauer,auflösung,tonspuren,untertitelspuren,inode,quersumme,verzeichnis,dateiname,beschreibung) VALUES (:DATEIGROESSE, :VIDEOLAENGE, :AUFLOESUNG, :AUDIO_SPUREN, :UNTERTITEL_SPUREN, :INODE, :Q, :VERZEICHNIS, :DATEINAME, :BESCHREIBUNG);" | psql -U mmroot -d mm_oeffentlich -v DATEIGROESSE="'${DATEIGROESSE}'" -v VIDEOLAENGE="'${VIDEOLAENGE}'" -v AUFLOESUNG="'${AUFLOESUNG}'" -v AUDIO_SPUREN="'${AUDIO_SPUREN}'" -v UNTERTITEL_SPUREN="'${UNTERTITEL_SPUREN}'" -v INODE="'${INODE}'" -v Q="'${Q}'" -v VERZEICHNIS="'${VERZEICHNIS}'" -v DATEINAME="'${DATEINAME}'" -v BESCHREIBUNG="'${BESCHREIBUNG}'" Die doppelte Einfassung, einmal mit doppelten und dann noch einmal mit einfachen Hochkommas, hat den Sinn, dass die doppelten Hochkommas für die SHELL sind und die einfachen Hochkommas für SQL. Die ersten beiden Zeilen im Beispiel-Kode, verändern den Inhalt der Variablen ''VERZEICHNIS'' und ''DATEINAME'' insofern, dass bereits vorhandene einfache Hochkommas (''%%'%%'') verdoppelt werden, um sie für SQL zu maskieren. Anderenfalls können sie nicht in die Datenbank eingetragen werden. Zum Beispiel muß der Dateiname ''%%Best's of Cap.mp4%%'' in ''%%Best''s of Cap.mp4%%'' umgewandelt werden. ein weiteres Beispiel: > echo "SELECT dateigröße_in_mib FROM video WHERE pfad=:VERZEICHNIS AND dateiname=:DATEINAME;" | psql -U mmroot -td mm_oeffentlich -v VERZEICHNIS="'/zpool/Video/Test/Test''s 1'" -v DATEINAME="'Best''s of \"Cap\".mp4'"