====== PostgreSQL 8.1 ======


===== PostgreSQL 8.1 (ArchLinux) =====


==== grundsätzliches ====

  # vi ~postgres/data/postgresql.conf
  # vi ~pgsql/data/postgresql.conf
        listen_addresses = '*'
        port = 5432
        max_connections = 100
        ssl = off
        shared_buffers = 1000
        log_directory = '/var/log'
        log_filename = 'postgresql.log'
        log_rotation_size = 10240
        stats_start_collector = on
        stats_row_level = on
        autovacuum = on
        timezone = MET
        client_encoding = UTF-8
        lc_messages = 'de_DE.utf8'
        lc_monetary = 'de_DE.utf8'
        lc_numeric = 'de_DE.utf8'
        lc_time = 'de_DE.utf8'

  # vi ~postgres/data/pg_hba.conf 
  # vi ~pgsql/data/pg_hba.conf 
        # IPv4 local connections:
        host    all         all         127.0.0.1/32          trust
        host    all         all         192.168.4.111/32      trust
        host    all         all         192.168.4.112/32      trust

  # vi ~postgres/data/pg_ident.conf
  # vi ~pgsql/data/pg_ident.conf
        MAPNAME     IDENT-USERNAME    PG-USERNAME


==== SSL-Verschlüsselte Verbindungen ====

  * [[http://www.postgresql.org/files/documentation/books/pghandbuch/html/ssl-tcp.html]]
        Der Server wartet auf normale und auf SSL-Verbindungen auf dem selben
        TCP-Port und verhandelt mit verbindenden Clients ob SSL verwendet
        werden soll.

SSL konfigurieren
  # vi /etc/ssl/openssl.cnf
        ....
        default_days    = 365                   # how long to certify for
        default_crl_days= 30                    # how long before next CRL
        default_md      = sha1                  # which md to use.
        preserve        = no                    # keep passed DN ordering
        ....
        [ req ]
        default_bits            = 8192
        default_keyfile         = privkey.pem
        distinguished_name      = req_distinguished_name
        attributes              = req_attributes
        x509_extensions = v3_ca # The extentions to add to the self signed cert
        ....
        [ req_distinguished_name ]
        countryName                     = Country Name (2 letter code)
        countryName_default             = DE
        countryName_min                 = 2
        countryName_max                 = 2
  
        stateOrProvinceName             = State or Province Name (full name)
        stateOrProvinceName_default     = Hessen
  
        localityName                    = Locality Name (eg, city)
        localityName_default            = Frankfurt am Main
  
        0.organizationName              = Organization Name (eg, company)
        0.organizationName_default      = Interactive Data Managed Solutions
  
        # we can do this but it is not needed normally :-)
        #1.organizationName             = Second Organization Name (eg, company)
        #1.organizationName_default     = World Wide Web Pty Ltd
  
        organizationalUnitName          = Organizational Unit Name (eg, section)
        organizationalUnitName_default  = System Administration
  
        commonName                      = Common Name
        commonName_default              = manfred.frankfurter-softwarefabrik.de
        commonName_max                  = 64
  
        emailAddress                    = eMail Adresse
        emailAddress_default            = manfred.heins@interactivedata.com
        emailAddress_max                = 64
        ....

selbstsigniertes Zertifikat erzeugen:
  # cd ~postgres/data/
  # cd ~pgsql/data/
        ### Bei "Common Name" muss der Hostname rein!
        openssl req -new -text -out server.req
        ### Passphrase entfernen:
        openssl rsa -in privkey.pem -out server.key && rm privkey.pem
        ### Schlüssel entsperren
        openssl req -x509 -in server.req -text -key server.key -out server.crt
        rm server.req
        chmod og-rwx server.key
        chown postgres:postgres server.*
        chown pgsql:pgsql server.*
  
  # vi ~postgres/data/postgresql.conf
  # vi ~pgsql/data/postgresql.conf
        ....
        ssl = on
        ....

  # vi ~postgres/data/pg_hba.conf 
  # vi ~pgsql/data/pg_hba.conf 
        # "local" is for Unix domain socket connections only
        local   all         all                               trust
        # IPv4 local connections:
        ### damit nur noch SSL-verschlüsselte Verbindungen möglich sind
        hostssl all         all         127.0.0.1/32          trust
        hostssl all         all         192.168.4.111/32      trust
        hostssl all         all         192.168.4.112/32      trust