[ISSUE] Acra Server doesn't encrypt on insert new row on PostgreSQL

cr0hn commented 1 year ago

Describe the bug

When I insert a row from a Python script, data is not encrypted in PostgreSQL, and got this error in the terminal:

Column count in RowDescription packet not same as parsed query count of columns

To Reproduce

Connect to ACRA Server.
Perform this query:

Example of query that doesn't encrypt data:

INSERT INTO HTTP_CALLS (
    REQUEST_PATH, REQUEST_METHOD, REQUEST_HEADERS,
    REQUEST_HTTP_VERSION, RESPONSE_CODE,
    RESPONSE_REASON, RESPONSE_HEADERS,
    RESPONSE_HTTP_VERSION, PROJECT_ID,
    SOURCE_ID, REQUEST_PATH_RAW, REQUEST_BODY_RAW,
    REQUEST_BODY_JSON, RESPONSE_BODY_RAW,
    RESPONSE_BODY_JSON, REQUEST_QUERY_PARAMS,
    REQUEST_COOKIES
)
VALUES
    (
        '/api/admin/myyy', 'GET', '{}',
        '1.1', 200, 'OK', '{"hello":1}', '1.1', 'b56036f2-abfa-4d93-bf51-116f86f372b1'::uuid,
        '438f3326-b705-46d0-9f94-b1acb2fc92c1'::uuid,
        '/api/admin/all_users', 'ewogICJ1c2VyIjogInVzZXIzQGFjbWUuY29tIiwKICAicGFzcyI6ICJyYW5kb21QYXNzMyIsCiAgIm5hbWUiOiAibXluYW1lIiwKICAiaXNfYWRtaW4iOiB0cnVlCn0=',
        NULL, 'eyJ1c2VyMTJAYWNtZS5jb20iOnsiVXNlciI6InVzZXIxMkBhY21lLmNvbSIsIlBhc3MiOiJyYW5kb21QYXNzMTEiLCJOYW1lIjoibXluYW1lMSIsIklzX2FkbWluIjpmYWxzZSwiQWNjb3VudF9iYWxhbmNlIjoxfSwidXNlcjFAYWNtZS5jb20iOnsiVXNlciI6InVzZXIxQGFjbWUuY29tIiwiUGFzcyI6InJhbmRvbVBhc3MxIiwiTmFtZSI6Im15bmFtZSIsIklzX2FkbWluIjpmYWxzZSwiQWNjb3VudF9iYWxhbmNlIjoxfSwidXNlcjMzQGFjbWUuY29tIjp7IlVzZXIiOiJ1c2VyMzNAYWNtZS5jb20iLCJQYXNzIjoiaGVsbG9waXhpIiwiTmFtZSI6IlBpeGkgVXNlciIsIklzX2FkbWluIjpmYWxzZSwiQWNjb3VudF9iYWxhbmNlIjowfSwidXNlcjNAYWNtZS5jb20iOnsiVXNlciI6InVzZXIzQGFjbWUuY29tIiwiUGFzcyI6InJhbmRvbVBhc3MzIiwiTmFtZSI6Im15bmFtZSIsIklzX2FkbWluIjp0cnVlLCJBY2NvdW50X2JhbGFuY2UiOjB9LCJ1c2VyQGFjbWUuY29tIjp7IlVzZXIiOiJ1c2VyQGFjbWUuY29tIiwiUGFzcyI6InJhbmRvbVBhc3MiLCJOYW1lIjoibXluYW1lIiwiSXNfYWRtaW4iOnRydWUsIkFjY291bnRfYmFsYW5jZSI6MX19',
        NULL, NULL, '{}'
    );

But, when I use an SQL UI App and insert a new row, information is encrypted

Expected behavior

Encrypted fields

Acra configuration files For AcraServer:

[X] Docker Compose:

  acra-server:
    build:
      context: database
      dockerfile: Dockerfile.acra
    restart: always
    depends_on:
      - postgres
    environment:
      ACRA_MASTER_KEY: [OMITTED]
    networks:
      - postgres-net
    ports:
      - 9393:9393  # SQL Port
    command: >-
      --client_id=MyClient
      --db_host=postgres
      --keys_dir=/keys
      --db_port=5432
      --postgresql_enable=true
      --encryptor_config_file=/config/encryptor_config_without_zone.yaml
      -v

Keys and config are embedded in the Dockerfile.acra file.

- [X] Dockerfile:

FROM debian:bullseye-slim

COPY --from=cossacklabs/acra-server:0.93.0 /acra-server /acra-server

RUN apt-get update && apt-get -y install \
    apt-transport-https \
    build-essential \
    ca-certificates \
    curl \
    git \
    gnupg \
    libssl-dev \
    openssl \
    rsync \
    wget && \
    wget -qO - https://pkgs-ce.cossacklabs.com/gpg | apt-key add - && \
    echo "deb https://pkgs-ce.cossacklabs.com/stable/debian bullseye main" | tee /etc/apt/sources.list.d/cossacklabs.list && \
    apt update && apt -y install libthemis

WORKDIR /root

COPY ./acra/keys/* /keys/
COPY ./acra/acra_without_zone.yaml /config/encryptor_config_without_zone.yaml

RUN chmod 700 /keys /config && \
    chmod 600 /keys/*

ENTRYPOINT ["/acra-server"]

- [X] `encryptor_config.yaml` if used.

defaults:
  crypto_envelope: acrablock

schemas:
  - table: http_calls
    columns:
      - id
      - request_path_raw
      - request_path
      - request_query_params
      - request_method
      - request_headers
      - request_cookies
      - request_body_raw
      - request_body_json
      - request_source_ipv4
      - request_source_ipv6
      - request_dest_ipv4
      - request_dest_ipv6
      - request_timestamp
      - request_http_version
      - response_code
      - response_reason
      - response_headers
      - response_body_raw
      - response_body_json
      - response_source_ipv4
      - response_source_ipv6
      - response_dest_ipv4
      - response_dest_ipv6
      - response_timestamp
      - response_http_version
      - created_date
      - project_id
      - source_id
      - analysis_id
    encrypted:
      - column: request_cookies
        data_type: "str"
      - column: request_body_raw
        data_type: "str"
      - column: request_body_json
        data_type: "str"
      - column: request_headers
        data_type: "str"
      - column: response_body_raw
        data_type: "str"
      - column: response_body_json
        data_type: "str"
      - column: response_body_headers
        data_type: "str"

[X] SQL Table


CREATE TABLE IF NOT EXISTS HTTP_CALLS
(
    ID uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    REQUEST_PATH_RAW TEXT NOT NULL, 
    REQUEST_PATH TEXT NOT NULL,
    REQUEST_QUERY_PARAMS JSONB NULL,
    REQUEST_METHOD VARCHAR(20),
    REQUEST_HEADERS JSONB NULL,
    REQUEST_COOKIES JSONB NULL,
    REQUEST_BODY_RAW TEXT default NULL,
    REQUEST_BODY_JSON JSONB default NULL,
    REQUEST_SOURCE_IPV4 TEXT NULL,
    REQUEST_SOURCE_IPV6 TEXT NULL,
    REQUEST_DEST_IPV4 TEXT NULL,
    REQUEST_DEST_IPV6 TEXT NULL,
    REQUEST_TIMESTAMP FLOAT,
    REQUEST_HTTP_VERSION VARCHAR(10),
    RESPONSE_CODE INT,
    RESPONSE_REASON VARCHAR(200),
    RESPONSE_HEADERS JSONB NULL,
    RESPONSE_BODY_RAW TEXT default NULL,
    RESPONSE_BODY_JSON JSONB default NULL,
    RESPONSE_SOURCE_IPV4 TEXT NULL,
    RESPONSE_SOURCE_IPV6 TEXT NULL,
    RESPONSE_DEST_IPV4 TEXT NULL,
    RESPONSE_DEST_IPV6 TEXT NULL,
    RESPONSE_TIMESTAMP FLOAT,
    RESPONSE_HTTP_VERSION VARCHAR(10),
    CREATED_DATE TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PROJECT_ID uuid,
    SOURCE_ID uuid NULL,
    ANALYSIS_ID uuid NULL,

    CONSTRAINT FK_PROJECT_ID
        FOREIGN KEY(PROJECT_ID)
            REFERENCES PROJECT(id) ON DELETE CASCADE,

    CONSTRAINT FK_SOURCE_ID
        FOREIGN KEY(SOURCE_ID)
            REFERENCES SOURCES(id) ON DELETE CASCADE,

    CONSTRAINT FK_ANALYSIS_ID
        FOREIGN KEY(ANALYSIS_ID)
            REFERENCES ANALYSIS(ID) ON DELETE CASCADE
);

Environment (please complete the following information):

Acra version: 0.93.0
Database server and its version: Postgres 13
Installed components: Acra Server
Data-in-transit encryption between Acra and the client-side application:
- [ ] TLS
- [ ] AcraConnector
- [X] no transport encryption
Installation way:
- [X] via Docker
- [ ] via package manager

Additional context

Lagovas commented 1 year ago

I will try it a bit later. But first fast try showed that you create tables with incorrect types. AcraServer encrypts data and pass it as binary values. Database should store BYTEA (postgresql) types. data_type: str helps to hide actual data type on DB side for application. App can deal as with string value instead of binary. But real data type on DB side should be BYTEA. So, database should return error for your insert due to invalid syntax of values. AcraServer will encrypt your string values and pass it as binary values. And in case of JSON type it will be invalid syntax.

vixentael commented 1 year ago

@cr0hn can you try recreating database table using BYTEA type for fields that should be encrypted?

For example, for request_cookies:

Database: bytea for encrypted field (currently wrong) Acra encr config: string (data type that app expects) (currently ok) App: string (probably ok)

cr0hn commented 1 year ago

Hi @Lagovas! I did it and it worked! Thanks a lot for your help.

As a suggestion: I don’t find a warning or something else in the documentation. Maybe It helps more people.

Thanks a lot for your help and your work. It’s a really nice project

vixentael commented 1 year ago

thank you @cr0hn ! w3e will definitely put a note to the docs

cossacklabs / acra

[ISSUE] Acra Server doesn't encrypt on insert new row on PostgreSQL #573