Support for AcraServer integration with Patroni HA

kumar1202 commented 2 years ago

Hey folks, feature request/question here.

We are currently using patroni as an HA solution for PostgreSQL and want to use Acra as an encryption/decryption DB proxy for on-the-fly transparent data encryption.

In Patroni, a. HAproxy connects with multiple postgres nodes, based on a leader-replica architecture using raft.

Is it possible to integrate AcraServer over the same patroni's HAProxy, so that we can introduce AcraServer without sacrificing our DB's latency instead of a separate AcraServer instance?

Lagovas commented 2 years ago

We have a demo with docker-compose scripts that deploys Acra with HAProxy - https://github.com/cossacklabs/acra-balancer-demo. Acra works as a proxy like PgBouncer/PgPool over DB-specific wire-protocol and provides additional transparent features. And has similar requirements like transactions should be proxied into the same node because it relies on connection session.

But when you will try, you should run docker-compose files with ACRA_DOCKER_IMAGE_TAG=0.90.0 environment variable value that specifies using Acra version 0.90.0 which was tested in this demo. It uses extra components AcraConnector and AcraWebConfig that were removed in next releases.

kumar1202 commented 2 years ago

Thanks for the response, I could get the examples to work. Currently I am integrating AcraServer with a dummy app and patroni cluster. But the app(Jmeter load test client) doesn't support SSL currently.

How do I install Acra's version 0.90.0 on an Ubuntu machine, didn't find any guides on the documentation to install older releases.

Lagovas commented 2 years ago

We publish only latest versions of packages to our ubuntu/debian repositories. But you can easily generate .deb package from sources for required version:

git clone https://github.com/cossacklabs/acra.git
cd acra
git checkout 0.90.0
make deb

And you can find generated .deb package at the folder ./build/deb/. But it requires installation all dependencies for building: build-essential git golang wget lsb-release ruby ruby-dev rubygems and installed themis

I run next commands inside fresh ubuntu:focal container to compile deb package:

apt update
apt install build-essential git golang wget lsb-release ruby ruby-dev rubygems apt-transport-https
git clone https://github.com/cossacklabs/acra.git
cd acra
git checkout 0.90.0
wget -qO - https://pkgs-ce.cossacklabs.com/gpg | apt-key add -
echo "deb https://pkgs-ce.cossacklabs.com/stable/ubuntu focal main" | tee /etc/apt/sources.list.d/cossacklabs.list
apt update
apt install libthemis-dev
gem install --no-document fpm
make deb

I can't put here file because github deny .deb and large files. So you can build it by yourself or download here - https://filetransfer.io/data-package/ysKJIrU8#link. I uploaded it to first found file sharing service.

kumar1202 commented 2 years ago

Thanks for the setup, I am able to setup AcraConnector on application's host VM and AcraServer on a seperate VM. But I'm running through an error when trying to relay the PostgreSQL connection from App <-> AcraConnector <-> AcraServer <-> HAProxy <-> Patroni Nodes.

I used the following commands to init AcraConnector and AcraServer respectively:

acra-connector --client_id=Neutron --acraserver_connection_host=10.120.10.11

acra-server --db_host=indo-integration.kernel-new-go-acra-server.server.golabs.io --db_port=5432 --postgresql_enable=true --client_id=Neutron --http_api_enable

And I am getting presented with these errors on the following logs:

AcraConnector

time="2022-03-29T22:05:21+07:00" level=error msg="Can't wrap connection" client_id=Neutron code=538 error=EOF

AcraServer

fatal error: unexpected signal during runtime execution
[signal SIGSEGV: segmentation violation code=0x2 addr=0x7f893cb74000 pc=0x7f8941ab04c0]

runtime stack:
runtime.throw(0xec0e14, 0x2a)
    /usr/lib/go-1.13/src/runtime/panic.go:774 +0x72
runtime.sigpanic()
    /usr/lib/go-1.13/src/runtime/signal_unix.go:378 +0x47c

goroutine 24 [syscall]:
runtime.cgocall(0xc8e700, 0xc000208908, 0xc0002be640)
    /usr/lib/go-1.13/src/runtime/cgocall.go:128 +0x5b fp=0xc0002088d8 sp=0xc0002088a0 pc=0x40655b
github.com/cossacklabs/themis/gothemis/session._Cfunc_session_unwrap_size(0xc00049a1e0, 0xc0002be640, 0x94, 0xc0004c20c8, 0x0)
    _cgo_gotypes.go:220 +0x4d fp=0xc000208908 sp=0xc0002088d8 pc=0x959f9d
github.com/cossacklabs/themis/gothemis/session.(*SecureSession).Unwrap.func1(0xc00049a1e0, 0xc0002089b8, 0xc0004c20c8, 0x94)
    /root/acra/build/pkg/mod/github.com/cossacklabs/themis/gothemis@v0.13.1/session/session.go:211 +0x174 fp=0xc000208958 sp=0xc000208908 pc=0x95c014
