class role::cryptoportfolio::postgresql inherits role::cryptoportfolio { $password_seed = lookup("base_installation::puppet_pass_seed") $pg_password = generate_password(24, $password_seed, "postgres_cryptoportfolio") $pg_replication_password = generate_password(24, $password_seed, "postgres_cryptoportfolio_replication") file { "/var/lib/postgres/data/certs": ensure => directory, mode => "0700", owner => $::profile::postgresql::pg_user, group => $::profile::postgresql::pg_user, require => File["/var/lib/postgres"], } file { "/var/lib/postgres/data/certs/cert.pem": source => "file:///etc/letsencrypt/live/$web_host/cert.pem", mode => "0600", links => "follow", owner => $::profile::postgresql::pg_user, group => $::profile::postgresql::pg_user, require => [Letsencrypt::Certonly[$web_host], File["/var/lib/postgres/data/certs"]] } file { "/var/lib/postgres/data/certs/privkey.pem": source => "file:///etc/letsencrypt/live/$web_host/privkey.pem", mode => "0600", links => "follow", owner => $::profile::postgresql::pg_user, group => $::profile::postgresql::pg_user, require => [Letsencrypt::Certonly[$web_host], File["/var/lib/postgres/data/certs"]] } postgresql::server::config_entry { "wal_level": value => "logical", } postgresql::server::config_entry { "ssl": value => "on", require => Letsencrypt::Certonly[$web_host], } postgresql::server::config_entry { "ssl_cert_file": value => "/var/lib/postgres/data/certs/cert.pem", require => Letsencrypt::Certonly[$web_host], } postgresql::server::config_entry { "ssl_key_file": value => "/var/lib/postgres/data/certs/privkey.pem", require => Letsencrypt::Certonly[$web_host], } postgresql::server::db { $pg_db: user => $pg_user, password => postgresql_password($pg_user, $pg_password), } -> postgresql_psql { "CREATE PUBLICATION ${pg_db}_publication FOR ALL TABLES": db => $pg_db, unless => "SELECT 1 FROM pg_catalog.pg_publication WHERE pubname = '${pg_db}_publication'", } -> postgresql::server::role { $pg_user_replication: db => $pg_db, replication => true, password_hash => postgresql_password($pg_user_replication, $pg_replication_password), } -> postgresql::server::database_grant { $pg_user_replication: db => $pg_db, privilege => "CONNECT", role => $pg_user_replication, } -> postgresql::server::grant { "all tables in schema:public:$pg_user_replication": db => $pg_db, role => $pg_user_replication, privilege => "SELECT", object_type => "ALL TABLES IN SCHEMA", object_name => "public", } -> postgresql::server::grant { "all sequences in schema:public:$pg_user_replication": db => $pg_db, role => $pg_user_replication, privilege => "SELECT", object_type => "ALL SEQUENCES IN SCHEMA", object_name => "public", } postgresql::server::pg_hba_rule { 'allow localhost TCP access to cryptoportfolio user': type => 'host', database => $pg_db, user => $pg_user, address => '127.0.0.1/32', auth_method => 'md5', order => "05-01", } postgresql::server::pg_hba_rule { 'allow localhost ip6 TCP access to cryptoportfolio user': type => 'host', database => $pg_db, user => $pg_user, address => '::1/128', auth_method => 'md5', order => "05-01", } postgresql::server::pg_hba_rule { 'allow TCP access to replication user from immae.eu': type => 'hostssl', database => $pg_db, user => $pg_user_replication, address => 'immae.eu', auth_method => 'md5', order => "05-01", } }