DevSecOps - Sécurisez, Auditez, Automatisez vos bases de données PostgreSQL (3/4)

Dans les précédents articles (Part1 & Part2), nous avons abordé les principes de sécurité à mettre en œuvre au sein d'une base PostgreSQL. Nous avons appliqué ces principes en déployant dans une 1ere phase, les roles et les permissions pour créer notre user "admin" de notre base.

Continuons en créant le modèle de données de notre "fake application", ainsi que la procédure stockée.

psql -h myfullrdsexample.pandemonium.eu-west-3.rds.amazonaws.com -p 5432 -U admin -d mydatabase -f create-tables.sql -W
Password: <find password in AWS SecretsManager in secret-kv-myfullrdsexample-admin>
CREATE TABLE
INSERT 0 1
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE TABLE


psql -h myfullrdsexample.pandemonium.eu-west-3.rds.amazonaws.com -p 5432 -U admin -d mydatabase -f create-procedure-statistiques.sql -W
CREATE PROCEDURE

psql -h myfullrdsexample.pandemonium.eu-west-3.rds.amazonaws.com -p 5432 -U admin -d mydatabase -f create-tables.sql -W
Password:

mydatabase=> \dt
         List of relations
 Schema |   Name   | Type  | Owner
--------+----------+-------+-------
 public | basket   | table | admin
 public | customer | table | admin
 public | product  | table | admin
 public | stats    | table | admin
(4 rows)

Pour créer des rôles ayant des permissions sur des objets en base, il est nécessaire que ces objets existent. C’est pour cela, qu’on crée d’abord le user “admin”, ensuite le modèle de données et enfin les différents users qui seront utilisés dans les composants de la “fake application”.

Construction de nos users applicatifs avec limitation de leurs permissions

Complétons notre fichier terraform.tfvars :

La partie “role” :

#  CREATE ROLES
   db_roles = [
      { id = "admin", role = "app_admin_role", inherit = true, login = false, validity = "infinity", privileges = ["USAGE", "CREATE"], createrole = true },
      { id = "readonly", role = "app_readonly_role", inherit = true, login = false, validity = "infinity", privileges = ["USAGE"], createrole = false },
      { id = "web", role = "app_writeweb_role", inherit = true, login = false, validity = "infinity", privileges = ["USAGE"], createrole = false },
      { id = "backoffice", role = "app_writebo_role", inherit = true, login = false, validity = "infinity", privileges = ["USAGE"], createrole = false },
      { id = "batch", role = "app_writebatch_role", inherit = true, login = false, validity = "infinity", privileges = ["USAGE"], createrole = false },
   ],