github.com/cossacklabs/themis/gothemis/session.(*SecureSession).Unwrap(0xc00049a1e0, 0xc0002be640, 0x94, 0x94, 0x94, 0x0, 0x0, 0x0, 0x0, 0xedb6a0)
    /root/acra/build/pkg/mod/github.com/cossacklabs/themis/gothemis@v0.13.1/session/session.go:211 +0xb3 fp=0xc0002089b0 sp=0xc000208958 pc=0x95ada3
github.com/cossacklabs/acra/network.(*SecureSessionConnectionWrapper).wrap(0xc000032540, 0x104a9e0, 0xc00017e180, 0x1701601, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
    /root/acra/network/secure_session_wrapper.go:373 +0x5ff fp=0xc000208b58 sp=0xc0002089b0 pc=0x96659f
github.com/cossacklabs/acra/network.(*SecureSessionConnectionWrapper).WrapServer(0xc000032540, 0x103f740, 0xc00017e450, 0x104a9e0, 0xc00017e180, 0x2, 0x2, 0x103f740, 0xc00017e450, 0xc0000f2180, ...)
    /root/acra/network/secure_session_wrapper.go:426 +0xf1 fp=0xc000209300 sp=0xc000208b58 pc=0x967a01
github.com/cossacklabs/acra/cmd/acra-server/common.(*SServer).processConnection(0xc0002f63f0, 0x103f680, 0xc000087840, 0x104a9e0, 0xc00017e180, 0xc00016cde0)
    /root/acra/cmd/acra-server/common/listener.go:233 +0x36a fp=0xc000209d98 sp=0xc000209300 pc=0xb6d03a
github.com/cossacklabs/acra/cmd/acra-server/common.(*SServer).start.func1(0xc0002f63f0, 0xc000151180, 0xc00016cde0, 0x104a9e0, 0xc00017e180, 0x103f680, 0xc000087840)
    /root/acra/cmd/acra-server/common/listener.go:309 +0x2fe fp=0xc000209fa8 sp=0xc000209d98 pc=0xb72b4e
runtime.goexit()
    /usr/lib/go-1.13/src/runtime/asm_amd64.s:1357 +0x1 fp=0xc000209fb0 sp=0xc000209fa8 pc=0x462c21
created by github.com/cossacklabs/acra/cmd/acra-server/common.(*SServer).start
    /root/acra/cmd/acra-server/common/listener.go:303 +0x106

goroutine 1 [chan receive]:
github.com/cossacklabs/acra/cmd/acra-server/common.(*SServer).StartServer(0xc0002f63f0, 0x103f680, 0xc000087840, 0xc0004aba00, 0x100, 0x0, 0x0)
    /root/acra/cmd/acra-server/common/server.go:57 +0x107
main.realMain(0x0, 0x0)
    /root/acra/cmd/acra-server/acra-server.go:812 +0x4f8f
main.main()
    /root/acra/cmd/acra-server/acra-server.go:113 +0x22

goroutine 5 [syscall]:
os/signal.signal_recv(0x462c26)
    /usr/lib/go-1.13/src/runtime/sigqueue.go:147 +0x9c
os/signal.loop()
    /usr/lib/go-1.13/src/os/signal/signal_unix.go:23 +0x22
created by os/signal.init.0
    /usr/lib/go-1.13/src/os/signal/signal_unix.go:29 +0x41

goroutine 9 [select]:
github.com/cossacklabs/acra/cmd.(*SignalHandler).RegisterWithContext(0xc000176500, 0x103f680, 0xc000087840)
    /root/acra/cmd/utils.go:113 +0x12c
main.realMain.func1(0xc0004aba00, 0xc000176500, 0x103f680, 0xc000087840)
    /root/acra/cmd/acra-server/acra-server.go:513 +0x73
created by main.realMain
    /root/acra/cmd/acra-server/acra-server.go:511 +0x4093

goroutine 10 [select]:
github.com/cossacklabs/acra/cmd.(*SignalHandler).RegisterWithContext(0xc000176550, 0x103f680, 0xc000087840)
    /root/acra/cmd/utils.go:113 +0x12c
main.realMain.func2(0xc0004aba00, 0xc000176550, 0x103f680, 0xc000087840)
    /root/acra/cmd/acra-server/acra-server.go:519 +0x73
created by main.realMain
    /root/acra/cmd/acra-server/acra-server.go:517 +0x40f9

goroutine 11 [select]:
github.com/cossacklabs/acra/cmd/acra-server/common.(*SServer).run(0xc0002f63f0, 0x103f680, 0xc000087840, 0x103ac00, 0xc00016ce40, 0xc00016ce70, 0xc0004540e0)
    /root/acra/cmd/acra-server/common/listener.go:513 +0x1e4
github.com/cossacklabs/acra/cmd/acra-server/common.(*SServer).StartCommands(0xc0002f63f0, 0x103f680, 0xc000087840)
    /root/acra/cmd/acra-server/common/listener.go:472 +0x5e3
github.com/cossacklabs/acra/cmd/acra-server/common.(*SServer).StartServer.func1(0xc0004aba00, 0xc0002f63f0, 0x103f680, 0xc000087840)
    /root/acra/cmd/acra-server/common/server.go:46 +0x73
created by github.com/cossacklabs/acra/cmd/acra-server/common.(*SServer).StartServer
    /root/acra/cmd/acra-server/common/server.go:44 +0x85

goroutine 12 [select]:
github.com/cossacklabs/acra/cmd/acra-server/common.(*SServer).run(0xc0002f63f0, 0x103f680, 0xc000087840, 0x103ac00, 0xc00016cdb0, 0xc00016cde0, 0xc000151180)
    /root/acra/cmd/acra-server/common/listener.go:513 +0x1e4
github.com/cossacklabs/acra/cmd/acra-server/common.(*SServer).Start(0xc0002f63f0, 0x103f680, 0xc000087840)
    /root/acra/cmd/acra-server/common/listener.go:347 +0x630
github.com/cossacklabs/acra/cmd/acra-server/common.(*SServer).StartServer.func2(0xc0004aba00, 0xc0002f63f0, 0x103f680, 0xc000087840)
    /root/acra/cmd/acra-server/common/server.go:52 +0x73
created by github.com/cossacklabs/acra/cmd/acra-server/common.(*SServer).StartServer
    /root/acra/cmd/acra-server/common/server.go:50 +0xdc

goroutine 13 [IO wait]:
internal/poll.runtime_pollWait(0x7f893d51e050, 0x72, 0x0)
    /usr/lib/go-1.13/src/runtime/netpoll.go:184 +0x55
internal/poll.(*pollDesc).wait(0xc00031b898, 0x72, 0x0, 0x0, 0xe9acb7)
    /usr/lib/go-1.13/src/internal/poll/fd_poll_runtime.go:87 +0x45
internal/poll.(*pollDesc).waitRead(...)
    /usr/lib/go-1.13/src/internal/poll/fd_poll_runtime.go:92
internal/poll.(*FD).Accept(0xc00031b880, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0)
    /usr/lib/go-1.13/src/internal/poll/fd_unix.go:384 +0x1f8
net.(*netFD).accept(0xc00031b880, 0x5cf258, 0x0, 0xc0004c2040)
    /usr/lib/go-1.13/src/net/fd_unix.go:238 +0x42
net.(*TCPListener).accept(0xc00048f8c0, 0xc0001f6000, 0x11, 0xe285c0)
    /usr/lib/go-1.13/src/net/tcpsock_posix.go:139 +0x32
net.(*TCPListener).Accept(0xc00048f8c0, 0x43c22e, 0xc0000678d8, 0xc000067908, 0x40986a)
    /usr/lib/go-1.13/src/net/tcpsock.go:261 +0x47
github.com/cossacklabs/acra/network.(*safeCloseListener).Accept(0xc00016cdb0, 0xc0002e1ec0, 0xc0002f6300, 0xc000151180, 0xc00016cde0)
    /root/acra/network/utils.go:144 +0x40
github.com/cossacklabs/acra/cmd/acra-server/common.(*SServer).start(0xc0002f63f0, 0x103f680, 0xc000087840, 0x103ac00, 0xc00016cdb0, 0xc00016cde0, 0xc000151180, 0xc0002e1f20)
    /root/acra/cmd/acra-server/common/listener.go:277 +0x15d
github.com/cossacklabs/acra/cmd/acra-server/common.(*SServer).run.func1(0xc0002f63f0, 0x103f680, 0xc000087840, 0x103ac00, 0xc00016cdb0, 0xc00016cde0, 0xc000151180, 0xc0002e1f20)
    /root/acra/cmd/acra-server/common/listener.go:510 +0xc6
created by github.com/cossacklabs/acra/cmd/acra-server/common.(*SServer).run
    /root/acra/cmd/acra-server/common/listener.go:508 +0x132

goroutine 14 [IO wait]:
internal/poll.runtime_pollWait(0x7f893d51df80, 0x72, 0x0)
    /usr/lib/go-1.13/src/runtime/netpoll.go:184 +0x55
internal/poll.(*pollDesc).wait(0xc0000f2098, 0x72, 0x0, 0x0, 0xe9acb7)
    /usr/lib/go-1.13/src/internal/poll/fd_poll_runtime.go:87 +0x45
internal/poll.(*pollDesc).waitRead(...)
    /usr/lib/go-1.13/src/internal/poll/fd_poll_runtime.go:92
internal/poll.(*FD).Accept(0xc0000f2080, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0)
    /usr/lib/go-1.13/src/internal/poll/fd_unix.go:384 +0x1f8
net.(*netFD).accept(0xc0000f2080, 0x0, 0x0, 0x0)
    /usr/lib/go-1.13/src/net/fd_unix.go:238 +0x42
net.(*TCPListener).accept(0xc00048f900, 0x0, 0x0, 0x0)
    /usr/lib/go-1.13/src/net/tcpsock_posix.go:139 +0x32
net.(*TCPListener).Accept(0xc00048f900, 0x0, 0x0, 0xc000069908, 0x40986a)
    /usr/lib/go-1.13/src/net/tcpsock.go:261 +0x47
github.com/cossacklabs/acra/network.(*safeCloseListener).Accept(0xc00016ce40, 0xc0002e1ec0, 0xeae800, 0x1b, 0x0)
    /root/acra/network/utils.go:144 +0x40
github.com/cossacklabs/acra/cmd/acra-server/common.(*SServer).start(0xc0002f63f0, 0x103f680, 0xc000087840, 0x103ac00, 0xc00016ce40, 0xc00016ce70, 0xc0004540e0, 0xc0002e1f80)
    /root/acra/cmd/acra-server/common/listener.go:277 +0x15d
github.com/cossacklabs/acra/cmd/acra-server/common.(*SServer).run.func1(0xc0002f63f0, 0x103f680, 0xc000087840, 0x103ac00, 0xc00016ce40, 0xc00016ce70, 0xc0004540e0, 0xc0002

I believe it's not able to comprehend the request which is coming from the HAProxy to AcraServer. The app works perfectly with the HAProxy when Acra is out of the picture. I also tried enabling SSL termination on HAProxy but no luck, same error.

Any insights to unblock this error will be really appreciated. Thanks!

Lagovas commented 2 years ago

Hm, I have two ideas where is a problem:

You didn't install Themis. Instructions how to install. It should be installed on acra-server and acra-connector sides.
Some problem with golang and CGO calls. You use go-1.13. But latest tested version for 0.90.0 was 1.14.9. Also tested 1.15.2 and 1.16.9. Newer - better.

You can run ldconfig -p | grep themis command to check, did your system contain and cached themis library or not.

UPD Output with installed themis looks like:

$ ldconfig  -p | grep themis
    libthemis.so.0 (libc6,x86-64) => /lib/x86_64-linux-gnu/libthemis.so.0
    libthemis.so (libc6,x86-64) => /lib/x86_64-linux-gnu/libthemis.so

kumar1202 commented 2 years ago

Stopped using Acra version 0.90.0 and updated to 0.92.0. And now the connection is passing through from the app(using sslmode=require) to AcraServer and reaching the DB. But seems like my TLS setup isn't correct. Can you help me debug using this log from AcraServer, as to till where the connection has been established.

time="2022-03-31T21:01:47+07:00" level=info msg="Got new connection to AcraServer: 10.120.9.89:56968" connection_string="tcp://0.0.0.0:9393/" from_descriptor=false
time="2022-03-31T21:01:47+07:00" level=debug msg="ConnectionManager Added new connection"
time="2022-03-31T21:01:47+07:00" level=info msg="Handle client's connection" client_id=neutron session_id=2
time="2022-03-31T21:01:47+07:00" level=debug msg="Connecting to db" client_id=neutron session_id=2
time="2022-03-31T21:01:47+07:00" level=debug msg=ProxyClientConnection client_id=neutron proxy=client session_id=2
time="2022-03-31T21:01:47+07:00" level=debug msg="Pg db proxy" client_id=neutron proxy=server session_id=2
time="2022-03-31T21:01:47+07:00" level=debug msg="Read startup message" client_id=neutron proxy=server session_id=2
time="2022-03-31T21:01:47+07:00" level=debug msg="Can't read first message type" client_id=neutron code=588 error=EOF proxy=server session_id=2
time="2022-03-31T21:01:47+07:00" level=debug msg="Stop to proxy" client_id=neutron interrupt_side=AcraServer-Database session_id=2
time="2022-03-31T21:01:47+07:00" level=debug msg="EOF connection closed" client_id=neutron interrupt_side=AcraServer-Database session_id=2
time="2022-03-31T21:01:47+07:00" level=info msg="Closing client's connection" client_id=neutron interrupt_side=AcraServer-Database session_id=2
time="2022-03-31T21:01:47+07:00" level=debug msg="Close acra-connector connection" client_id=neutron session_id=2
time="2022-03-31T21:01:47+07:00" level=debug msg="Can't read first 5 bytes" client_id=neutron code=588 error="read tcp 10.120.3.145:9393->10.120.9.89:56968: use of closed network connection" proxy=client session_id=2
time="2022-03-31T21:01:47+07:00" level=debug msg="Can't read packet from client to database" client_id=neutron error="read tcp 10.120.3.145:9393->10.120.9.89:56968: use of closed network connection" proxy=client session_id=2
time="2022-03-31T21:01:47+07:00" level=debug msg="Close db connection" client_id=neutron session_id=2
time="2022-03-31T21:01:47+07:00" level=debug msg="All connections closed" client_id=neutron session_id=2
time="2022-03-31T21:01:47+07:00" level=debug msg="Second proxy goroutine stopped" client_id=neutron error="Client-AcraServer:read tcp 10.120.3.145:9393->10.120.9.89:56968: use of closed network connection" interrupt_side=AcraServer-Database session_id=2
time="2022-03-31T21:01:47+07:00" level=info msg="Finished processing client's connection" client_id=neutron interrupt_side=AcraServer-Database session_id=2

Does msg="EOF connection closed" client_id=neutron interrupt_side=AcraServer-Database mean the connection between AcraServer and DB is getting interrupted and connection between app and AcraServer is established?

Any help will be appreciated. Thanks!

Lagovas commented 2 years ago

I'm not sure that you app or DB client actually uses sslmode=require option. That is how logs looks when database supports TLS and psql connects with PGSSLMODE=require psql -h<host> -p<acra port>:

time="2022-03-31T17:23:50+03:00" level=info msg="Got new connection to AcraServer: [::1]:49832" connection_string="tcp://0.0.0.0:9393/" from_descriptor=false
time="2022-03-31T17:23:50+03:00" level=debug msg="ConnectionManager Added new connection"
time="2022-03-31T17:23:50+03:00" level=info msg="Handle client's connection" client_id=client session_id=2
time="2022-03-31T17:23:50+03:00" level=debug msg="Connecting to db" client_id=client session_id=2
time="2022-03-31T17:23:50+03:00" level=debug msg=ProxyClientConnection client_id=client proxy=client session_id=2
time="2022-03-31T17:23:50+03:00" level=debug msg="Pg db proxy" client_id=client proxy=server session_id=2
time="2022-03-31T17:23:50+03:00" level=debug msg="Read startup message" client_id=client proxy=server session_id=2
time="2022-03-31T17:23:50+03:00" level=debug msg="Start tls proxy" client_id=client proxy=server session_id=2
time="2022-03-31T17:23:50+03:00" level=debug msg="Can't read first 5 bytes" client_id=client code=588 error="read tcp [::1]:9393->[::1]:49832: i/o timeout" proxy=client session_id=2
time="2022-03-31T17:23:50+03:00" level=debug msg="Stop client connection" client_id=client proxy=server session_id=2
time="2022-03-31T17:23:50+03:00" level=debug msg="Init tls with client" client_id=client proxy=server session_id=2

Here we can see:

time="2022-03-31T17:23:50+03:00" level=debug msg="Read startup message" client_id=client proxy=server session_id=2
time="2022-03-31T17:23:50+03:00" level=debug msg="Start tls proxy" client_id=client proxy=server session_id=2

That after startup message started establishing TLS connection. In that case driver sends SSLRequest as first packet. I used our image for PostgreSQL that uses official image for 13 version and just copy and configure TLS certs. And psql rendered error for me:

psql: error: SSL error: sslv3 alert bad certificate

It's expected error because I didn't pass any certs for TLS and AcraServer configured with mTLS requirement.

That is how looks logs when I tried connect to database that doesn't support tls:

time="2022-03-31T17:28:44+03:00" level=info msg="Got new connection to AcraServer: [::1]:49934" connection_string="tcp://0.0.0.0:9393/" from_descriptor=false
time="2022-03-31T17:28:44+03:00" level=debug msg="ConnectionManager Added new connection"
time="2022-03-31T17:28:44+03:00" level=info msg="Handle client's connection" client_id=client session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg="Connecting to db" client_id=client session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg="Pg db proxy" client_id=client proxy=server session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg="Read startup message" client_id=client proxy=server session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg=ProxyClientConnection client_id=client proxy=client session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg="Deny ssl request" client_id=client proxy=server session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg="Read packet" client_id=client proxy=server session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg="Can't read first 5 bytes" client_id=client code=588 error=EOF proxy=client session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg="Can't read packet from client to database" client_id=client error=EOF proxy=client session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg="Stop to proxy" client_id=client interrupt_side=Client-AcraServer session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg="EOF connection closed" client_id=client interrupt_side=Client-AcraServer session_id=3
time="2022-03-31T17:28:44+03:00" level=info msg="Closing client's connection" client_id=client interrupt_side=Client-AcraServer session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg="Close acra-connector connection" client_id=client session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg="Close db connection" client_id=client session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg="Can't read packet" client_id=client code=588 error="read tcp [::1]:56110->[::1]:5432: use of closed network connection" proxy=server session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg="All connections closed" client_id=client session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg="Second proxy goroutine stopped" client_id=client error="AcraServer-Database:read tcp [::1]:56110->[::1]:5432: use of closed network connection" interrupt_side=Client-AcraServer session_id=3
time="2022-03-31T17:28:44+03:00" level=info msg="Finished processing client's connection" client_id=client interrupt_side=Client-AcraServer session_id=3

Here we can see that database denied SSL request:

time="2022-03-31T17:28:44+03:00" level=debug msg="Read startup message" client_id=client proxy=server session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg=ProxyClientConnection client_id=client proxy=client session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg="Deny ssl request" client_id=client proxy=server session_id=3

And after that driver terminated connection:

time="2022-03-31T17:28:44+03:00" level=debug msg="Stop to proxy" client_id=client interrupt_side=Client-AcraServer session_id=3
time="2022-03-31T17:28:44+03:00" level=debug msg="EOF connection closed" client_id=client interrupt_side=Client-AcraServer session_id=3

In your case, I can suppose that your driver sent a simple Startup packet (not SSLRequest) and took SSL requirement from database. In such cases, when the driver doesn't support TLS, they terminate connection.

Without extra information about the application or software used to connect to the database, I can't help more.

kumar1202 commented 2 years ago

Sure, that makes a lot of things clear for me, thanks. Currently I am just trying to connect to the DB through AcraServer using psql. The command I used is pasted below:

psql "host=indo-integration.acra-server.server.golabs.io port=9393 user=hannibal dbname=hannibal_integration sslmode=verify-full sslcert=/etc/secret_manager/indo-integration.hannibal-load-testing.server.golabs.io/fullchain.pem  sslkey=/etc/secret_manager/indo-integration.hannibal-load-testing.server.golabs.io/privkey.pem  sslrootcert=/etc/secret_manager/indo-integration.hannibal-load-testing.server.golabs.io/root.pem"

Lagovas commented 2 years ago

If you have a problem with this command, then try: chmod 0600 /etc/secret_manager/indo-integration.hannibal-load-testing.server.golabs.io/privkey.pem. I had a problem with TLS using psql when it silently ignored my key with certificate because it had incorrect and group/other readable permissions. And didn't warn. So check it too.

Also, personally I didn't try to connection through connection string like that, so I can't confirm that it should work. Everytime I passed certs/keys through env variables PGSSLROOTCERT/PGSSLCERT/PGSSLKEY/PGSSLMODE like PGSSLKEY=<path> PGSSLCERT=<path> PGSSLROOTCERT=<path) PGSSLMODE=verify-full psql <params>

kumar1202 commented 2 years ago

Thanks, I was able to connect to the HAproxy through AcraServer. I am trying to run load tests now, and need to run a MAX function on one of the encrypted columns. As the column is in bytea. it won't support MAX method. Any tips to approach this?

Thanks in advance!

Lagovas commented 2 years ago

Unfortunately, it is not supported. Encryption limits the usage of data. You can encrypt it and do create/read/update/delete operations without querying. You can use searchable encryption and be able to make queries with operators =, <> only. Or you can use tokenization that pseudonymize data with random values of same type. Here is about tokenization.

kumar1202 commented 2 years ago

Thanks for the reply, there is another issue with foreign keys.

Consider an example where, I have two tables bookings and booking_routes, id in bookings is the foreign key to booking_routes stored as booking_id. Both encrypted and with bytea types in the schema.

Encryptor config file for reference:

schemas:
  - table: bookings
    columns:
      - id
      - order_number
      - customer_id
      - driver_id
      - status
      - created_at
    encrypted:
      - column: id
        searchable: true
      - column: customer_id
        searchable: true

  - table: booking_routes
    columns:
      - id
      - booking_id
      - distance_in_meters
      - origin_latitude
      - origin_longitude
      - arrival_latitude
      - arrival_longitude
      - pickup_latitude
      - pickup_longitude
      - destination_latitude
      - destination_longitude
      - created_at
    encrypted:
      - column: booking_id
        searchable: true

When I run this query:

INSERT INTO bookings(
  id,
  order_number,
  customer_id,
  status
) VALUES (
  '0',
  'RB-0',
  '1',
  'booked'
);

INSERT INTO booking_routes(
  booking_id,
  distance_in_meters,
  origin_latitude,
  origin_longitude
) VALUES (
  '0',
  45,
  1.2342353,
  106.123124
);

The second one fails with this error, and is unable to find the booking id in bookings table using FK relationship.

ERROR:  insert or update on table "booking_routes" violates foreign key constraint "booking_routes_booking_id_fkey"
DETAIL:  Key (booking_id)=(\x7fae1c158627e1d34bc5989afcaf483b5135a63c648cea4d14dbb9bef1752f2ce22525259700000000000000f022222222870000000000000000861c004c00000101400c0000001000000020000000d43f5c7ee0fdcd50a8b8c03c8b8ad1708cf8bc3ae6645ded55a43cf6282ed5dfcf6cc6e0087c017692d1fc07b7fad9a8c5f066b08d72fcf038ce6473000101400c0000001000000001000000b3376b85840f1daabf54dfdb603ec8b75f4b3c281bd0dde36aa004a446) is not present in table "bookings".

What fix do you suggest in this case? Thanks in advance!

Lagovas commented 2 years ago

As first step, I suggest deciding what data you want to encrypt and why) What the final goal. Not all data must be encrypted. Usually, it is sensitive data, some PII (personally identifiable information), etc. Usually, identifiers are not sensitive data and can be decrypted. Because they stores only some unrelated to data value used just for identification data. If app needs to hide somehow it too and hide ordering, then it can be UUID with random part in the value.

