CoverMyMeds, Inc

Production

Integration (++)

Testing, Development... (∞)

Puppet Labs

https://forge.puppetlabs.com/puppetlabs/postgresql

Create a Server

class { 'postgresql::server': }
            

Create a Database + User

postgresql::server::db { 'covermymeds':
  user     => 'cmm_user',
  password => postgresql_password('cmm_user', 'supersecret  '),
}
            

Create Database Roles

# Create the Role
postgresql::server::role { 'app_user': }
  password_hash => postgresql_password('app_user', 'secret'),
}

# Apply GRANTS
postgresql::server::database_grant {'app_user':
  privilege => 'ALL',
  db        => 'covermymeds',
  role      => 'app_user',
}
            

Now Scale ⇪

Dig in!

The Psql Resource Type

postgresql_psql { "unique_name":
  command    => 'ANY COMMAND THAT PSQL SUPPORTS',
  db         => 'DATABASE TO ACT ON',
  unless     => 'SQL COMMAND TO CHECK IF ACTION WAS ALREADY TAKEN',
  require    => Class['postgresql::server']
}
          

Hieradata

Hieradata

List of Apps

# DB Cluster to Application Mapping
cmm_pgsql::dblist::cluster1:
  mydatabase:
    apps:
      - myapp1
      - myapp2
      - myapp3

# Equivilant to a hash of arrays
$myhash = hiera('cmm_pgsql::dblist::cluster1')
# yields => {
#  'mydatabase' => {
#    'apps'     => [
#      'myapp1',
#      'myapp2',
#      'myapp3',
#    ],
#  },
#}
          

List of Roles

# Application to Role Mapping
app::dbconfig::myapp1:
  mydatabase:
    write_handle:
      default_handle: true
      role: default_write
      host: vip-cluster1
      database: mydatabase
      adapter: postgres
      username: myapp1_user
      password: password
# Equivalent to a hash of arrays
$myhash = hiera('cmm_pgsql::dbconfig::myapp1')
# yields => {
#  'mydatabase'     => {
#    'write_handle' => {
#      'default_handle' => true,
#      'role'           => 'default_write',
#      'host'           => 'vip-cluster1',
#      'database'       => 'mydatabase',
#      'adapter'        => 'postgres',
#      'username'       => 'myapp1_user',
#      'password'       => 'password',
#    },
#  },
#}
          

Introducing:

Statuses are determined by lib/facter/pg_replication.rb

Base setup will install requirements like SSL keys for client connections, other supporting packages (pg_top, pg_repack, etc), Postgres configuration and Shinken (nagios) Monitors. Masters and slaves alike get base setup configuration.

Hieradata

# Define any valid pg config value
cmm_pgsql::config:
  'hot_standby_feedback':
    value: 'on'
  'ssl':
    value: 'on'
  'hot_standby':
    value: 'on'
  'max_connections':
    value: '100'
  'shared_buffers':
    value: '1024MB'
  'effective_cache_size':
    value: '3072MB'
  'checkpoint_segments':
    value: '32'
  'checkpoint_completion_target':
    value: '0.9'
  'default_statistics_target':
    value: '100'
  'work_mem':
    value: '16MB'
          

The dbhandle type is essentially a hackzor (see dirty little secrets) to link the database creation with the application credentials. This takes the name parameter and allows unique creation of the db role credentials by splitting on a pre-concatenated resource name.

# Create a unique resource name
$role_grants = "role:${role} ${username}@${database}"

# Only define the role if it doesn't exist
unless defined(Postgresql_psql[$role_grants]) {

  postgresql_psql { $role_grants:
    command    => template("cmm_pgsql/grants/${role}.sql.erb"),
    db         => $database,
    psql_user  => $::postgresql::server::user,
    psql_group => $::postgresql::server::group,
    psql_path  => $::postgresql::server::psql_path,
    unless     => "SELECT nspname, defaclobjtype, defaclacl
                 FROM pg_default_acl a
                 JOIN pg_namespace b ON a.defaclnamespace=b.oid
                 WHERE defaclobjtype = 'r'
                 AND aclcontains(defaclacl, '\"${username}\"=r/postgres')",
    require    => Postgresql::Server::Role[$username],
  }

}
          

# Template of grants, with username substituted
GRANT select, insert, update, delete ON ALL TABLES IN SCHEMA public
  TO "<%= @username %>";

GRANT execute ON ALL functions IN SCHEMA public
  TO "<%= @username %>";

GRANT USAGE ON ALL sequences IN SCHEMA public
  TO "<%= @username %>";

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT select, insert, update, delete
  ON TABLES TO "<%= @username %>";

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT execute ON functions to "<%= @username %>";

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT usage ON sequences to "<%= @username %>";
        

# Determine if role permissions have been applied
SELECT nspname, defaclobjtype, defaclacl
  FROM pg_default_acl a
  JOIN pg_namespace b ON a.defaclnamespace=b.oid
  WHERE defaclobjtype = 'r'
  AND aclcontains(defaclacl, '"username"=r/postgres')

## Output
 nspname | defaclobjtype |                   defaclacl
---------+---------------+---------------------------------------------
 public  | r             | {username=r/postgres,username=arwd/postgres}
(1 row)