CREATE SERVER fuseki
FOREIGN DATA WRAPPER rdf_fdw 
OPTIONS (
  endpoint   'http://fuseki:3030/dt/sparql',
  update_url 'http://fuseki:3030/dt/update',
  http_proxy 'http://172.19.42.101:3128',
  connect_timeout '1');
CREATE FOREIGN TABLE ft (
  subject   rdfnode OPTIONS (variable '?s'),
  predicate rdfnode OPTIONS (variable '?p'),
  object    rdfnode OPTIONS (variable '?o') 
)
SERVER fuseki OPTIONS (
  log_sparql 'true',
  sparql 'SELECT * WHERE {?s ?p ?o}',
  sparql_update_pattern '?s ?p ?o .'
);
CREATE USER MAPPING FOR postgres
SERVER fuseki OPTIONS (
  user 'admin', password 'secret',
  proxy_user 'proxyuser', proxy_password 'proxypass');
/* Correct proxy settings */
INSERT INTO ft (subject, predicate, object)
VALUES  ('<https://www.uni-muenster.de>', '<http://dbpedia.org/property/name>', '"Westfälische Wilhelms-Universität Münster"@de');
INFO:  SPARQL query sent to 'fuseki':
INSERT DATA { <https://www.uni-muenster.de> <http://dbpedia.org/property/name> "Westfälische Wilhelms-Universität Münster"@de };


SELECT * FROM ft;
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o}

INFO:  SPARQL returned 1 record.

            subject            |             predicate              |                     object                     
-------------------------------+------------------------------------+------------------------------------------------
 <https://www.uni-muenster.de> | <http://dbpedia.org/property/name> | "Westfälische Wilhelms-Universität Münster"@de
(1 row)

SELECT *
FROM sparql.describe('fuseki', 'DESCRIBE <https://www.uni-muenster.de>');
            subject            |             predicate              |                     object                     
-------------------------------+------------------------------------+------------------------------------------------
 <https://www.uni-muenster.de> | <http://dbpedia.org/property/name> | "Westfälische Wilhelms-Universität Münster"@de
(1 row)

CALL rdf_fdw_clone_table(
        create_table => true,
        foreign_table => 'public.ft',
        target_table  => 'public.t1'
     );
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o}
ORDER BY ASC(?s) 
OFFSET 0 LIMIT 100

INFO:  SPARQL returned 1 record.

INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o}
ORDER BY ASC(?s) 
OFFSET 100 LIMIT 100

INFO:  SPARQL returned 0 records.

SELECT * FROM public.t1;
            subject            |             predicate              |                     object                     
-------------------------------+------------------------------------+------------------------------------------------
 <https://www.uni-muenster.de> | <http://dbpedia.org/property/name> | "Westfälische Wilhelms-Universität Münster"@de
(1 row)

UPDATE ft SET object = '"University of Münster"@en'
WHERE subject = '<https://www.uni-muenster.de>';
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o
 ## rdf_fdw pushdown conditions ##
 FILTER(?s = <https://www.uni-muenster.de>)
}

INFO:  SPARQL returned 1 record.

INFO:  SPARQL query sent to 'fuseki':
DELETE DATA { <https://www.uni-muenster.de> <http://dbpedia.org/property/name> "Westfälische Wilhelms-Universität Münster"@de };
INSERT DATA { <https://www.uni-muenster.de> <http://dbpedia.org/property/name> "University of Münster"@en };


SELECT * FROM ft;
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o}

INFO:  SPARQL returned 1 record.

            subject            |             predicate              |           object           
-------------------------------+------------------------------------+----------------------------
 <https://www.uni-muenster.de> | <http://dbpedia.org/property/name> | "University of Münster"@en
(1 row)

DELETE FROM ft;
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o}

INFO:  SPARQL returned 1 record.

INFO:  SPARQL query sent to 'fuseki':
DELETE DATA { <https://www.uni-muenster.de> <http://dbpedia.org/property/name> "University of Münster"@en };


SELECT * FROM ft;
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o}

INFO:  SPARQL returned 0 records.

 subject | predicate | object 
---------+-----------+--------
(0 rows)

