PostgreSQL Cheat Sheets – Sammlung

Für PostgreSQL finden sich einige sehr nützliche Cheat-Sheets, um im Alltag eine schnelle Referenz zur Hand zu haben.

Die korrekte Übersetzung von Cheat-Sheet lautet „Schummelzettel“.

Allerdings zielt die Verwendung in erster Linie nicht auf das Schummeln ab, sonder es handelt sich vielmehr um eine übersichtliche Zusammenstellung von wichtigsten Details zu einem ganz bestimmten Thema.

Im Idealfall sollte die Länge von einem A4 Blatt nicht überschritten werden, aber es gibt natürlich auch umfassendere Cheat-Sheets.

Zum Thema PostgreSQL haben wir folgende nützliche Cheat-Sheets gefunden:

[Link ] PostgreSQL Cheat Sheet von postgresqltutorial.com – 3 Seiten

In diesem Cheat Sheet werden folgende PostgreSQL Themen behandelt:

  • quering data from table
  • quering from multible tables (verschiedene Joins – left join, right join, outer join, cross join, usw..)
  • SQL operatoren – union, intersect, except, like – not like, in – not in, between and, is null – is not null
  • Table Management – create, drop, alter, truncate, etc…
  • SQL Constraints – primary key, foreign key, unique, check, usw..
  • Data Modification – insert into values, insert into select * from table, update set, delete from, etc…
  • View Management – create view as select, create recursive view, create temporary view, etc…
  • Index Management – create index, create unique index, drop index
  • SQL Aggreate Functions – avg, count, sum, max, min
  • Trigger Management – create trigger when event, etc…

[ Link ] Postgresql Cheat Sheet von alberton.info – 1 Seite

In diesem Cheat-Sheet werden folgende PostgreSQL Themen behandelt:

  • Data Types
  • Internal Functions
  • Usefull Queries
  • Information Shema

[ Link ] PostgreSQL Sting Functions Cheat Sheet von SQLBackupAndFTP.com – 1 Seite

In diesem Cheat-Sheet werden PostgreSQL String Functions behandelt:

  • Conversion
  • Measurement
  • Modification

[Link ] Postgresql terminal commands – cheatography.com – 1 Seite

In diesem Cheat-Sheet werden PostgreSQL terminal commands behandelt:

  • Connecting
  • PSQL
  • Roles and database management
  • Database backup

 

 

 

 

Ändern des Passwort Hash Algorithmus in PostgreSQL

PostgreSQL: Ändern des Passwort Hash Algorithmus

Standardmäßig verwendet PostgreSQL den MD5-Algorithmus, um Passwörter von Benutzern in der Datenbank abzulegen.

Da MD5 mittlerweile nicht mehr als all zu sicher gilt, wird empfohlen, andere Hash Algorithmen für die Passwortablage zu verwenden.
PostgreSQL hat dazu ab Version 10 die Möglichkeit geschaffen, auf den SCRAM-SHA-256-Algorithmus umzustellen.

Wichtig ist, wie bereits erwähnt, dass die PostgreSQL Datenbank Version 10 oder höher ist, sowie, dass die Programme, die auf die Datenbank zugreifen, diesen Algorithmus unterstützen.

Sind die Bedingungen erfüllt, kann man den Default-Algorithmus in der Datenbankkonfiguration umstellen.

Dies muss an zwei Stellen getan werden:

1.       In der postgresql.conf (per Default im Verzeichnis /etc/postgresql/10/main/postgresql.conf) muss der Parameter password_encryption auf ’scram-sha-256′ gesetzt werden.
Die resultierende Zeile muss in der postgresql.conf folgendermaßen aussehen:
password_encryption = ’scram-sha-256′

2.  Außerdem muss in der pg_hba.conf (per Default im Verzeichnis /etc/postgresql/10/main/pg_hba.conf) der gewünschte Algorithmus für die gewünschte Socketmethode angepasst werden. Die Konfiguration befindet sich am Ende der Datei, für IPv4 müsste die Zeile danach so aussehen:
# IPv4 local connections:

host    all             all             127.0.0.1/32            scram-sha-256

Ist dies getan, werden alle zukünftigen Passwörter nun mit dem SCRAM-SHA-256-Algorithmus gehashed.

WICHTIG: Alle bereits gesetzten Passwörter von Datenbankbenutzern bleiben in dem zum jeweiligen Zeitpunkt konfigurierten Hash-Algorithmus bestehen.

Wenn also bereits vorhandene User in der Datenbank existieren, müssen die Passwörter dieser User neu gesetzt werden, um mit dem neuen Hashalgorithmus abgespeichert zu werden.

Ansonsten würde ein Login via PSQL auf die Datenbank den Hashwert des eingegebenen Passwortes mittels SCRAM-SHA-256 ermitteln und diesen Wert mit dem MD5-gehashten abgelegten Passwort in der Datenbank vergleichen.
Da diese Werte nicht übereinstimmen, würde der Zugriff verweigert werden.

Select * from dual in postgresql

Select * from DUAL in Postgres

Wann immer ein simpler Wert per SQL gesetzt oder abgefragt werden soll, wird die Pseudotabelle DUAL dafür herangezogen.

Einer der populärsten Einsatzzwecke dafür war bis Oracle Version 10 die Nutzung von Sequences in PL/SQL.
Sequences mussten in diesen Oracle Versionen immer über eine SQL-Query abgefragt werden, der folgende Code funktionierte:

select my_seq.nextval from dual;

Der folgende Code funktionierte allerdings nicht:

v_myvar := my_seq.nextval;

Die DUAL Tabelle ist eine tatsächlich existierende physische Tabelle in der Datenbank welche im SYS-Schema liegt.

Sie verfügt über eine Spalte mit der Bezeichnung „DUMMY“ welche den Datentyp VARCHAR2(1) hat.
Die Zeile hat eine Zeile mit dem Wert ‚X‘, das kann mit folgendem Statment abgefragt werden:

select * from dual;

Theoretisch kann diese Tabelle auch verändert werden.
Natürlich rät Oracle streng davon ab das zu tun da eine Fülle an unvorhersehbaren Fehlern entstehen kann und wird.

Das grundlegende Verhalten könnte auch mit einer eigenen 1-Spalten-1-Zeilen-Tabelle simuliert werden, doch der Oracle Optimizer erkennt DUAL auch als Keyword und verfährt beim Execution Plan dann entsprechend.

Wie läuft das nun in Postgres?

Wenn Sie Code von Oracle auf Postgres portieren dann werden sie feststellen, dass das Statement von oben …

select 1 from dual;

…nicht funktioniert!
Postgres kennt keine DUAL Tabelle.

Nun gibt es zwei Möglichkeiten, je nachdem was die Ausgangslage und das Ziel ist:

  1. Man legt die DUAL Tabelle in Postgres als „Dummy“-View an
  2. Man passt das Statement an

Die erste Variante ist simpel und vor allem bei größeren Portierungen empfohlen, wo mehrere dutzend oder sogar hunderte Statements angepasst werden müssten.

Die zweite Variante ist bei wenigen Statements zu empfehlen, da sie dem Postgres Standard entspricht. Das entsprechende Statement in Postgres würde dann wie folgt aussehen:

select 1

Die komplette FROM-Klausel verschwindet hier!

Postgres macht durch diese Syntax die Verwendung einer Dummy-Tabelle unnötig um einen einzelnen Wert per SQL zu setzen oder abzufragen (z.B. auch das SYSDATE).

Das ist deutlich simpler.

 

Managed Services für PostgeSQL

Kennen Sie schon unsere Managed Services für PostgreSQL Datenbanken? – hier klicken>