Forums PostgreSQL.fr

guk92

Membre

[RESOLU] Enregistrer toutes les actions liés aux comptes utilisateur

Bonjour,

J'ai connaissance de "pg_stat_activity", mais je souhaiterais savoir s'il est possible d'enregistrer dans une table de log :
- le compte utilisateur
- l'adresse IP de la machine via laquelle a été exécutée l'action
- le datetime d'exécution
- la requête qui a été exécutée

... à chaque fois qu'un utilisateur exécute une requête. Est-ce que quelque chose est prévue pour ce type d'opération ou faut-t-il utiliser une couche d'abstraction au niveau logiciel pour effectuer cette opération soi-même ?

Merci

Dernière modification par guk92 (25/10/2013 11:34:08)

arthurr

Membre

Re : [RESOLU] Enregistrer toutes les actions liés aux comptes utilisateur

bonjour,

http://docs.postgresql.fr/9.3/runtime-c … gging.html

vous pouvez paramétrer postgresql avec log_statement = 'all' + log_connection = on (attention aux perfs !).
vous devez modifier également log_line_prefix pour avoir les infos que vous voulez (IP, date, base, utilisateur ...)
il est églament possible de logger au format csv : chapitre 18.8.4. "Utiliser les journaux au format CSV"

guk92

Membre

Re : [RESOLU] Enregistrer toutes les actions liés aux comptes utilisateur

Bonjour,

Je vous remercie pour votre réponse Arthurr. Ce que je souhaiterais, c'est enregistrer de façon continu les logs dans une table de la base.

Du coup j'aurais quelques questions :
1. J'ai vu qu'il existait plusieurs façon d'enregistrer le log (stderr, csvlog, syslog, and eventlog). Sauf qu'on ne dit pas exactement à quoi correspondent ces 4 types d'enregistrement dans la doc (même si on se doute bien que pour csvlog il va enregistrer dans un fichier au format CSV).
    a. A quoi correspondent chacun de ces 4 types d'enregistrement ?  Lesquels enregistrent sous forme de fichier, et d'autres directement dans la table d'une base ?
    b. N'y a-t-il pas un moyen d'enregistrer le log dans une base à part comme "base_pg_des_logs" avec une table "table_des_logs" ? Si oui comment faire ?

2. Je suis conscient pour les ressources (perfs) que cela demande, mais ce qui m'inquiète c'est surtout l'enregistrement des logs de mes scripts d'automatisation journalière. Du coup est-t-il possible d'indiquer dans postgresql.conf le nom des comptes dont il ne faut pas enregistrer les logs ? (ex: le compte admin postgres).
    a. Si oui, comment l'indiquer dans le postgresql.conf ?
    b. Si non, est-t-il possible d'installer 2 SGBDR PostgreSQL sur la même machine ? (le premier aura pour but d'effectuer les traitements journaliers, le second récupère les résultats du premier serveur pour servir de consultation + enregistrement des logs utilisateurs).

Voici le contenu de mon postgresql.conf, partie "Error Reporting and Logging" (aucune modification apporté pour le moment):

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

log_destination = 'stderr'        # Valid values are combinations of
                    # stderr, csvlog, syslog, and eventlog,
                    # depending on platform.  csvlog
                    # requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on        # Enable capturing of stderr and csvlog
                    # into log files. Required to be on for
                    # csvlogs.
                    # (change requires restart)

# These are only used if logging_collector is on:
#log_directory = 'pg_log'        # directory where log files are written,
                    # can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'    # log file name pattern,
                    # can include strftime() escapes
#log_file_mode = 0600            # creation mode for log files,
                    # begin with 0 to use octal notation
#log_truncate_on_rotation = off        # If on, an existing log file with the
                    # same name as the new log file will be
                    # truncated rather than appended to.
                    # But such truncation only occurs on
                    # time-driven rotation, not on restarts
                    # or size-driven rotation.  Default is
                    # off, meaning append to existing files
                    # in all cases.
#log_rotation_age = 1d            # Automatic rotation of logfiles will
                    # happen after that time.  0 disables.
#log_rotation_size = 10MB        # Automatic rotation of logfiles will
                    # happen after that much log output.
                    # 0 disables.

# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

# This is only relevant when logging to eventlog (win32):
#event_source = 'PostgreSQL'