So yes, if your app doesn't require encrypting identifiers then leave it not encrypted. If you need cross-joins between tables through data then you should use searchable encryption that uses hash values additionally to ciphertext. They can be used for joins and searching. In the open-sourced version of Acra it supports only WHERE statements with automatic update of predicates for searching and doesn't support cross-joins. But you can use it manually by replacing columns value with substr(search_encrypted_column, 1, 33) to use static hash value for joins. If you will encrypt order_number and want to join two tables through this column:

select * from table1 t1 inner join table2 t2 on substr(t1.booking_number, 1, 33)=substr(t2.booking_number);

In this example booking_number configured with searchable: true.

kumar1202 commented 2 years ago

Thanks a lot for these suggestions, incorporated these and now it works for our use-case. I have been trying to install Acra 0.92.0 on other clusters also now, but getting this error while installing. Looks like the SSL certs are expired from your side.

Can you confirm once? I tried this on multiple VMs, same error.

~# wget -qO - https://pkgs.cossacklabs.com/gpg | sudo apt-key add -
OK

~# echo "deb https://pkgs.cossacklabs.com/stable/debian focal main" | \
>     sudo tee /etc/apt/sources.list.d/cossacklabs.list
deb https://pkgs.cossacklabs.com/stable/debian focal main

~# apt-get update
Hit:1 http://asia-east1.gce.archive.ubuntu.com/ubuntu focal InRelease
Hit:2 http://asia-east1.gce.archive.ubuntu.com/ubuntu focal-updates InRelease
Hit:3 http://asia-east1.gce.archive.ubuntu.com/ubuntu focal-backports InRelease
Hit:4 https://packages.cloud.google.com/apt google-cloud-logging-focal-all InRelease
Get:5 https://artifactory-gojek.golabs.io/artifactory/debian-virtual trusty InRelease [3218 B]
Hit:6 http://security.ubuntu.com/ubuntu focal-security InRelease
Hit:7 http://ppa.launchpad.net/vbernat/haproxy-2.0/ubuntu focal InRelease
Get:8 https://artifactory-gojek.golabs.io/artifactory/debian-virtual trusty/main amd64 Packages [3122 kB]
Ign:9 https://pkgs.cossacklabs.com/stable/debian focal InRelease
Err:10 https://pkgs.cossacklabs.com/stable/debian focal Release
  Certificate verification failed: The certificate is NOT trusted. The certificate chain uses expired certificate.  Could not handshake: Error in the certificate verification. [IP: 178.63.6.188 443]
Reading package lists... Done
E: The repository 'https://pkgs.cossacklabs.com/stable/debian focal Release' does not have a Release file.
N: Updating from such a repository can't be done securely, and is therefore disabled by default.
N: See apt-secure(8) manpage for repository creation and user configuration details.

~# apt-get install acra
Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package acra

Lagovas commented 2 years ago

you should use: echo "deb https://pkgs.cossacklabs.com/stable/debian bullseye main" or change debian to ubuntu and leave focal.

kumar1202 commented 2 years ago

My bad, I didn't change the OS type in the URL. Still facing same issue when tried with ubuntu focal and all other options:

root@:~# wget -qO - https://pkgs.cossacklabs.com/gpg | sudo apt-key add -
OK
root@:~# echo "deb https://pkgs.cossacklabs.com/stable/ubuntu focal main" | \
>     sudo tee /etc/apt/sources.list.d/cossacklabs.list
deb https://pkgs.cossacklabs.com/stable/ubuntu focal main
root@:~# sudo apt-get update
Hit:1 http://asia-east1.gce.archive.ubuntu.com/ubuntu focal InRelease
Get:2 http://asia-east1.gce.archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB]
Get:3 http://asia-east1.gce.archive.ubuntu.com/ubuntu focal-backports InRelease [108 kB]
Get:4 https://packages.cloud.google.com/apt google-cloud-logging-focal-all InRelease [5492 B]
Get:5 https://artifactory-gojek.golabs.io/artifactory/debian-virtual trusty InRelease [3218 B]
Get:6 http://asia-east1.gce.archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages [1712 kB]
Get:7 http://asia-east1.gce.archive.ubuntu.com/ubuntu focal-updates/main Translation-en [320 kB]
Get:8 http://asia-east1.gce.archive.ubuntu.com/ubuntu focal-updates/main amd64 c-n-f Metadata [14.9 kB]
Get:9 http://asia-east1.gce.archive.ubuntu.com/ubuntu focal-updates/universe amd64 Packages [916 kB]
Get:10 http://asia-east1.gce.archive.ubuntu.com/ubuntu focal-updates/universe amd64 c-n-f Metadata [20.4 kB]
0% [6 Packages store 0 B] [Connecting to security.ubuntu.com (91.189.91.38)] [Waiting for headers]
Hit:11 http://ppa.launchpad.net/vbernat/haproxy-2.0/ubuntu focal InRelease
Get:12 http://security.ubuntu.com/ubuntu focal-security InRelease [114 kB]
Get:13 https://artifactory-gojek.golabs.io/artifactory/debian-virtual trusty/main amd64 Packages [3100 kB]
Ign:14 https://pkgs.cossacklabs.com/stable/ubuntu focal InRelease
Get:15 http://security.ubuntu.com/ubuntu focal-security/main amd64 Packages [1384 kB]
Err:16 https://pkgs.cossacklabs.com/stable/ubuntu focal Release
  Certificate verification failed: The certificate is NOT trusted. The certificate chain uses expired certificate.  Could not handshake: Error in the certificate verification. [IP: 178.63.6.188 443]