Appliquons les permissions strictement nécessaires pour chacun de ces rôles :

  db_grants = [
   # define grants for app_admin_role :
   # - access to all objects on database
       { object_type = "database", privileges = ["CREATE", "CONNECT", "TEMPORARY"], objects = [], role = "app_admin_role", owner_role = "root", grant_option = true },
       { object_type = "type", privileges = ["USAGE"], objects = [], role = "app_admin_role", owner_role = "root", grant_option = true },

   # define grants for app_readonly_role
   # - access to 'SELECT' on all tables
   # - access to 'SELECT' on all sequences
       { object_type = "database", privileges = ["CONNECT"], objects = [], role = "app_readonly_role", owner_role = "app_admin_role", grant_option = false },
       { object_type = "type", privileges = ["USAGE"], objects = [], role = "app_readonly_role", owner_role = "app_admin_role", grant_option = true },
       { object_type = "table", privileges = ["SELECT", "REFERENCES", "TRIGGER"], objects = [], role = "app_readonly_role", owner_role = "app_admin_role", grant_option = false },
       { object_type = "sequence", privileges = ["SELECT", "USAGE"], objects = [], role = "app_readonly_role", owner_role = "app_admin_role", grant_option = false },

   # define grants for app_writeweb_role
   # - access in Read/Write on tables "customer" & "basket"
   # - access in Read on table "Product"
       { object_type = "database", privileges = ["CONNECT"], objects = [], role = "app_writeweb_role", owner_role = "app_admin_role", grant_option = false },
       { object_type = "type", privileges = ["USAGE"], objects = [], role = "app_writeweb_role", owner_role = "app_admin_role", grant_option = true },
       { object_type = "table", privileges = ["SELECT", "REFERENCES", "TRIGGER", "INSERT", "UPDATE", "DELETE"], objects = ["customer", "basket"], role = "app_writeweb_role", owner_role = "app_admin_role", grant_option = false },
       { object_type = "table", privileges = ["SELECT", "REFERENCES", "TRIGGER"], objects = ["product"], role = "app_writeweb_role", owner_role = "app_admin_role", grant_option = false },
       { object_type = "sequence", privileges = ["SELECT", "USAGE"], objects = [], role = "app_writeweb_role", owner_role = "app_admin_role", grant_option = false },

   # define grants for app_writebo_role
   # - access in Read/Write on table "product"
   # - access in Read on table "customer" , "basket" & "stats"
       { object_type = "database", privileges = ["CONNECT"], objects = [], role = "app_writebo_role", owner_role = "app_admin_role", grant_option = false },
       { object_type = "type", privileges = ["USAGE"], objects = [], role = "app_writebo_role", owner_role = "app_admin_role", grant_option = true },
       { object_type = "table", privileges = ["SELECT", "REFERENCES", "TRIGGER", "INSERT", "UPDATE", "DELETE"], objects = ["product"], role = "app_writebo_role", owner_role = "app_admin_role", grant_option = false },
       { object_type = "table", privileges = ["SELECT", "REFERENCES", "TRIGGER"], objects = ["customer", "basket", "stats"], role = "app_writebo_role", owner_role = "app_admin_role", grant_option = false },
       { object_type = "sequence", privileges = ["SELECT", "USAGE"], objects = [], role = "app_writebo_role", owner_role = "app_admin_role", grant_option = false },

   # define grants for app_writebatch_role
   # - access in Read/Write on table "stats"
   # - access in Read on table "customer", "basket", "product"
   # - execute functions
       { object_type = "database", privileges = ["CONNECT"], objects = [], role = "app_writebatch_role", owner_role = "app_admin_role", grant_option = false },
       { object_type = "type", privileges = ["USAGE"], objects = [], role = "app_writebatch_role", owner_role = "app_admin_role", grant_option = true },
       { object_type = "table", privileges = ["SELECT", "REFERENCES", "TRIGGER", "INSERT", "UPDATE", "DELETE"], objects = ["stats"], role = "app_writebatch_role", owner_role = "app_admin_role", grant_option = false },
       { object_type = "table", privileges = ["SELECT", "REFERENCES", "TRIGGER"], objects = ["customer", "basket", "product"], role = "app_writebatch_role", owner_role = "app_admin_role", grant_option = false },
       { object_type = "sequence", privileges = ["SELECT", "USAGE"], objects = [], role = "app_writebatch_role", owner_role = "app_admin_role", grant_option = false },
       { object_type = "function", privileges = ["EXECUTE"], objects = [], role = "app_writebatch_role", owner_role = "app_admin_role", grant_option = false },

  ],

Notez que pour tous ces rôles, l’attribut owner_role a la valeur app_admin_role (et non le super-user) et que l’attribut grant_option vaut false.

Enfin, créons les users correspondant à chacun de ces rôles :

   db_users = [
      { name = "admin", inherit = true, login = true, membership = ["app_admin_role"], validity = "infinity", connection_limit = -1, createrole = true },
      { name = "web", inherit = true, login = true, membership = ["app_writeweb_role"], validity = "infinity", connection_limit = -1, createrole = false },
      { name = "backoffice", inherit = true, login = true, membership = ["app_writebo_role"], validity = "infinity", connection_limit = -1, createrole = false },
      { name = "batch", inherit = true, login = true, membership = ["app_writebatch_role"], validity = "infinity", connection_limit = -1, createrole = false },
      { name = "lemmy", inherit = true, login = true, membership = ["app_readonly_role"], validity = "2022-07-31 00:00:00+00", connection_limit = -1, createrole = false },
   ]

L’attribut createrole = false empêche l’utilisateur de créer un rôle.

Un utilisateur lemmy, ayant les droits “read” sur toutes les tables, a été créé avec une date d’expiration.

Et on a terminé !! Après l’apply, voici une partie des outputs :