# - When to Log -

#client_min_messages = notice        # values in order of decreasing detail:
                    #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                    #   log
                    #   notice
                    #   warning
                    #   error

#log_min_messages = warning        # values in order of decreasing detail:
                    #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                    #   info
                    #   notice
                    #   warning
                    #   error
                    #   log
                    #   fatal
                    #   panic

#log_min_error_statement = error    # values in order of decreasing detail:
                     #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                     #   info
                    #   notice
                    #   warning
                    #   error
                    #   log
                    #   fatal
                    #   panic (effectively off)

#log_min_duration_statement = -1    # -1 is disabled, 0 logs all statements
                    # and their durations, > 0 logs only
                    # statements running at least this number
                    # of milliseconds


# - What to Log -

#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default        # terse, default, or verbose messages
#log_hostname = off
log_line_prefix = '%t '            # special values:
                    #   %a = application name
                    #   %u = user name
                    #   %d = database name
                    #   %r = remote host and port
                    #   %h = remote host
                    #   %p = process ID
                    #   %t = timestamp without milliseconds
                    #   %m = timestamp with milliseconds
                    #   %i = command tag
                    #   %e = SQL state
                    #   %c = session ID
                    #   %l = session line number
                    #   %s = session start timestamp
                    #   %v = virtual transaction ID
                    #   %x = transaction ID (0 if none)
                    #   %q = stop here in non-session
                    #        processes
                    #   %% = '%'
                    # e.g. '<%u%%%d> '
#log_lock_waits = off            # log lock waits >= deadlock_timeout
#log_statement = 'none'            # none, ddl, mod, all
#log_temp_files = -1            # log temporary files equal or larger
                    # than the specified size in kilobytes;
                    # -1 disables, 0 logs all temp files
log_timezone = 'Europe/Brussels'

Je vous remercie, cordialement,

kenrio

Membre

Re : [RESOLU] Enregistrer toutes les actions liés aux comptes utilisateur

ça serait moi, je monterai un serveur syslog et j'enverrais les logs la bas et c'est ce serveur qui ferait les traitements que vous souhaitez.

mettre 2 serveurs pg sur la meme machine pour traiter les logs c'est pas viable dans le future ou alors c'est une mini prod

gleu

Administrateur

Re : [RESOLU] Enregistrer toutes les actions liés aux comptes utilisateur

1A. Tous sous forme de fichiers.

1B. Non. Le seul moyen simple est d'avoir un job cron qui intègre le contenu d'un fichier de traces dans une table. Et encore, simple, pas si sûr.

2A. Non. Il est possible de créer une extension qui le fait mais ça demande des connaissances en langage C.

2B. Oui.

---

Guillaume.

guk92

Membre

Re : [RESOLU] Enregistrer toutes les actions liés aux comptes utilisateur

Merci pour les réponses gleu

3. Si stderr, csvlog, syslog, et eventlog sont tous des fichiers, est-ce que c'est juste la mise en forme du texte à l'intérieur du fichier qui change ? Quel serait votre préférence et pourquoi ?

4. Est-t-il possible de choisir plusieurs de ces formats comme ceci : log_destination = 'stderr, syslog, csvlog' ?

5. J'ai vu que le contenu du dossier pg_log contenait déjà des des fichiers d'extension .log, donc si je met juste log_destination = 'csvlog' aurais-je des fichiers avec le nom : postgresql-%Y-%m-%d_%H%M%S.csv ? Ou bien dois-je paramétrer l'attribut log_filename pour l'extension ? Oui parce que comment faire la différence entre stderr et csvlog s'ils ont tout les deux la même extension et le même nom de fichier (ce qui n'est pas possible) ?

6. Pensez-vous que pour ce que je souhaite faire ("enregistrer de façon continu les logs dans une table de la base"), mieux vaut passer par un fichier CSV (csvlog) puis charger dans une base de la table à partir d'une tâche planifié ?

7. Puis-je définir le log_line_prefix comme ceci : log_line_prefix = '%t, %a, %u, %d, %r, %i, %e' . Aussi lequel de ces lettre représente la date d'exécution et la durée de la requête ?

8. Comment faire pour mettre un sujet en statut "Résolu" dans le forum ?

PS: Je sais qu'il n'est pas bien d'avoir deux PosgtreSQL sur la même machine, mais c'est au cas où je n'aurais pas le choix (et c'est une "mini prod").

