Created
March 31, 2018 09:37
-
-
Save cabecada/dd765a30f6946fdbf0bec0eb31fba047 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment