[go: up one dir, main page]

Skip to content

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()';
  1. Copy _blackbox_demo_dump.sql and rules.sql to app folder next to docker-compose.yml
  2. docker compose up -d
  3. docker exec -it anon bash
  4. psql then ALTER DATABASE postgres SET anon.privacy_by_default = True; and exit
  5. psql then SELECT name,setting FROM pg_settings WHERE name LIKE '%anon%'; and exit
               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
  1. cat _blackbox_demo_dump.sql rules.sql | /dump.sh > anon.sql throws 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?

To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information