/*
 * A cached plan uses the user mapping current at execution, not the one
 * of the role that planned it.
 */
\set VERBOSITY terse
CREATE ROLE proxy_role;
GRANT USAGE ON FOREIGN SERVER fuseki TO proxy_role;
GRANT SELECT ON ft TO proxy_role;
CREATE USER MAPPING FOR proxy_role
SERVER fuseki OPTIONS (proxy_user 'proxyuser', proxy_password 'wrongpass');
PREPARE ft_count AS SELECT count(*) FROM ft;
EXECUTE ft_count;
INFO:  SPARQL query sent to 'fuseki':

SELECT  * 
{?s ?p ?o}

INFO:  SPARQL returned 0 records.

 count 
-------
     0
(1 row)

SET ROLE proxy_role;
EXECUTE ft_count; -- must fail: proxy_role's mapping
INFO:  SPARQL query sent to 'fuseki':

SELECT  * 
{?s ?p ?o}

ERROR:  proxy authentication required on server "fuseki" (HTTP 407)
RESET ROLE;
ALTER USER MAPPING FOR postgres SERVER fuseki OPTIONS (SET proxy_password 'wrongpass');
EXECUTE ft_count; -- must fail: altered mapping
INFO:  SPARQL query sent to 'fuseki':

SELECT  * 
{?s ?p ?o}

ERROR:  proxy authentication required on server "fuseki" (HTTP 407)
ALTER USER MAPPING FOR postgres SERVER fuseki OPTIONS (SET proxy_password 'proxypass');
EXECUTE ft_count;
INFO:  SPARQL query sent to 'fuseki':

SELECT  * 
{?s ?p ?o}

INFO:  SPARQL returned 0 records.

 count 
-------
     0
(1 row)

DEALLOCATE ft_count;
/*
 * A foreign table queried through a view uses the user mapping of the
 * view owner, as the permissions are checked as that role.
 */
CREATE VIEW ft_view AS SELECT * FROM ft;
GRANT SELECT, INSERT, UPDATE, DELETE ON ft_view TO proxy_role;
SET ROLE proxy_role;
INSERT INTO ft_view (subject, predicate, object)
VALUES ('<https://www.uni-muenster.de>', '<http://dbpedia.org/property/name>', '"Universität Münster"@de');
INFO:  SPARQL query sent to 'fuseki':
INSERT DATA { <https://www.uni-muenster.de> <http://dbpedia.org/property/name> "Universität Münster"@de };


SELECT * FROM ft_view;
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o}

INFO:  SPARQL returned 1 record.

            subject            |             predicate              |          object          
-------------------------------+------------------------------------+--------------------------
 <https://www.uni-muenster.de> | <http://dbpedia.org/property/name> | "Universität Münster"@de
(1 row)

UPDATE ft_view SET object = '"University of Münster"@en'
WHERE subject = '<https://www.uni-muenster.de>';
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o
 ## rdf_fdw pushdown conditions ##
 FILTER(?s = <https://www.uni-muenster.de>)
}

INFO:  SPARQL returned 1 record.

INFO:  SPARQL query sent to 'fuseki':
DELETE DATA { <https://www.uni-muenster.de> <http://dbpedia.org/property/name> "Universität Münster"@de };
INSERT DATA { <https://www.uni-muenster.de> <http://dbpedia.org/property/name> "University of Münster"@en };


SELECT * FROM ft_view;
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o}

INFO:  SPARQL returned 1 record.

            subject            |             predicate              |           object           
-------------------------------+------------------------------------+----------------------------
 <https://www.uni-muenster.de> | <http://dbpedia.org/property/name> | "University of Münster"@en
(1 row)

DELETE FROM ft_view;
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o}

INFO:  SPARQL returned 1 record.

INFO:  SPARQL query sent to 'fuseki':
DELETE DATA { <https://www.uni-muenster.de> <http://dbpedia.org/property/name> "University of Münster"@en };


SELECT * FROM ft_view;
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o}

INFO:  SPARQL returned 0 records.

 subject | predicate | object 
---------+-----------+--------
(0 rows)

SELECT * FROM ft; -- must fail: proxy_role's mapping
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o}

