Incremental backups do not work w/ password-protected base backups

[a8filich]

Company or project name

MIC

Describe the unexpected behavior

Hello everyone,

The built-in backup functionality of ClickHouse Server allows incremental backups by specifying a base backup in the backup settings, which we use w/ Azure Blob Storage. With base backups that are not protected by a password, this works as expected.

However, when we assign a password to a backup, and then attempt to use this password-protected backup (.zip file) as the base backup for creating an incremental backup in another backup operation, we get an error that a password is required and the password-protected backup's archive cannot be unpacked. While attempting to perform such an incremental backup operation, the error occurs despite providing in the backup settings the password that was used to create the base backup.

Presumably, the backup password setting is used only for creating a new standalone password-protected backup or for restoring from a password-protected backup, but it does not seem to be set in ZipArchiveReader in the backup scenario described above to read from a password-protected base backup file. Although this would make sense, as it is reasonable to use a single password only for one particular backup, it also appears that a dedicated backup setting for the base backup password is not present.

Does it reproduce on the most recent release?

Yes, it reproduces w/ the most recent release v24.6.2.17-stable (2024-07-05), and also w/ earlier releases v24.5.3.5-stable (2024-06-13) and v24.4.1.2088-stable (2024-05-01).

How to reproduce

• Which ClickHouse server version to use

  24.6.2.17

• Queries to run that lead to unexpected result

  1.

  -- creates a password-protected base backup
  BACKUP DATABASE default
  TO AzureBlobStorage('<connection_string>', '<blob_container_name>', '<blob_path_base_backup_zip_file>')
  SETTINGS password='<backup_pwd>';

  2.

  -- attempts to create an incremental backup from the password-protected base backup created w/ the previous query
  -- fails
  BACKUP DATABASE default
  TO AzureBlobStorage('<connection_string>', '<blob_container_name>', '<blob_path_incremental_backup_zip_file>')
  SETTINGS
  base_backup=AzureBlobStorage('<connection_string>', '<blob_container_name>', '<blob_path_base_backup_zip_file>'),
  password='<backup_pwd>';

Expected behavior

It would be possible to create an incremental backup that uses as the base backup a password-protected archive backup file, i.e., a preceding backup created w/ the password setting. This would work also w/ Azure Blob Storage.

In the best-case scenario, there would be an additional dedicated backup setting for base backup passwords to use in incremental backup operations w/ base backups. For example: 1.

-- creates a password-protected base backup
BACKUP DATABASE default
TO AzureBlobStorage('<connection_string>', '<blob_container_name>', '<blob_path_base_backup_zip_file>')
SETTINGS password='<base_backup_pwd>';` 

2.

-- creates an incremental backup from the password-protected base backup created w/ the previous query
-- uses a new dedicated setting base_backup_password to unpack the base backup
-- uses the password setting to protect the incremental backup w/ a different password
BACKUP DATABASE default
TO AzureBlobStorage('<connection_string>', '<blob_container_name>', '<blob_path_incremental_backup_zip_file>')
SETTINGS
base_backup=AzureBlobStorage('<connection_string>', '<blob_container_name>', '<blob_path_base_backup_zip_file>'),
base_backup_password='<base_backup_pwd>',
password='<incremental_backup_pwd>';

Error message and/or stacktrace

Screenshots of logs from a test environment:

[UnamedRus]

Pull request https://github.com/ClickHouse/ClickHouse/pull/66214