Je vous remercie

gleu

Administrateur

Re : [RESOLU] Enregistrer toutes les actions liés aux comptes utilisateur

3. Oui. stderr, mais c'est purement personnel (ie, subjectif).

4. Non, et ça n'aurait aucun sens. Stocker deux fois les mêmes traces, c'est juste pour perdre en performances ?

5. Il va créer des .csv.

6. Oui.

7. Oui. Rien (il n'est pas possible d'avoir la date d'exécution, vous n'avez que la date de la trace ; quant à la durée, il faut configurer le log_min_duration_statement).

8. On ne peut pas (à moins de changer le titre du thread).

---

Guillaume.

guk92

Membre

Re : [RESOLU] Enregistrer toutes les actions liés aux comptes utilisateur

Bonjour,

Merci pour vos réponses gleu.

J'ai donc modifié le fichier postgresql.conf en indiquant les paramètres suivants :

log_destination = 'csvlog'
logging_collector = on # Celui-ci était déjà à ON
log_connections = on
log_line_prefix = '%t, %a, %u, %d, %r, %i, %e'
log_statement = 'all'

La première chose que je remarque est que le fichier enregistre toutes les actions possibles, c'est très confus. D'après la documentation :

log_statement (enum)

Controls which SQL statements are logged. Valid values are none (off), ddl, mod, and all (all statements). ddl logs all data definition statements, such as CREATE, ALTER, and DROP statements. mod logs all ddl statements, plus data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE, EXECUTE, and EXPLAIN ANALYZE statements are also logged if their contained command is of an appropriate type. For clients using extended query protocol, logging occurs when an Execute message is received, and values of the Bind parameters are included (with any embedded single-quote marks doubled).

9. Serait-t-il possible de le limiter aux requêtes de type SELECT + UPDATE + INSERT ? Si oui comment car je voudrais juste me limiter aux requêtes de type DML (Data Manipulation Language -> SELECT | INSERT | UPDATE | DELETE).

10. Le CSV généré a pour séparateur une virgule, comment préciser que je veux un "point-vigule" à la place d'une virgule ? (ce sera plus pratique pour ouvrir avec des outils comme Excel).

PS: J'ai remarqué que le paramètre log_line_prefix n'est pas pris en compte lorsqu'on choisit log_destination = 'csvlog', d'après la doc, par défaut avec csvlog :

CREATE TABLE postgres_log
(
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text,
  PRIMARY KEY (session_id, session_line_num)
);

Je vous remercie

gleu

Administrateur

Re : [RESOLU] Enregistrer toutes les actions liés aux comptes utilisateur

9. Non.

10. Vous ne pouvez pas. Le format CSV (Comma Separated Value) a une séparation par virgule.

11? En format CSV, vous avez déjà tout. Rien ne sert d'indiquer le log_line_prefix.

---

Guillaume.

guk92

Membre

Re : [RESOLU] Enregistrer toutes les actions liés aux comptes utilisateur

Bonjour,

C'est dommage qu'on ne puisse pas choisir son caractère de "séparation" pour le csvlog, heureusement que la commande COPY permet de définir le type de séparateur lors d'un import/export.

En tout cas je vous remercie pour toutes vos réponses. Je considère ce topic comme résolu et espère qu'il apportera des réponses aux personnes qui se posent les mêmes questions.

PS: Pas de question 11 , c'était juste une petite remarque. D'ailleurs j'aurais deux nouvelles remarques :
* Dans le dossier pg_log j'ai maintenant un fichier "postgresql-2013-10-25_000000.csv" et un fichier "postgresql-2013-10-25_000000.log", et pourtant j'ai bien défini "log_destination = 'csvlog'" et pas "log_destination = 'stderr, csvlog'". Le fichier d'extension ".log" reste vide donc pas de double enregistrement. Aussi, lors d'un redémarrage du serveur, un nouveau fichier ".log" est créé 3-4 secondes avant un nouveau fichier ".csv".
PostgreSQL version: 9.2

* J'ai testé "log_statement = 'ddl'" et "log_statement = 'mod'", rien à faire, cela n'enregistre pas les SELECT, il faut passer par "log_statement = 'all'". Dommage que l'option "dml" n'existe pas !

Cordialement,