gistfile1.txt

logstash patterns 
```
#%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h,cmd=%i,s=%c,e=%e

#     [message] =~ /(?i)password/ {
#     "disconnection:" in [message] {
#     "connection received:" in [message] {
#     "connection authorized:" in [message] {
#     "duration:" in [message] {
#     "statement:" in [message] {
#     "statement:" in [message] {
#     "prepare:" in [message] {
#     "checkpoint (starting|complete):" in [message] {
#     "no pg_hba.conf entry for " in [message] {
#     "password authentication failed" in [message] {
#     "permission denied" in [message] {
#     "could not receive data from WAL" in [message] {
#     "No space left on device" in [message] {
#     "shutting down" in [message] {
#     "database system is ready to accept connections" in [message] {
#     "remaining connection slots are reserved for non-replication" in [message] {
#     "server closed the connection unexpectedly" in [message] {

#%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h,cmd=%i,s=%c,e=%e
#2018-03-29 19:08:13 UTC [19148]: [8-1] user=example_user,db=example,app=psql,client=172.16.6.2,cmd=INSERT,s=5abd3986.4acc,e=00000
PGPREFIX %{TIMESTAMP_ISO8601:postgres_timestamp} %{WORD:postgres_tz} \[%{POSINT:postgres_pid}\]: \[%{POSINT:postgres_lineno}-1\] user=(%{DATA:postgres_user})?,db=(%{DATA:postgres_db})?,app=(%{DATA:postgre
s_app})?,client=(%{DATA:postgres_client})?,cmd=(%{DATA:postgres_cmd})?,s=(%{DATA:postgres_session_id})?,e=(%{DATA:postgres_sqlstate})?

#2018-03-29 19:08:13 UTC [19148]: [8-1] user=example_user,db=example,app=psql,client=172.16.6.2,cmd=INSERT,s=5abd3986.4acc,e=00000 LOG:
PGLOG %{PGPREFIX} %{DATA:postgresql_log_level}:

#duration: 20293.208 ms  statement: insert into hello values (1000006);
PGQUERY %{PGLOG}  duration: %{DATA:postgres_duration:float} ms  statement: %{GREEDYDATA:postgres_query}

#statement: SELECT CASE WHEN
PGSTATEMENT %{PGLOG}  statement: %{GREEDYDATA:postgres_query}

#duration: 100041.989 ms
PGDURATION %{PGLOG}  duration: %{DATA:postgres_duration:float} ms

#prepare: PREPARE zooplan as select pg_sleep(14);
PGPREPARE %{PGLOG}  prepare: %{GREEDYDATA:postgres_query}

#disconnection: session time: 0:00:00.006 user=monitor database=template1 host=127.0.0.1 port=39622
PGDISCONNECT %{PGLOG}  disconnection: session time: %{DATA:postgres_sessiontime} user=%{DATA} database=%{DATA} host=%{IPORHOST} port=%{NUMBER:postgres_client_port}

#connection received: host=127.0.0.1 port=39622
PGCONNECT %{PGLOG}  connection received: host=%{IPORHOST} port=%{NUMBER:postgres_client_port}

#everything else
PGMESSAGE %{PGLOG}  %{GREEDYDATA:postgres_message}

### put it all together, first the most structured ones, then plain messages like errors and stuff
POSTGRES %{PGQUERY}|%{PGPREPARE}|%{PGSTATEMENT}|%{PGDURATION}|%{PGCONNECT}|%{PGDISCONNECT}|%{PGMESSAGE}
```

logstash filter:
```
filter {
  if [type] == "postgresql" {

    grok {
      patterns_dir => ["/etc/logstash/conf.d/patterns"]
      match => { "message" => "%{POSTGRES}" }
    }

    # drop monitoring queries
    if [postgres_user] =~ /(?i)unknown|(?i)monitor|(?i)postgres/ {
      drop {}
    }
    # drop insert, update, delete, alter
    else if [postgres_cmd] =~ /(?i)insert|(?i)update|(?i)delete|(?i)alter|(?i)authentication/ {
      drop {}
    }
    # drop insert, update, delete, alter
    else if [postgres_query] =~ /(?i)insert|(?i)update|(?i)delete|(?i)alter/ {
      drop {}
    }

    date {
      match  => [ "postgres_timestamp", "yyyy-MM-dd HH:mm:ss"]
      timezone => "UTC"
    }
  }
  ```
  
  now the concern is queries may have sensitive data wrt GDPR etc, and that creates problems logging to a centralized logging system.
  i found this extension awesome (normalize the queries) which works the best when insert/updates are not parameterized/prepared.
  
  but to my bad luck, i thought of trying to use this in an logstash plugin, but looks like jruby (logstash jruby) does not compile c extensions.
  
  now the main part:
  can you help me know if it is possible to hook this extension in the postgres logging facility and anonymize the params in the log queries.
  
  just asking, feel free to ignore.