affected_schema = "public"
created_roles = [
   "app_admin_role",
   "app_readonly_role",
   "app_writeweb_role",
   "app_writebo_role",
   "app_writebatch_role",
]
db_users = {
   "admin" = {
         "connect_command" = "psql -h myfullrdsexample.pandemonium.eu-west-3.rds.amazonaws.com -p 5432 -U admin -d mydatabase -W"
         "secret_arn" = "arn:aws:secretsmanager:eu-west-3:444444444444:secret:secret-kv-myfullrdsexample-admin-PFxvvF"
         "secret_name" = "secret-kv-myfullrdsexample-admin"
   }
   "backoffice" = {
         "connect_command" = "psql -h myfullrdsexample.pandemonium.eu-west-3.rds.amazonaws.com -p 5432 -U backoffice -d mydatabase -W"
         "secret_arn" = "arn:aws:secretsmanager:eu-west-3:444444444444:secret:secret-kv-myfullrdsexample-backoffice-cvPvLW"
         "secret_name" = "secret-kv-myfullrdsexample-backoffice"
   }
   "batch" = {
         "connect_command" = "psql -h myfullrdsexample.pandemonium.eu-west-3.rds.amazonaws.com -p 5432 -U batch -d mydatabase -W"
         "secret_arn" = "arn:aws:secretsmanager:eu-west-3:444444444444:secret:secret-kv-myfullrdsexample-batch-jrtS2V"
         "secret_name" = "secret-kv-myfullrdsexample-batch"
   }
   "lemmy" = {
         "connect_command" = "psql -h myfullrdsexample.pandemonium.eu-west-3.rds.amazonaws.com -p 5432 -U lemmy -d mydatabase -W"
         "secret_arn" = "arn:aws:secretsmanager:eu-west-3:444444444444:secret:secret-kv-myfullrdsexample-lemmy-g0ARmI"
         "secret_name" = "secret-kv-myfullrdsexample-lemmy"
   }
   "web" = {
            "connect_command" = "psql -h myfullrdsexample.pandemonium.eu-west-3.rds.amazonaws.com -p 5432 -U web -d mydatabase -W"
            "secret_arn" = "arn:aws:secretsmanager:eu-west-3:444444444444:secret:secret-kv-myfullrdsexample-web-PvmPYu"
            "secret_name" = "secret-kv-myfullrdsexample-web"
   }
}

Voici le résultat des tests :

• user “backoffice” :

• user “web” :

• user “batch” :

C’est donc exactement ce que l’on souhaitait.

Testons quelques instructions SQL d’élévation de privilèges depuis le user web :

psql -h myfullrdsexample.pandemonium.eu-west-3.rds.amazonaws.com -p 5432 -U web -d mydatabase -W
Password:
psql (13.5 (Ubuntu 13.5-2.pgdg20.04+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
mydatabase=> CREATE DATABASE exfiltration;
ERROR:  permission denied to create database

mydatabase=> CREATE SCHEMA exfiltration;
ERROR:  permission denied for database mydatabase

mydatabase=> CALL feed_stats(1);
ERROR:  permission denied for table stats
CONTEXT:  SQL statement "insert into stats (product,value) values (infos.ProductLabel,infos.totalAmount)"
PL/pgSQL function feed_stats(integer) line 18 at SQL statement

mydatabase=> CREATE ROLE dream SUPERUSER LOGIN PASSWORD 'theater';
ERROR:  must be superuser to create superusers

mydatabase=> CREATE ROLE suicidal CREATEDB LOGIN PASSWORD 'tendencies';
ERROR:  permission denied to create role

mydatabase=> CREATE ROLE marilyn LOGIN PASSWORD 'manson';
ERROR:  permission denied to create role

mydatabase=> CREATE ROLE slayer;
ERROR:  permission denied to create role

Ce qui est plutôt rassurant.

Conclusion

Dans cet exemple, si j’avais dû gérer manuellement la création des rôles, des permissions et des users, cela m’aurait pris un certain temps pour adapter les scripts SQL que j’ai amoncelés durant mes différentes expériences. D’autre part, j'aurais dû reporter les mots de passe dans AWS SecretsManager.

Enfin, ici j’ai créé 5 users. Si je devais le faire pour 20, voire plus, comme c’est possible dans certaines organisations, cela prendrait encore plus de temps.

Si on doit rajouter la gestion d’un process pour supprimer un user, ou invalider son accès, ou mettre à jour son mot de passe, ou étendre sa date d’expiration, c’est encore d’autres scripts qu’il faut maintenir.

Avec ce module, j’ai maintenant une méthode simple permettant d’appliquer tous les aspects de sécurité présentés dans le 1er article. Ce module s’inscrit parfaitement dans la mouvance “devops” en apportant aux développeurs ou aux ops, un moyen de sécuriser leurs bases de données.

Enfin, le système de post-processing playbook mis en œuvre permet d’adapter ce module à chaque contexte client, et par la même occasion, de stocker à un seul endroit le mot de passe, dans un endroit sécurisé de son choix. Bien que l’utilisation des null_resource de Terraform soit à proscrire, en écrivant des scripts shell ou python, vous ne prendrez pas un grand risque dans l’interopérabilité de l’exécution du code Terraform.

Pour terminer le tour d'horizon, retrouver dans le 4eme et dernier article, la mise en oeuvre  d'un système d'auditabilité, nécessaire dans certaines activités soumises aux réglementations. Ces entreprises doivent pouvoir prouver l'activité sur certaines applications critiques, et par conséquent sur leurs bases de données.

Stay Tuned,