pillar.example

# vim: ft=yaml

postgresql:
  version: 11

  # Call reload action instead of restart upon configuration changes.
  # This defaults to "true", as restarts result in a short downtime
  reload: true

  # Packages to additionally install
  # additional_dependencies: [awscli, git]

  postgis:
    version: 2.3

  # Configure postgresql.conf settings
  #
  # When not specified here, the default settings of the postgresql package from PGDG on
  # Ubuntu 18.04 LTS are used.
  config:
    # Listen on all interfaces
    listen_addresses: "'*'"

    # Disable SSL
    # See section "Deploy SSL certificates" for information howto enable SSL
    ssl: false

    # Settings for a general purpose, standalone database instance
    {% set max_connections = 100 %}
    max_connections: {{ max_connections }}
    work_mem: {{ (grains['mem_total'] * 0.9 / max_connections)|int }}MB
    shared_buffers: {{ (grains['mem_total'] * 0.1)|int }}MB
    maintenance_work_mem: {{ (grains['mem_total'] / 1024 * 50)|int }}MB
    effective_cache_size: {{ (grains['mem_total'] * 0.8)|int }}MB


  # Deploy SSL certificates
  #
  # The files will be deployed into the data directory of the server.
  # Make sure to also set the respective settings to access them in the configuration options,
  # e.g.:
  #
  #  config:
  #    ssl: true
  #    ssl_ca_file: root.crt
  #    ssl_cert_file: server.crt
  #    ssl_key_file: server.key
  certificates:
    root.crt: |
      Certificate:
          Data:
              Version: 1 (0x0)
              Serial Number:
                  xx:xx:xx:xx:xx:xx:xx:xx
      [...]
      -----BEGIN CERTIFICATE-----
      [...]
      -----END CERTIFICATE-----
    server.crt: |
      -----BEGIN CERTIFICATE-----
      [...]
      -----END CERTIFICATE-----
    server.key: |
      -----BEGIN RSA PRIVATE KEY-----
      [...]
      -----END RSA PRIVATE KEY-----


  # These users will be created and added to pg_hba.conf
  # NOTE: If you create multiple users with access to the same database, the last user listed
  #       here will become the owner
  users:
    - username: user_without_password
      type: local
      database: production
      method: trust

      # NOTE: The following values represent the defaults
      superuser: false
      createdb: false
      createroles: false
      inherit: true
      replication: false

    # Users with passwords
    # NOTE: By default password are stored in scram-sha-256,
    #       to use md5 you have to set the method explicit to md5.

    # scram-sha-256
    - username: user_with_scram-sha-256_password
      type: host
      database: production
      address: 10.1.2.0/24
      password: password
      method: scram-sha-256

    # md5
    - username: user_with_md5_password
      type: host
      database: production
      address: 10.1.2.0/24
      password: password
      method: md5


    # Create a read-only user
    # NOTE: This requires that the database was alreay created by a non-read-only user before
    - username: read_only_user
      type: local
      database: production
      method: trust
      read_only: true

      # You can specify another owner for the database if required.
      # If db_owner is not set, it will default to the "username" setting above
      db_owner: user_without_password

    - username: owner
      type: host
      database: db_with_extension
      extensions: [pgcrypto]
      address: all
      method: trust

    # Define user, but don't add pg_hba.conf entry
    - username: postgres
      database: template1
      extensions: [pg_stat_statements]
      pg_hba: false
      createdb: true
      createroles: true
      replication: true
      superuser: true

    - username: example_role
      type: host
      database: staging
      address: 10.1.2.0/24
      method: trust
      login: false

    - username: example_user_with_role
      type: host
      database: staging
      address: 10.1.2.0/24
      method: trust
      inherit: true
      groups:
        - example_role

    - username: replicant
      type: host
      database: replication
      extensions: []
      address: 10.1.2.0/24
      replication: true
      method: md5

    - username: allowall
      type: local
      database: all
      method: trust

    # If you want multiple databases for a user, you can use a for loop
    {% for db in ['beta', 'test'] %}
    - username: deploy
      type: host
      database: {{ db }}
      address: 10.1.2.0/24
      method: trust
    {% endfor %}

sysctl:
  vm.overcommit_ratio: 70