pg_dump errors with privacy_by_default setting on
Hey! I wanted to use privacy_by_default setting to anonymise SQL dump file using postgresql_anonymizer image, but it errors during dump. Have you encountered similar issue?
Code to reproduce:
docker-compose.yml
services:
anon:
container_name: anon
image: registry.gitlab.com/dalibo/postgresql_anonymizer:latest
platform: linux/amd64
volumes:
- ./app:/app
working_dir: /app
environment:
- POSTGRES_PASSWORD=postgres
- PGUSER=postgres
_blackbox_demo_dump.sql dump file created in postgres:17 using sample from https://gitlab.com/dalibo/postgresql_anonymizer/-/blob/latest/demo/blackbox.sh?ref_type=heads
--
-- PostgreSQL database dump
--
-- Dumped from database version 17.0 (Debian 17.0-1.pgdg120+1)
-- Dumped by pg_dump version 17.0 (Debian 17.0-1.pgdg120+1)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: people; Type: TABLE; Schema: public; Owner: blackbox_demo_owner
--
CREATE TABLE public.people (
firstname text,
lastname text
);
ALTER TABLE public.people OWNER TO blackbox_demo_owner;
--
-- Data for Name: people; Type: TABLE DATA; Schema: public; Owner: blackbox_demo_owner
--
COPY public.people (firstname, lastname) FROM stdin;
Sarah Conor
\.
--
-- Name: TABLE people; Type: ACL; Schema: public; Owner: blackbox_demo_owner
--
GRANT SELECT ON TABLE public.people TO blackbox_demo_reader;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE public.people TO blackbox_demo_writer;
--
-- PostgreSQL database dump complete
--
rules.sql
SELECT pg_catalog.set_config('search_path', 'public', false);
CREATE EXTENSION anon CASCADE;
SELECT anon.init();
SECURITY LABEL FOR anon ON COLUMN people.lastname
IS 'MASKED WITH FUNCTION anon.fake_last_name()';
- Copy
_blackbox_demo_dump.sqlandrules.sqltoappfolder next todocker-compose.yml docker compose up -ddocker exec -it anon bash-
psqlthenALTER DATABASE postgres SET anon.privacy_by_default = True;andexit -
psqlthenSELECT name,setting FROM pg_settings WHERE name LIKE '%anon%';andexit
name | setting
----------------------------------+-------------
anon.algorithm | sha256
anon.dummy_locale | en_US
anon.k_anonymity_provider | k_anonymity
anon.masking_policies |
anon.maskschema | mask
anon.privacy_by_default | on
anon.restrict_to_trusted_schemas | on
anon.salt |
anon.sourceschema | public
anon.strict_mode | on
anon.transparent_dynamic_masking | off
-
cat _blackbox_demo_dump.sql rules.sql | /dump.sh > anon.sqlthrows an error
pg_dump: error: query failed: ERROR: could not find attribute -6 in subquery targetlist
pg_dump: detail: Query was: SELECT x.tableoid, x.oid, x.extname, n.nspname, x.extrelocatable, x.extversion, x.extconfig, x.extcondition FROM pg_extension x JOIN pg_namespace n ON n.oid = x.extnamespace
In addition running just pg_dump like pg_dump postgres -U dump_anon throws same error.
Turning off privacy_by_default removes error.
Do you have any clue what is happening or what am I doing wrong?