ERROR:  proxy authentication required on server "fuseki" (HTTP 407)
RESET ROLE;
DROP VIEW ft_view;
DROP USER MAPPING FOR proxy_role SERVER fuseki;
REVOKE ALL ON ft FROM proxy_role;
REVOKE ALL ON FOREIGN SERVER fuseki FROM proxy_role;
DROP ROLE proxy_role;
\set VERBOSITY default
/* Wrong user - must fail */
ALTER USER MAPPING FOR postgres SERVER fuseki OPTIONS (SET proxy_user 'wronguser');
SELECT * FROM ft;
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o}

ERROR:  proxy authentication required on server "fuseki" (HTTP 407)
DETAIL:  <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta type="copyright" content="Copyright (C) 1996-2021 The Squid Software Foundation and contributors">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>ERROR: Cache Access Denied</title>
<style type="text/css"><!-- 
 /*
 * Copyright (C) 1996-2024 The Squid Software Foundation and contributors
 *
 * Squid software is distributed under GPLv2+ license and includes
 * contributions... (truncated)
HINT:  The endpoint requires proxy authentication. Check the proxy settings.
SELECT * FROM sparql.describe('fuseki', 'DESCRIBE <https://www.uni-muenster.de>');
ERROR:  proxy authentication required on server "fuseki" (HTTP 407)
DETAIL:  <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta type="copyright" content="Copyright (C) 1996-2021 The Squid Software Foundation and contributors">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>ERROR: Cache Access Denied</title>
<style type="text/css"><!-- 
 /*
 * Copyright (C) 1996-2024 The Squid Software Foundation and contributors
 *
 * Squid software is distributed under GPLv2+ license and includes
 * contributions... (truncated)
HINT:  The endpoint requires proxy authentication. Check the proxy settings.
CALL rdf_fdw_clone_table(
        foreign_table => 'public.ft',
        target_table  => 'public.t1'
     );
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o}
ORDER BY ASC(?s) 
OFFSET 0 LIMIT 100

ERROR:  proxy authentication required on server "fuseki" (HTTP 407)
DETAIL:  <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta type="copyright" content="Copyright (C) 1996-2021 The Squid Software Foundation and contributors">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>ERROR: Cache Access Denied</title>
<style type="text/css"><!-- 
 /*
 * Copyright (C) 1996-2024 The Squid Software Foundation and contributors
 *
 * Squid software is distributed under GPLv2+ license and includes
 * contributions... (truncated)
HINT:  The endpoint requires proxy authentication. Check the proxy settings.
/* Wrong password - must fail */
ALTER USER MAPPING FOR postgres SERVER fuseki OPTIONS (SET proxy_user 'proxyuser', SET proxy_password 'wrongpass');
SELECT * FROM ft;
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o}

ERROR:  proxy authentication required on server "fuseki" (HTTP 407)
DETAIL:  <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta type="copyright" content="Copyright (C) 1996-2021 The Squid Software Foundation and contributors">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>ERROR: Cache Access Denied</title>
<style type="text/css"><!-- 
 /*
 * Copyright (C) 1996-2024 The Squid Software Foundation and contributors
 *
 * Squid software is distributed under GPLv2+ license and includes
 * contributions... (truncated)
HINT:  The endpoint requires proxy authentication. Check the proxy settings.
SELECT * FROM sparql.describe('fuseki', 'DESCRIBE <https://www.uni-muenster.de>');
ERROR:  proxy authentication required on server "fuseki" (HTTP 407)
DETAIL:  <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta type="copyright" content="Copyright (C) 1996-2021 The Squid Software Foundation and contributors">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>ERROR: Cache Access Denied</title>
<style type="text/css"><!-- 
 /*
 * Copyright (C) 1996-2024 The Squid Software Foundation and contributors
 *
 * Squid software is distributed under GPLv2+ license and includes
 * contributions... (truncated)
HINT:  The endpoint requires proxy authentication. Check the proxy settings.
CALL rdf_fdw_clone_table(
        foreign_table => 'public.ft',
        target_table  => 'public.t1'
     );
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o}
ORDER BY ASC(?s) 
OFFSET 0 LIMIT 100

ERROR:  proxy authentication required on server "fuseki" (HTTP 407)
DETAIL:  <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta type="copyright" content="Copyright (C) 1996-2021 The Squid Software Foundation and contributors">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>ERROR: Cache Access Denied</title>
<style type="text/css"><!-- 
 /*
 * Copyright (C) 1996-2024 The Squid Software Foundation and contributors
 *
 * Squid software is distributed under GPLv2+ license and includes
 * contributions... (truncated)
HINT:  The endpoint requires proxy authentication. Check the proxy settings.
/* No password - must fail */
ALTER USER MAPPING FOR postgres SERVER fuseki OPTIONS (DROP proxy_password);
SELECT * FROM ft;
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o}