Get:17 http://security.ubuntu.com/ubuntu focal-security/main Translation-en [239 kB]
Get:18 http://security.ubuntu.com/ubuntu focal-security/main amd64 c-n-f Metadata [9900 B]
Get:19 http://security.ubuntu.com/ubuntu focal-security/universe amd64 Packages [696 kB]
Get:20 http://security.ubuntu.com/ubuntu focal-security/universe amd64 c-n-f Metadata [14.1 kB]
Reading package lists... Done
E: The repository 'https://pkgs.cossacklabs.com/stable/ubuntu focal Release' does not have a Release file.
N: Updating from such a repository can't be done securely, and is therefore disabled by default.
N: See apt-secure(8) manpage for repository creation and user configuration details.

Are you sure it's not an issue with your certs?

Edit: I installed themis and acra from source code, will update on if it works properly.

Lagovas commented 2 years ago

Are you sure it's not an issue with your certs?

Yes, because I tested it. Plus, you can open in browser https://pkgs.cossacklabs.com and check it by yourself. It will redirect you to https://pkgs-ce.cossacklabs.com/:

kumar1202 commented 2 years ago

Sure, thanks. Was going through the key rotation docs but realized it's only available on Enterprise Edition.

Are all the three strategies restricted to enterprise edition? As it's a bit confusing from the docs as it's mentioned atop.

