---
warning: DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github.com/duckdb/community-extensions repository, check README there
title: apt
excerpt: |
  DuckDB Community Extensions
  Query the apt/dpkg package manager of Debian-based systems with SQL - installed packages, dependencies, repositories and their trust, package origins, pending security updates and change history

extension:
  name: apt
  description: Query the apt/dpkg package manager of Debian-based systems with SQL - installed packages, dependencies, repositories and their trust, package origins, pending security updates and change history
  version: 0.1.0
  language: C++
  build: cmake
  license: MIT
  maintainers:
    - adriens
  # apt only exists on Debian-based (glibc) Linux
  excluded_platforms: "wasm_mvp;wasm_eh;wasm_threads;windows_amd64;windows_amd64_mingw;windows_arm64;osx_amd64;osx_arm64;linux_amd64_musl;linux_arm64_musl"
  # lz4, to read the lz4-compressed apt indexes (vcpkg.json)
  requires_toolchains: "vcpkg"

repo:
  github: adriens/duckdb-apt
  # v0.1.0; DuckDB 2.0 only, so variegata (v1.5.6 builds) is unset
  ref: 14746b3542d12ab547e5e9720ee48e0b94cddc95

docs:
  hello_world: |
    INSTALL apt FROM community;
    LOAD apt;

    -- Version of apt
    SELECT apt_version();

    -- What did I install on purpose?
    SELECT name, version
    FROM apt_packages()
    WHERE NOT auto_installed
    ORDER BY name;

    -- Which packages will apt never update?
    SELECT name, version
    FROM apt_packages()
    ANTI JOIN apt_package_origins()
        USING (name, architecture);

    -- Pending security updates
    SELECT name, current_version, new_version
    FROM apt_upgradable()
    WHERE is_security;

    -- Repositories without a pinned signing key, or fetched over plain HTTP
    SELECT uri, suite, signed_by
    FROM apt_sources()
    WHERE enabled
      AND (signed_by IS NULL OR uri LIKE 'http://%');

    -- Who changed what lately?
    SELECT start_time, action, package, requested_by
    FROM apt_history()
    ORDER BY start_time DESC
    LIMIT 20;

    -- Compare Debian versions like dpkg
    SELECT apt_version_compare('1:1.0', '2.0');

    -- Package URLs for vulnerability scanners
    COPY (SELECT purl FROM apt_packages()) TO 'purls.csv';
  extended_description: |
    The apt extension reads the files of the apt and dpkg package managers (/var/lib/dpkg, /etc/apt,
    /var/lib/apt/lists, /var/log/apt) and exposes them as tables, so that the software installed on a
    Debian, Ubuntu or derived system can be audited with SQL: inventory, dependency graph, repository
    configuration and freshness, the repository each package comes from (and the packages no repository
    updates anymore), pending updates flagged as security fixes, held packages and the change history with
    who requested each change.

    Files are read through DuckDB's file system, so enable_external_access and allowed_directories apply.
    Every table function accepts a root parameter to audit another system, such as a mounted disk image or a
    container filesystem. On systems without apt, the functions return no rows.

    Documentation: https://adriens.github.io/duckdb-apt/

    A Quarto notebook in the repository turns these tables into a PDF security report.

extension_star_count: 0
extension_star_count_pretty: 0
extension_download_count: null
extension_download_count_pretty: n/a
image: '/images/community_extensions/social_preview/preview_community_extension_apt.png'
layout: community_extension_doc
---
### Compatibility Matrix