ERROR:  proxy authentication required on server "fuseki" (HTTP 407)
DETAIL:  <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta type="copyright" content="Copyright (C) 1996-2021 The Squid Software Foundation and contributors">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>ERROR: Cache Access Denied</title>
<style type="text/css"><!-- 
 /*
 * Copyright (C) 1996-2024 The Squid Software Foundation and contributors
 *
 * Squid software is distributed under GPLv2+ license and includes
 * contributions... (truncated)
HINT:  The endpoint requires proxy authentication. Check the proxy settings.
SELECT * FROM sparql.describe('fuseki', 'DESCRIBE <https://www.uni-muenster.de>');
ERROR:  proxy authentication required on server "fuseki" (HTTP 407)
DETAIL:  <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta type="copyright" content="Copyright (C) 1996-2021 The Squid Software Foundation and contributors">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>ERROR: Cache Access Denied</title>
<style type="text/css"><!-- 
 /*
 * Copyright (C) 1996-2024 The Squid Software Foundation and contributors
 *
 * Squid software is distributed under GPLv2+ license and includes
 * contributions... (truncated)
HINT:  The endpoint requires proxy authentication. Check the proxy settings.
CALL rdf_fdw_clone_table(
        foreign_table => 'public.ft',
        target_table  => 'public.t1'
     );
INFO:  SPARQL query sent to 'fuseki':

SELECT ?s ?p ?o 
{?s ?p ?o}
ORDER BY ASC(?s) 
OFFSET 0 LIMIT 100

ERROR:  proxy authentication required on server "fuseki" (HTTP 407)
DETAIL:  <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta type="copyright" content="Copyright (C) 1996-2021 The Squid Software Foundation and contributors">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>ERROR: Cache Access Denied</title>
<style type="text/css"><!-- 
 /*
 * Copyright (C) 1996-2024 The Squid Software Foundation and contributors
 *
 * Squid software is distributed under GPLv2+ license and includes
 * contributions... (truncated)
HINT:  The endpoint requires proxy authentication. Check the proxy settings.
/*
 * HTTPS endpoint: the proxy refuses the CONNECT tunnel itself, so the 407 is
 * the proxy's answer, not the endpoint's. It must fail at once, as over HTTP,
 * rather than be retried as if no answer had come.
 */
CREATE SERVER fuseki_https
FOREIGN DATA WRAPPER rdf_fdw
OPTIONS (
  endpoint   'https://fuseki:3030/dt/sparql',
  http_proxy 'http://172.19.42.101:3128',
  connect_timeout '1');
CREATE USER MAPPING FOR postgres
SERVER fuseki_https OPTIONS (proxy_user 'proxyuser', proxy_password 'wrongpass');
CREATE FOREIGN TABLE ft_https (s rdfnode OPTIONS (variable '?s'))
SERVER fuseki_https OPTIONS (sparql 'SELECT ?s WHERE {?s ?p ?o}');
SELECT * FROM ft_https;
ERROR:  proxy authentication required on server "fuseki_https" (HTTP 407)
HINT:  The endpoint requires proxy authentication. Check the proxy settings.
/* Cleanup */
DROP TABLE public.t1;
DROP SERVER fuseki CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to foreign table ft
drop cascades to user mapping for postgres on server fuseki
DROP SERVER fuseki_https CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to user mapping for postgres on server fuseki_https
drop cascades to foreign table ft_https