If using community edition, will I be able to use rotation without re-encryption strategy or will I have to resort to full re-encryption, as I see that can be achieved via a script. Is full re-encryption also only available on the enterprise edition?

vixentael commented 2 years ago

Yes, out-of-the-box key rotation is supported in Acra Enterprise Edition. When using Acra Community Edition, you can build your own key rotation manually / using script, by creating new symmetric keys and putting them to the keystore, and then gradually removing old ones.

As you are using Acra Community Edition, may I ask what's the project you are working for?

We have an option called "Acra Community Edition + services" which basically means that you use Acra CE (this repo) plus we help to build a security tooling around – automatic deployment, data migration, key rotation, PKI, log export, etc, depending on your needs. It's cheaper than Enterprise Acra, doesn't have all the features, but it's a good option for small products that don't need all security features now.

kumar1202 commented 2 years ago

Hey @vixentael. I am from the database team at Gojek and currently we are experimenting with Acra Community edition for transparently encrypting PII data in our critical databases.

We have published a POC to integrate AcraServer with our Patroni cluster setup and have run viable load tests for the same, our next step is to integrate AcraServer to a critical production database.

I really appreciate your offer for your services, but before jumping into a paid plan, we want to integrate Acra to atleast few of our production databases, and help our security team to provide clearance to Acra for using it as a TDE solution org-wide at Gojek.

