Defined Type: postgresql::role

Defined in:
manifests/role.pp

Overview

postgres=# select * from pg_roles;

    rolname       | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls |                   rolconfig
|  oid

(…)

Parameters:

  • password (Any)
  • rolename (Any) (defaults to: $name)
  • login (Any) (defaults to: true)
  • superuser (Any) (defaults to: false)
  • replication (Any) (defaults to: false)
  • inherit (Any) (defaults to: true)
  • db_host (Any) (defaults to: undef)
  • port (Any) (defaults to: $postgresql::port)
  • pgbouncer_tag (Any) (defaults to: undef) 


5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
 
# File 'manifests/role.pp', line 5

define postgresql::role (
                          $password,
                          $rolename       = $name,
                          $login          = true,
                          $superuser      = false,
                          $replication    = false,
                          $inherit        = true,
                          $db_host        = undef,
                          $port           = $postgresql::port,
                          $pgbouncer_tag  = undef,
                        ) {
  Exec {
    path => '/usr/sbin:/usr/bin:/sbin:/bin',
  }

  Postgresql_psql {
    port => $port,
  }

  $password_hash_md5=md5("${password}${rolename}")
  $password_hash_sql="md5${password_hash_md5}"
  $password_sql="ENCRYPTED PASSWORD '${password}'"

  if($pgbouncer_tag!=undef)
  {
    @postgresql::pgbouncer::username { $rolename:
      password_md5 => $password_hash_sql,
      tag => $pgbouncer_tag,
    }
  }

  postgresql_psql { "ALTER ROLE ${rolename} ENCRYPTED PASSWORD":
    command => "ALTER ROLE \"${rolename}\" ${password_sql}",
    unless  => "SELECT usename FROM pg_shadow WHERE usename='${rolename}' and passwd='${password_hash_sql}'",
    require => [Class['::postgresql::service'], Postgresql_psql["CREATE ROLE ${rolename}"]],
  }

  $login_sql=$login ? { true => 'LOGIN', default => 'NOLOGIN' }

  postgresql_psql {"ALTER ROLE \"${rolename}\" ${login_sql}":
    unless  => "SELECT rolname FROM pg_roles WHERE rolname='${rolename}' and rolcanlogin=${login}",
    require => [Class['::postgresql::service'], Postgresql_psql["CREATE ROLE ${rolename}"]],
    host    => $db_host,
  }

  $superuser_sql=$superuser ? { true => 'SUPERUSER', default => 'NOSUPERUSER' }

  postgresql_psql {"ALTER ROLE \"${rolename}\" ${superuser_sql}":
    unless  => "SELECT rolname FROM pg_roles WHERE rolname='${rolename}' and rolsuper=${superuser}",
    require => [Class['::postgresql::service'], Postgresql_psql["CREATE ROLE ${rolename}"]],
    host    => $db_host,
  }

  $replication_sql=$replication ? { true => 'REPLICATION', default => '' }

  postgresql_psql {"ALTER ROLE \"${rolename}\" ${replication_sql}":
    unless  => "SELECT rolname FROM pg_roles WHERE rolname='${rolename}' and rolreplication=${replication}",
    require => [Class['::postgresql::service'], Postgresql_psql["CREATE ROLE ${rolename}"]],
    host    => $db_host,
  }

  $inherit_sql = $inherit ? { true => 'INHERIT', default => 'NOINHERIT' }
  postgresql_psql {"ALTER ROLE \"${rolename}\" ${inherit_sql}":
    unless  => "SELECT rolname FROM pg_roles WHERE rolname='${rolename}' and rolinherit=${inherit}",
    require => [Class['::postgresql::service'], Postgresql_psql["CREATE ROLE ${rolename}"]],
    host    => $db_host,
  }

  # ALTER ROLE myprojectuser SET client_encoding TO 'utf8';
  # ALTER ROLE myprojectuser SET default_transaction_isolation TO 'read committed';
  # ALTER ROLE myprojectuser SET timezone TO 'UTC';

  #
  # CREATE ROLE
  #

  postgresql_psql { "CREATE ROLE ${rolename}":
    command => "CREATE ROLE ${rolename} ${login_sql} ${superuser_sql} ${replication_sql} ${password_sql} ${inherit_sql};",
    unless  => "SELECT rolname FROM pg_roles WHERE rolname='${rolename}'",
    require => Class['::postgresql::service'],
    host    => $db_host,
  }

}