Administration PostgreSQL

• Compte administrateur postgres
  • Les bases de données postgres disposent d'un catalogue système qui permet de définir tout l'environnement du système.
  • Les noms des tables de ce catalogue sont toujours préfixées par pg_
  • Tous les objets manipulables avec postgres sont associés à une table : pg_sequence, pg_type, pg_trigger, pg_proc, ...
• Structure de la table pg_database

  postgres=# \d pg_database
             Table "pg_catalog.pg_database"
  Column     |   Type    | Collation | Nullable | Default 
  ---------------+-----------+-----------+----------+---------
  oid           | oid       |           | not null | 
  datname       | name      |           | not null | 
  datdba        | oid       |           | not null | 
  encoding      | integer   |           | not null | 
  datcollate    | name      |           | not null | 
  datctype      | name      |           | not null | 
  datistemplate | boolean   |           | not null | 
  datallowconn  | boolean   |           | not null | 
  datconnlimit  | integer   |           | not null | 
  datlastsysoid | oid       |           | not null | 
  datfrozenxid  | xid       |           | not null | 
  datminmxid    | xid       |           | not null | 
  dattablespace | oid       |           | not null | 
  datacl        | aclitem[] |           |          | 
  Indexes:
  "pg_database_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
  "pg_database_datname_index" UNIQUE CONSTRAINT, btree (datname), tablespace "pg_global"
  Tablespace: "pg_global"

• Un ensembles de vues décrivant le système sont également disponibles

  postgres=# \d pg_user;
                      View "pg_catalog.pg_user"
  Column    |           Type           | Collation | Nullable | Default 
  --------------+--------------------------+-----------+----------+---------
  usename      | name                     |           |          | 
  usesysid     | oid                      |           |          | 
  usecreatedb  | boolean                  |           |          | 
  usesuper     | boolean                  |           |          | 
  userepl      | boolean                  |           |          | 
  usebypassrls | boolean                  |           |          | 
  passwd       | text                     |           |          | 
  valuntil     | timestamp with time zone |           |          | 
  useconfig    | text[]                   | C         |          | 

• la gestion de l'origine des connexions est configurée dans un fichier pg_hba.conf (Host Based Authentication)
  • distingue les connexions locales ou TCP/IP (avec ou sans cryptage)
  • permet de configurer l'accès pour tous les utilisateurs ou pour un utilisateur ou un rôle particulier ou encore un ensemble d'utilisateurs reconnu par une expression rationnelle
  • dans le cas des connexions TCP/IP, il est possible de préciser une adresse ou un ensemble d'adresses, etc.
  • Pour plus de détails : https://www.postgresql.org/docs/16/auth-pg-hba-conf.html
• Création des bases de données et templates
  • Deux bases de données existent par défaut : template0 et template1
  • La création d'une nouvelle base de données (CREATE DATABASE dbname) consiste à cloner template1
  • Si des objets ont été ajoutés dans template1, ils sont clonés dans la nouvelle base de données
  • template0 correspond à la version fondamentale de template1 et peu être cloné explicitement ainsi

    CREATE DATABASE dbname TEMPLATE template0;

• Spécificités de l'organisation des bases de données postgres
  • les bases de données sont structurés en schemas qui contiennent les tables (entre autres)

    CREATE SCHEMA sampleSchema;

  • permet d'avoir plusieurs objets de même nom dans une base de données

    CREATE TABLE sampleSchema.maTable ( colname type );

  • structure la base de données en groupe cohérent plus facile à gérer
  • Par défaut, on travaille dans le schema public

    CREATE TABLE maTable( col type);
    -- équivalent à CREATE TABLE public.maTable( col type);

  • Les noms des objets postgres non préfixés par un nom de schéma sont recherchés dans le schema public.
  • Par défaut le schéma public accorde le privilège USAGE à tous les utilisateurs !
  • Pour la plupart des bases de données, il convient de restreindre les droits sur les schemas publics :

    REVOKE ALL ON DATABASE newDatabase FROM PUBLIC;
    GRANT ALL ON DATABASE newDatabase TO newDatabaseOwner;
    REVOKE CREATE ON SCHEMA public FROM PUBLIC;