►
From YouTube: PostgreSQL Network Filter for EnvoyProxy - Fabrízio de Royes Mello, Christoph Pakulski
Description
PostgreSQL Network Filter for EnvoyProxy - Fabrízio de Royes Mello, Christoph Pakulski
How do you monitor Postgres? What information can you get out of it, and to what degree does this information help to troubleshoot operational issues? What if you want/need to log all the queries? That may bring heavy trafficked databases down. At OnGres we’re obsessed with improving PostgreSQL’s observability. So we worked together with Tetrate folks on an Envoy’s Network Filter extension for PostgreSQL, to provide and extend observability of the traffic inout a cluster infrastructure. This extension is public and open source. You can use it anywhere you use Envoy. It allows you to capture automated metrics and to debug network traffic. This talk will be a technical deep-dive into PostgreSQL’s protocol decoding, Envoy proxy filters and will cover all the capabilities of the tool and its usage and deployment in any environment.
A
A
So,
let's
start
with
you
know,
for
with
android
proxy,
this
presentation
will
have
several
parts,
so
in
the
first
part
we
will
look
towards
posgress
protocol
from
envoy
point
of
view.
So
this
is
a
simple
connection
model
at
the
goal
you
know
is
connecting
a
client
to
the
farm
of
this
of
server.
So
on
the
left
side,
we
have
a
typical
client
there's
the
application,
and
it
really
wants
to
connect
to
a
databases
and
postgres
requires
a
tcp
connection
between
the
client
and
the
server.
A
So
you
know,
and
that's
what
envoy
does
if
you
look
here,
if
you
look
at
this
picture,
it's
really
simple
configuration
it's
just
a
tcp
proxy
and
it
provides
the
connectivity
between
the
client
and
the
server.
So
we
can
use
extensive
backend
features
which
are
provided
by
by
envoy.
So
we
can
do
a
load
balancing
on
the
back
and
we
can
do
a
health
checking.
We
can
detect
that
one
of
the
nodes
are
behaving
in
a
really
weird
way.
So
this
is
something
we
call
outlier
detector
and
it
works
so
clients
we
we
achieved.
A
What
do
you
want
that
we
wanted
to
connect
client
and
the
server?
You
can
also
get
some
statistics,
but
on
a
very
gross
level,
so
you
will
only
see
that
the
client
connected
to
a
database
and-
and
that's
that's
all-
I-
you
won't
be
able
to
see
what
was
going
inside
of
the
of
that
session.
So
tcp
tcp
stats
will
tell
you
how
many
tcp
were
open.
A
Tcp
sessions
were
open,
how
many
bytes
were
exchanged,
but
also
an
access
log
with
retail
that
you
know
pretty
much
describes
similar
things,
so
you
you
know
that
the
client
talked
to
database,
but
you
really
know
you
don't
really
know
what
was
talking
about.
So
in
order
to
know
what
was
going
on
inside
of
the
session,
you
need
one
extra
component
in
this
picture,
and
this
is
this
is
a
postgres
filter.
A
One
thing
you
have
to
mention
right
now
that,
in
order
to
achieve
all
these
things,
we're
talking
about
postgres
filter
needs
access
to
a
clear
text
protocol.
So
it
cannot
be
encrypted
we'll
talk
about
later
on
what
we
do
when
it's
encrypted.
But
when
I
talk
about
you
know
this
assume
that
it
has
a
clear
text.
A
A
A
So
it's
not
as
a
one
long-lived
tcp
session,
but
postgres
filter
will
know
what
was
going
on
inside.
So
one
thing
I
have
to
mention
here
that
the
sequence
of
posgress,
filter
and
tcp
proxy
filter
is
established.
Every
time.
Client
establish
a
connection
to
a
server
so
like
I
said
it
is
a
long
lift
and
when
it
ends
that
two
filters
are
destroyed,
that
would
be
next
slide.
Please.
A
Okay,
so
let's
see
what's
going
on
inside
so
on
the
tcp
level
and
the
data
delivery,
they
you
know
tcp
windowing
mechanism
and
also
buffering
on
the
network
cars.
They
really
completely,
not
aware,
what's
in
that,
their
postgres
messages
pass
on
top
of
these,
so
filter
receives
just
chunks
of
data,
and
it
has
to
make
sense
of
this
so
before
we
can
before
the
filter
can
start
processing
it.
A
So
it
always
starts
in
the
initial
state
initially
and
it
stays
in
the
state
as
long
as
the
coder
so-called
likes
the
messages.
So
I'm
saying
like
so
it's
able
to
find
the
boundary
when
the
one
message
ends
and
then
when
the
next
starts
and
it
can
kind
of
make
sense,
what's
passing
between
client
and
the
server
and
as
long
as
it
the
decoder
likely
stays
in
in
sync
state.
A
There
are
two
states.
There
are
two
situations
when
the
encoder
can
leave
that
state.
So
the
first
one
state
is
normal
state.
I'm
saying:
there's
no
error
and
it's
called
an
encrypted
state
and
it
happens
when
client
and
the
server
negotiate
an
encrypted
session
and
basically
it's
encrypted
and
failed
to
say,
hey
girls,
but
it's
nothing.
I
can
do
I
and
it's
that
basically
starts
passing
this
without
looking
inside
without
doing
any
deep
inspection.
A
So
the
other
situation
when
the
filter
can
live
in
sync
state
is
when
something
unusual
happens.
So
either
there
was
a
corruption
and
the
decoder
says
you
know.
I
don't
really
know.
What's
going
on
here
and
and
and
say
you
know,
I
better
start
ignoring
those
messages.
The
other
situation
is
when
it
can
happen.
A
Is
that
when
administrator
configures
everything
for
postgres,
so
there
will
be
a
postgres
filter,
tcp,
filter,
configures
everything
but
starts
passing
the
traffic,
which
is
not
really
postgres,
and
the
biggest
danger
here
is
that
the
filter
really
looks
inside
of
the
postgres
mesh.
That's
header.
There
is
a
four
byte
fields
which
is
which
indicates
the
length
of
the
message
and,
as
I
said
before,
before
we
can
start
a
filter
can
start
acting
upon
that
message.
It
has
to
keep
it
in
a
memory.
A
A
This
is
sql
database,
so
you
know
it.
There
is
a
an
sql
query.
Passing
in
the
messages
could
be
quite
complex
and
really
only
database
and
sql.
Parser
really
understands
this.
So
metadata
is
the
way
of
describing
the
quite
complex
sql
query
using
just
a
few
labels,
and
those
labels
might
be
whether
this
sql,
you
know
query,
is
read
operation
right
operation
on
which
database
on
which
table
just
using
three
or
four
labels.
A
We
able
to
describe
it
in
a
concise
way
what
this
operation
is
about
and
that
metadata
is
attached
to
a
request
and
it
travels
as
data
travel.
It
travels
with
data
as
it
passes
different
filters.
So
if
we
put
another
filter
like
arbuck,
we
can
program
our
bug
to
reject
or
stop
certain
operations.
You
know
from
being
said,
so
you
may
program,
it
would
be.
A
good
simple
example
would
be
you
can
program
it
that
it
only
routes,
reads,
operation
on
a
specific
table
and
stop
all
the
right
operations
right
at
this
moment.
A
B
So,
thank
you
christopher
now.
Let's
talk
a
little
bit
about
postgresql
here.
The
idea
is
not
to
talk
about
a
lot
of
great
features,
but
give
you
a
clear
picture
of
cohen
stats
of
development
side
of
posters.
You
know
so
postgres
has
more
than
30
years
of
development.
It's
fully
open
sources
since
the
beginning
and
we
have
a
postgres
license.
It's
a
specific
license
based
on
bsd
license
is
totally
driven
by
community.
I
mean
driven
by
postgresql
global
development
group.
There
are
no
single
single
company
driving
the
development.
B
If
you
see
in
this
picture
a
lot
of
great
use
cases-
apple,
skype,
instagram,
twitch,
spotify
imdb-
and
there
are
a
lot
of
missing
other
great
missing
user
cases
in
these
images
like
lifty,
tripadvisor,
ching,
git,
lab
atlasian
and
a
lot
of
government
in
different
scales
use
posters
today
you
know
so
it's
a
great
great
open
source
database.
We
often
call
the
most
advanced
open
source
database
in
the
world
on
the
postgres
community
society.
B
B
So
since
the
7.5
version,
it's
the
same
version
of
the
protocol
and
the
server
still
supports
the
protocol,
the
first
version
of
the
protocol
now
so
there
are
a
lot
of
other
delivered
projects
delivered
postgres
projects
that
can
benefit
of
this
envoy
network
filter,
I
can
say:
head
shift
from
amazon
was
built
on
top
of
postgres
many
years
ago.
Aurora
rds
yoga
bites
db,
cockroach
db
greenplan,
and
there
are
a
lot
of
projects
out
there
that
implement
the
postgresql
format.
B
So
you
you
can
benefit
for
this
envoy
network
filter
so
about
the
protocol.
There
are
a
name
called
phoebe
frame
format.
Phoebe
is
a
common
term
in
posters
world
that
means
front
end
and
back-end.
It's
same
like
a
client
and
server
are
upstream
downstream.
You
know,
and
basically
we
have
two
types
of
packages
that
traffic
between
the
client
and
the
server.
There
are
a
startup
package
in
the
regular
package
package
and
there
they
start
the
package.
B
There
are
three
fields.
The
first
field
is
the
the
length
of
the
message.
The
second
field
is
the
protocol
version
and
there
are
a
payload
with
other
needed
information
with
parameters
for
start.
The
connection
in
the
regular
package
we
have
first
field
is
a
tag
which
is
one
byte,
one
character,
the
length
of
the
message
and
the
payload
with
the
necessary
data
about
the
the
message-
and
there
are
a
special
starter
package-
called
ssl
negotiation.
B
B
The
message
for
for
a
new
connection
on
the
left
side
is
for
a
unencrypted
connection
and
the
right
side
is
for
encrypted
connection.
So
here
we
send
a
startup
package
server
response.
Without
a
request:
it's
okay
and
optionally.
You
can
send
a
password
and
server
response
with
a
lot
of
parameter
status,
back-end
key
data
and
finish
reading
for
query,
that
is
send,
say
to
client,
hey,
client,
I'm
waiting
for
you
and
you
can
send
the
next
command.
B
A
query
appears
message
depending
of
simple
or
extended
protocol,
and
the
difference
when
we
start
in
encrypted
connection
is
because
we
send
a
ssl
negotiation.
It's
a
dummy,
the
package
and
the
server
will
response,
yes
or
no.
If
you,
if
your
server
is
able
to
start
at
ls,
if
you
have
config,
if
you
have
the
enough
configuration
certificates
and
wherever
and
if
we,
the
response
is
s
we
will
have,
we
will
start
the
ssl
handshake
and
after
that
the
message
flow
will
be
the
same.
B
It
has
an
encrypted
connection,
you
know-
and
here
it's
other
other
parts
of
the
protocol
when
we
negotiate
with
the
server
to,
for
example,
run
a
simple
query:
okay
in
postgres
wire
protocol.
Basically,
there
are
two
different
ways
to
interact
with
the
server.
There
are
simple
protocol
and
the
extended
protocol
that
was
introduced
introduced
in
version
3.0
70
years
ago.
Okay,
here,
I
will
just
explain
the
simple
protocol,
because
the
idea
will
be
the
same
for
extended.
B
B
B
B
B
Okay,
oh
so,
let's
start
with
the
demonstration
part
of
the
the
presentation-
and
you
expect
some
result
like
this-
we
are
able
to
create
to
some
dashboards
in
grapherna
to
get
the
metrics
and
this
image
you
have
several
graphics,
interesting,
graphics.
Without
touch
to
the
postgres.
I
mean
zero
configuration
on
poster
site.
B
Just
by
decoding
the
wire
format.
We
can
expose
those
metrics
on
voice
side.
It
will
create
will
not
create
a
lot
of
overhead
on
post
the
side
because
another
way
to
to
get
this
information
running
some
queries
to
against
the
postgres
server
requirement
is
very
simple
for
this
demonstration:
just
git
darker
and
darker
compost.
B
You
know
the
architecture
is
very
simple.
There
are
five
containers
here
and
there
are
a
special
container
that
generates
some
trafficking.
I
mean
create
some
dummy
workload.
We
are
using
here
some
house-made
descripts
and
the
pg
bench
it's
up
to
postgres
2
for
benchmark
and
those
the
this
traffic
is
sent
to
envoy
and
then
voice
send
the
to
another
container,
with
postgres
and
promoters
to
grab
to
scrap
the
metrics
from
envoy
and
graphene
to
collect,
to
expose
metrics
in
to
create
some
dashboards.
B
B
I
mean
this
sql
statement
and
the
back-end
response
with
the
whole
description
and
after
that,
several
data
holes
sending
back
the
data
to
the
client
no
and
the
here
there
are
a
lot
of,
and
another
important
important
thing
is
common
complete
here.
This
is
the
the
the
tricky
that
we
did
for
exposing
matrices
drilling
down.
B
Select
the
different
kind
of
statement
drained
off
for
different
kind
of
statement,
because
the
postgres
response
at
the
end
of
the
the
data
that
data
data
row
is
streaming,
this
tag
for
the
command,
and
here
what
we
are
doing
on
the
invoice
side
is
increase
a
specific
metric
about
select
statement
when
the
command
completes
arrive,
pass
through
the
the
network
filter.
You
know
the
other
important
thing
is
here
here
we
have
a
script
to
generate
some
traffic.
It's
very
very
simple:
we
do
a
lot
of
random
to
run
some
benchmarks
and
run
within
different
protocols.
B
You
know
a
simple
extended
prepared,
so
we
can
generate
a
lot
of
different
trafficking
in
this
small
demonstration
to
generate
some
graph.
Here
is
our
graphene
running
and
we
have
a
special
dashboard
with
all
graphs
about
the
traffic
tps
transactions
per
seconds.
Hidden
and
righteous
different
type
of
statements,
front
end
and
back
end
messages
and
number
of
sessions
per
second.
A
So
so,
let's,
let's
let's
talk
about
it.
What
what
we
plan
for
the
future,
so
we
want
to
do
a
few
things
and
some
of
them
are
already
under
active
developments.
The
first
one
is
a
necessary
termination
and
we
said
that
in
order
to
produce
all
those
statistics,
we
need
an
access
to
to
clear
text
packet.
But
what
happens
if
you
have
a
requirement
that
the
client
has
to
you
know
travel.
A
It
traverse
a
certain
portion
of
your
network
in
an
encrypted
fashion,
so
we
want
to
terminate
that's
accelerated
at
the
end
level,
so
traffic
between
the
client
and
if
it's
going
to
be
encrypt
and
then
everyone
can
actually
look
what's
inside
of
the
packet.
So
it's
under
active
development.
We
also
want
to
have
a
better
result:
parsing
the
current
parts
we
use,
it's
not
sufficient
in
all
cases,
so
we
want
to
switch
to
a
better
one.
A
Like
I
said
before,
in
fabrizio
was
demonstrating
here,
we
can
provide
all
different
statistics
in
an
aggregated
way,
but
they
are
also
a
requirement
that
if
some
clients
are
using
one
database
and
the
other
clients
are
using
the
other,
but
they
shall
network.
We
want
to
basically
provide
the
statistics
per
database.
So
when
we
talk
to
promiscuous
and
we
extract
data,
we
just
want
to
somehow
divide
it
and
say
that's
for
database
a
that's
what
database
b
and
you
also
want
to
maybe
introduce
a
routing
based
on
query
and
that's.
A
I
said
when
I
was
talking
about
the
metadata
that
metadata
can
be
used
later
on
during
the
processing.
Maybe
you
want
to
route
some
queries
to
a
certain
host
because
it's
a
right
operation
and
maybe
a
different
host,
because
it's
a
read
operation
so,
what's
going
on
in
a
community,
so
there
is
a
dedicated
envoy
slack.
There
is
a
bunch
of
channels
for
development
users
and
there
is
one
very
specific
dedicated
to
envoy
sorry
to
postgres
development.
So
there
is
a
hash
here
and
all
the
issues
which
are
raised
by
users.
A
B
A
B
A
B
A
B
B
A
It
popped
up
a
few
times,
but
we
didn't
make
any
find
you
know
concrete
decision
and
but
but
but
but
anyway
maintains
some.
You
know
open
session
on
the
back
end,
so
maybe
we
can
leverage
that.
Definitely
we
have
to
look
at
the
you
know
very
technicalities
how
we
can
leverage
things
which
already
exist
yeah.
So
there
is
a
question
about
the
cockroachdb,
so
all
the
pro
all
the
databases
which
support
wire
protocol
which
is
compatible
with
postgres.
You
can
benefit
from
this
filter.