This table covers all DuckDB releases that have not reached [end-of-life](https://duckdb.org/release_calendar), including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

<div class="extension_compatibility_table"></div>

| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
| :--- | :---: | :---: | :---: | :---: | :---: |
| Linux AMD64 | ❌ | ❌ | ❌ | ❌ | ✅ |
| Linux ARM64 | ❌ | ❌ | ❌ | ❌ | ✅ |
| macOS AMD64 | ❌ | ❌ | ❌ | ❌ | ❌ |
| macOS ARM64 | ❌ | ❌ | ❌ | ❌ | ❌ |
| Windows AMD64 | ❌ | ❌ | ❌ | ❌ | ❌ |
| Windows ARM64 | ❌ | ❌ | ❌ | ❌ | ❌ |
| WebAssembly | ❌ | ❌ | ❌ | ❌ | ❌ |

### Installing and Loading
```sql
INSTALL {{ page.extension.name }} FROM community;
LOAD {{ page.extension.name }};
```

{% if page.docs.hello_world %}
### Example
```sql
{{ page.docs.hello_world }}```
{% endif %}

{% if page.docs.extended_description %}
### About {{ page.extension.name }}
{{ page.docs.extended_description }}
{% endif %}
### Added Functions

<div class="extension_functions_table"></div>

|    function_name    | function_type |                                                                                            description                                                                                            | comment |                                            examples                                            |
|---------------------|---------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|---------|------------------------------------------------------------------------------------------------|
| apt_dependencies    | table         | Dependencies of the installed packages (Pre-Depends, Depends, Recommends, Suggests), one row per alternative                                                                                      | NULL    | [SELECT package FROM apt_dependencies() WHERE dependency = 'libssl3t64']                       |
| apt_history         | table         | Package installs, upgrades and removals recorded by apt (history.log and its rotated .gz files), with who requested them: requested_by is NULL for automatic updates such as unattended-upgrades  | NULL    | [SELECT start_time, action, package, requested_by FROM apt_history() ORDER BY start_time DESC] |
| apt_package_origins | table         | Repositories publishing each installed package, with the version they publish: an installed package without any row is unknown to every repository and never updated by apt                       | NULL    | [SELECT name FROM apt_packages() ANTI JOIN apt_package_origins() USING (name, architecture)]   |
| apt_packages        | table         | Installed packages, from the dpkg database: version, size, maintainer, whether they were installed on purpose (auto_installed), held, last install time and package URL (purl)                    | NULL    | [SELECT name, version FROM apt_packages() WHERE NOT auto_installed]                            |
| apt_provides        | table         | Virtual packages provided by the installed packages                                                                                                                                               | NULL    | [SELECT package FROM apt_provides() WHERE provides = 'mail-transport-agent']                   |
| apt_repositories    | table         | Repositories known from the indexes downloaded by apt update: origin, suite, component, number of packages, release date and validity, and whether they are security repositories                 | NULL    | [SELECT site, suite, date FROM apt_repositories() ORDER BY date]                               |
| apt_sources         | table         | Configured repositories (sources.list and sources.list.d, one-line and deb822 formats), one row per type x URI x suite, with how they are trusted                                                 | NULL    | [SELECT uri, suite FROM apt_sources() WHERE enabled AND signed_by IS NULL]                     |
| apt_upgradable      | table         | Installed packages with a newer version available, computed from the downloaded indexes with apt's default policy (pins are not applied), and whether the update comes from a security repository | NULL    | [SELECT name, current_version, new_version FROM apt_upgradable() WHERE is_security]            |
| apt_version         | scalar        | Installed version of apt on the system mounted at root                                                                                                                                            | NULL    | [SELECT apt_version('/mnt/image')]                                                             |
| apt_version         | scalar        | Installed version of apt, NULL when apt is not installed                                                                                                                                          | NULL    | [SELECT apt_version()]                                                                         |
| apt_version_compare | scalar        | Compares two Debian package versions like dpkg --compare-versions (epoch, upstream version, revision, "~" sorting first): -1 if a < b, 0 if equal, 1 if a > b                                     | NULL    | [SELECT apt_version_compare('1:1.0', '2.0')]                                                   |

### Overloaded Functions

<div class="extension_functions_table"></div>

This extension does not add any function overloads.

### Added Types

<div class="extension_types_table"></div>

This extension does not add any types.

### Added Settings

<div class="extension_settings_table"></div>

|      name      |                       description                       | input_type | scope  | aliases | typed_value |
|----------------|---------------------------------------------------------|------------|--------|---------|-------------|
| temp_directory | Set the directory to which temporary files are written. | VARCHAR    | GLOBAL | []      | post.db.tmp |