Once, we have established these above expectations from our side, then it would be easier to us to choose which Acra plan suits best for our use-case.

kumar1202 commented 2 years ago

Hey @Lagovas @vixentael , we have a few concerns regarding data migration for preparing DB for transparent encryption using Acra:

For a live production DB with over a million rows, what is the strategy you would recommend to change the data type to bytea and encrypt the existing data with minimal downtime. One approach which comes to my mind is to query all data of the columns and re-write it passing through Acra, but that seems inefficient and will bring downtime in a live production setup.
In case, we completed the migration, and due to some issue we need to rollback to the previous setup. How will we go about it, as the data inserted for the period between Acra setup and rollback will have to be retrieved specifically as it'll be encrypted. So, how do we deal with rollback and point-in-time recovery in such cases?

Thanks in advance!

Lagovas commented 2 years ago

The most safe way to do it with zero-downtime is to provide +1 column with bytea type. Then encrypt data from source column to new one. Save new data to both columns on application side during migration (triggers will not work because they change data on DB side and don't pass through Acra). Or if it is not possible then make downtime and process remaining data with encryption. And after that rename to <_old_column_name : type>, and rename to .

To rollback you should rename columns back. As I remember, renaming is fast operation and will not block DB too long.

And yes, the simplest way to encrypt data is select + update data through Acra. Or you can write your own script according to your requirements, run AcraTranslator and encrypt data via AcraTranslator's API and replace data by yourself. Or in the loop row by row over production DB without long blocks.

vixentael commented 2 years ago

I guess the Q is resolved :)

cossacklabs / acra

Support for AcraServer integration with Patroni HA #513