====== PostgreSQL ======

Anleitung für den Fall, dass eine Postgres Datenbank zum lokalen Speichern von ''shibpid'' und ''storagerecords'' verwendet werden soll.

===== Installation der Abhängigkeiten =====

<code yaml>
- name: "Install postgres"
  apt:
    name: "{{ item }}"
  loop:
    - postgresql
    - libpostgresql-jdbc-java
    - libpq-dev
    - python-psycopg2
  tags:
    - postgres

- name: Ensure german locale exists
  community.general.locale_gen:
    name: de_DE.UTF-8
    state: present
  tags:
    - postgres
  notify: restart postgresql
</code>

Der Handler ist einfach:

<code yaml>
- name: restart postgresql
  service: name=postgresql state=restarted
</code>

===== Erstellen der Datenbank =====

==== Leere Datenbank ====

Der folgende Block von Tasks erstellt eine geeignete Datenbank mit den entsprechenden Tabellen. Variablen: ''postgresql_username: shibboleth'', ''postgresql_password: SETZMICH''

<code yaml>
- name: Create and initialize Postgres DB
  block:
    - name: Postgres - CREATE ROLE
      postgresql_user:
        name: "{{ postgresql_username }}"
        password: "{{ postgresql_password }}"
        role_attr_flags: CREATEROLE
        expires: infinity

    - name: Postgres - CREATE DATABASE
      community.postgresql.postgresql_db:
        name: "{{ postgresql_username }}"
        owner: "{{ postgresql_username }}"
        encoding: UTF-8
        lc_collate: de_DE.UTF-8
        lc_ctype: de_DE.UTF-8
        template: template0

    - name: Postgres - CREATE TABLE shibpid
      postgresql_table:
        db: "{{ postgresql_username }}"
        name: shibpid
        columns:
        - localEntity VARCHAR(1024) NOT NULL
        - peerEntity VARCHAR(1024) NOT NULL
        - principalName VARCHAR(255) NOT NULL
        - localId VARCHAR(255) NOT NULL
        - persistentId VARCHAR(36) NOT NULL
        - peerProvidedId VARCHAR(255) NULL
        - creationDate TIMESTAMP NOT NULL DEFAULT LOCALTIMESTAMP
        - deactivationDate TIMESTAMP NULL DEFAULT NULL
        - PRIMARY KEY (localEntity, peerEntity, persistentId)
        owner: "{{ postgresql_username }}"
        storage_params:
        - autovacuum_vacuum_scale_factor = 0.002  # fraction of table size before vacuum
        - autovacuum_analyze_scale_factor = 0.001 # fraction of table size before analyze

    - name: CREATE INDEX shibpid_getbysourcevalue_index
      postgresql_idx:
        db: "{{ postgresql_username }}"
        idxname: shibpid_getbysourcevalue_index
        table: shibpid
        columns:
        - localEntity
        - peerEntity
        - localId
        - deactivationDate

    - name: Postgres - CREATE TABLE storagerecords
      postgresql_table:
        db: "{{ postgresql_username }}"
        name: storagerecords
        columns:
        - context VARCHAR(255) NOT NULL
        - id VARCHAR(255) NOT NULL
        - expires BIGINT DEFAULT NULL
        - value TEXT NOT NULL
        - version BIGINT NOT NULL
        - PRIMARY KEY (context, id)
        owner: "{{ postgresql_username }}"
        storage_params:
        - autovacuum_vacuum_scale_factor = 0.002  # fraction of table size before vacuum
        - autovacuum_analyze_scale_factor = 0.001 # fraction of table size before analyze

    - name: CREATE INDEX storagerecords_expires_index
      postgresql_idx:
        db: "{{ postgresql_username }}"
        idxname: storagerecords_expires_index
        table: storagerecords
        columns:
        - expires

    - name: grant privileges to postgresql_user
      postgresql_privs:
        database: "{{ postgresql_username }}"
        roles: "{{ postgresql_username }}"
        privs: TEMPORARY,CONNECT,CREATE
        type: database

  when: new_postgres_db|default(false)|bool
  become: yes
  become_method: su
  become_user: postgres
  tags:
    - postgres
</code>


==== Vorhandene Postgres DB übernehmen ====

Soll eine vorhandene DB übertragen werden, benötigt man den oberen Block nicht, sondern kann einmalig auf dem Quellsystem einen Dump erstellen:

<code bash>
root@idp:~# su - postgres
postgres@idp:~$ psql
postgres@idp /tmp % pg_dumpall -c -f dump.out
</code>

Zwei Kniffe:

  *''pg_dumpall'' arbeitet nicht mit ''pg_restore'' zusammen
  *Locale und Ctype werden nicht exportiert

Import auf Zielsystem:

''dump.out'' editieren und die Zeile:

<code bash>
CREATE DATABASE shibboleth WITH TEMPLATE = template0 OWNER = shibboleth;
</code>

ergänzen zu:

<code bash>
CREATE DATABASE shibboleth WITH TEMPLATE = template0 OWNER = shibboleth LC_COLLATE = 'de_DE.UTF-8' LC_CTYPE = 'de_DE.UTF-8';
</code>

Dann DB auf Zielsystem einspielen:

<code bash>
postgres@idp-dev:~$ psql -f /tmp/dump.out
</code>

Da dies für gewöhnlich nur einmalig gemacht wird, existiert bisher keine Ansible Rolle. Falls jemand eine erstellt, gerne ergänzen / ersetzen.