Inhaltsverzeichnis
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 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'"