You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
406 lines
14 KiB
406 lines
14 KiB
-- ===================================================================
|
|
-- create FDW objects
|
|
-- ===================================================================
|
|
\set QUIET on
|
|
SET client_min_messages TO error;
|
|
\set VERBOSITY terse
|
|
|
|
CREATE EXTENSION postgres_fdw;
|
|
|
|
-- We create a username following the same steps as organization members
|
|
CREATE ROLE cdb_fs_tester LOGIN PASSWORD 'cdb_fs_passwd';
|
|
GRANT CONNECT ON DATABASE contrib_regression TO cdb_fs_tester;
|
|
|
|
CREATE ROLE cdb_fs_tester2 LOGIN PASSWORD 'cdb_fs_passwd2';
|
|
GRANT CONNECT ON DATABASE contrib_regression TO cdb_fs_tester2;
|
|
|
|
-- Create database to be used as remote
|
|
CREATE DATABASE cdb_fs_tester OWNER cdb_fs_tester;
|
|
|
|
SELECT 'C1', cartodb.CDB_Federated_Server_Register_PG(server := 'loopback'::text, config := '{
|
|
"server": {
|
|
"host": "localhost",
|
|
"port": @@PGPORT@@
|
|
},
|
|
"credentials": {
|
|
"username": "cdb_fs_tester",
|
|
"password": "cdb_fs_passwd"
|
|
}
|
|
}'::jsonb);
|
|
|
|
|
|
-- ===================================================================
|
|
-- create objects used through FDW loopback server
|
|
-- ===================================================================
|
|
|
|
\c cdb_fs_tester postgres
|
|
|
|
CREATE EXTENSION postgis;
|
|
|
|
\c cdb_fs_tester cdb_fs_tester
|
|
|
|
CREATE SCHEMA remote_schema;
|
|
CREATE TABLE remote_schema.remote_geom(id int, another_field text, geom geometry(Geometry,4326));
|
|
|
|
INSERT INTO remote_schema.remote_geom VALUES (1, 'patata', 'SRID=4326;POINT(1 1)'::geometry);
|
|
INSERT INTO remote_schema.remote_geom VALUES (2, 'patata2', 'SRID=4326;POINT(2 2)'::geometry);
|
|
|
|
CREATE TABLE remote_schema.remote_geom2(cartodb_id bigint, another_field text, the_geom geometry(Geometry,4326), the_geom_webmercator geometry(Geometry,3857));
|
|
|
|
INSERT INTO remote_schema.remote_geom2 VALUES (3, 'patata', 'SRID=4326;POINT(3 3)'::geometry, 'SRID=3857;POINT(3 3)');
|
|
|
|
CREATE TABLE remote_schema.remote_other(id bigint, field text, field2 text);
|
|
INSERT INTO remote_schema.remote_other VALUES (1, 'delicious', 'potatoes');
|
|
|
|
CREATE TABLE remote_schema.remote_geom3(id bigint, geom geometry(Geometry,4326), geom_mercator geometry(Geometry,3857));
|
|
|
|
-- ===================================================================
|
|
-- Test the listing functions
|
|
-- ===================================================================
|
|
|
|
\c contrib_regression postgres
|
|
SET client_min_messages TO error;
|
|
\set VERBOSITY terse
|
|
\set QUIET off
|
|
|
|
\echo '## Registering an existing table works'
|
|
SELECT 'R1', cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom',
|
|
id_column => 'id',
|
|
geom_column => 'geom'
|
|
);
|
|
|
|
SELECT 'S1', cartodb_id, ST_AsText(the_geom), another_field FROM cdb_fs_loopback.remote_geom;
|
|
|
|
Select * FROM CDB_Federated_Server_List_Remote_Tables(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema'
|
|
);
|
|
|
|
\echo '## Registering another existing table works'
|
|
SELECT 'R2', cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom2',
|
|
id_column => 'cartodb_id',
|
|
geom_column => 'the_geom',
|
|
webmercator_column => 'the_geom_webmercator',
|
|
local_name => 'myFullTable'
|
|
);
|
|
|
|
SELECT 'S2', cartodb_id, ST_AsText(the_geom), another_field FROM cdb_fs_loopback."myFullTable";
|
|
Select * FROM CDB_Federated_Server_List_Remote_Tables(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema'
|
|
);
|
|
|
|
\echo '## Re-registering a table works'
|
|
SELECT 'R3', cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom2',
|
|
id_column => 'cartodb_id',
|
|
-- Switch geom and geom_column on purpose to force ST_Transform to be used
|
|
geom_column => 'the_geom_webmercator',
|
|
webmercator_column => 'the_geom',
|
|
local_name => 'different_name'
|
|
);
|
|
|
|
-- The old view should dissapear
|
|
SELECT 'S3_old', cartodb_id, another_field FROM cdb_fs_loopback."myFullTable";
|
|
-- And the new appear
|
|
SELECT 'S3_new', cartodb_id, another_field FROM cdb_fs_loopback.different_name;
|
|
|
|
\echo '## Unregistering works'
|
|
-- Deregistering the first table
|
|
SELECT 'U1', cartodb.CDB_Federated_Table_Unregister(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom'
|
|
);
|
|
-- Selecting from the created view should fail now
|
|
SELECT 'UCheck1', cartodb_id, ST_AsText(the_geom), another_field FROM remote_geom;
|
|
|
|
Select * FROM CDB_Federated_Server_List_Remote_Tables(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema'
|
|
);
|
|
|
|
-- ===================================================================
|
|
-- Test input
|
|
-- ===================================================================
|
|
|
|
\echo '## Registering a table: Invalid server fails'
|
|
SELECT cartodb.CDB_Federated_Table_Register(
|
|
server => 'Does not exist',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom',
|
|
id_column => 'id',
|
|
geom_column => 'geom'
|
|
);
|
|
|
|
\echo '## Registering a table: NULL server fails'
|
|
SELECT cartodb.CDB_Federated_Table_Register(
|
|
server => NULL::text,
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom',
|
|
id_column => 'id',
|
|
geom_column => 'geom'
|
|
);
|
|
|
|
\echo '## Registering a table: Invalid schema fails'
|
|
SELECT cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => 'Does not exist',
|
|
remote_table => 'remote_geom',
|
|
id_column => 'id',
|
|
geom_column => 'geom'
|
|
);
|
|
|
|
\echo '## Registering a table: NULL schema fails'
|
|
SELECT cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => NULL::text,
|
|
remote_table => 'remote_geom',
|
|
id_column => 'id',
|
|
geom_column => 'geom'
|
|
);
|
|
|
|
\echo '## Registering a table: Invalid table fails'
|
|
SELECT cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'Does not exist',
|
|
id_column => 'id',
|
|
geom_column => 'geom'
|
|
);
|
|
|
|
\echo '## Registering a table: NULL table fails'
|
|
SELECT cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => NULL::text,
|
|
id_column => 'id',
|
|
geom_column => 'geom'
|
|
);
|
|
|
|
\echo '## Registering a table: Invalid id fails'
|
|
SELECT cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom',
|
|
id_column => 'Does not exist',
|
|
geom_column => 'geom'
|
|
);
|
|
|
|
\echo '## Registering a table: NULL id fails'
|
|
SELECT cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom',
|
|
id_column => NULL::text,
|
|
geom_column => 'geom'
|
|
);
|
|
|
|
\echo '## Registering a table: Invalid geom_column fails'
|
|
SELECT cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom',
|
|
id_column => 'id',
|
|
geom_column => 'Does not exists'
|
|
);
|
|
|
|
\echo '## Registering a table: NULL geom_column is OK: Reuses geom_mercator'
|
|
SELECT cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom',
|
|
id_column => 'id',
|
|
geom_column => NULL::text,
|
|
webmercator_column => 'geom'
|
|
);
|
|
SELECT * FROM cartodb.CDB_Federated_Server_List_Remote_Tables(server => 'loopback', remote_schema => 'remote_schema') where remote_table = 'remote_geom';
|
|
|
|
SELECT cartodb.CDB_Federated_Table_Unregister(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom'
|
|
);
|
|
|
|
\echo '## Registering a table: Invalid webmercator_column fails'
|
|
SELECT cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom',
|
|
id_column => 'id',
|
|
geom_column => 'geom',
|
|
webmercator_column => 'Does not exists'
|
|
);
|
|
|
|
\echo '## Registering a table: NULL webmercator_column is OK: Reuses geom'
|
|
SELECT cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom',
|
|
id_column => 'id',
|
|
geom_column => 'geom',
|
|
webmercator_column => NULL::text
|
|
);
|
|
SELECT * FROM cartodb.CDB_Federated_Server_List_Remote_Tables(server => 'loopback', remote_schema => 'remote_schema') where remote_table = 'remote_geom';
|
|
SELECT cartodb.CDB_Federated_Table_Unregister(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom'
|
|
);
|
|
|
|
\echo '##Registering a table with extra columns show the correct information'
|
|
SELECT cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom3',
|
|
id_column => 'id',
|
|
geom_column => 'geom',
|
|
webmercator_column => 'geom_mercator'
|
|
);
|
|
Select * FROM cartodb.CDB_Federated_Server_List_Remote_Tables(server => 'loopback', remote_schema => 'remote_schema') where remote_table = 'remote_geom3';
|
|
SELECT cartodb.CDB_Federated_Table_Unregister(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom3'
|
|
);
|
|
|
|
-- ===================================================================
|
|
-- Test conflicts
|
|
-- ===================================================================
|
|
|
|
\echo '## Target conflict is handled nicely: Table'
|
|
CREATE TABLE cdb_fs_loopback.localtable (a integer);
|
|
SELECT cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom',
|
|
id_column => 'id',
|
|
geom_column => 'geom',
|
|
local_name => 'localtable');
|
|
|
|
\echo '## Target conflict is handled nicely: View'
|
|
CREATE VIEW cdb_fs_loopback.localtable2 AS Select * from cdb_fs_loopback.localtable;
|
|
SELECT cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom',
|
|
id_column => 'id',
|
|
geom_column => 'geom',
|
|
local_name => 'localtable2');
|
|
|
|
DROP VIEW cdb_fs_loopback.localtable2;
|
|
DROP TABLE cdb_fs_loopback.localtable;
|
|
|
|
-- ===================================================================
|
|
-- Test permissions
|
|
-- ===================================================================
|
|
|
|
|
|
\echo '## Registering tables does not work without permissions'
|
|
\c contrib_regression cdb_fs_tester
|
|
SELECT cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom',
|
|
id_column => 'id',
|
|
geom_column => 'geom',
|
|
local_name => 'localtable');
|
|
|
|
\echo '## Normal users can not write in the import schema'
|
|
CREATE TABLE cdb_fs_loopback.localtable (a integer);
|
|
|
|
\echo '## Listing remote tables does not work without permissions'
|
|
Select * FROM cartodb.CDB_Federated_Server_List_Remote_Tables(server => 'loopback', remote_schema => 'remote_schema');
|
|
|
|
\echo '## Registering tables works with granted permissions'
|
|
\c contrib_regression postgres
|
|
SELECT cartodb.CDB_Federated_Server_Grant_Access(server := 'loopback', db_role := 'cdb_fs_tester'::name);
|
|
\c contrib_regression cdb_fs_tester
|
|
SELECT cartodb.CDB_Federated_Table_Register(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom',
|
|
id_column => 'id',
|
|
geom_column => 'geom',
|
|
local_name => 'localtable');
|
|
|
|
\echo '## Listing remote tables works with granted permissions'
|
|
Select * FROM cartodb.CDB_Federated_Server_List_Remote_Tables(server => 'loopback', remote_schema => 'remote_schema');
|
|
|
|
\echo '## Selecting from a registered table with granted permissions works'
|
|
Select cartodb_id, ST_AsText(the_geom) from cdb_fs_loopback.localtable;
|
|
|
|
\echo '## Selecting from a registered table without permissions does not work'
|
|
\c contrib_regression cdb_fs_tester2
|
|
CREATE OR REPLACE FUNCTION catch_permission_error(query text)
|
|
RETURNS bool
|
|
AS $$
|
|
BEGIN
|
|
EXECUTE query;
|
|
RETURN FALSE;
|
|
EXCEPTION
|
|
WHEN insufficient_privilege THEN
|
|
RETURN TRUE;
|
|
WHEN OTHERS THEN
|
|
RAISE WARNING 'Exception %', sqlstate;
|
|
RETURN FALSE;
|
|
END
|
|
$$ LANGUAGE 'plpgsql';
|
|
Select catch_permission_error($$SELECT cartodb_id, ST_AsText(the_geom) from cdb_fs_loopback.localtable$$);
|
|
DROP FUNCTION catch_permission_error(text);
|
|
|
|
\echo '## Deleting a registered table without permissions does not work'
|
|
SELECT cartodb.CDB_Federated_Table_Unregister(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom'
|
|
);
|
|
|
|
\echo '## Only the owner can grant permissions over the server'
|
|
SELECT cartodb.CDB_Federated_Server_Grant_Access(server := 'loopback', db_role := 'cdb_fs_tester2'::name);
|
|
|
|
\echo '## Everything works for a different user when granted permissions'
|
|
\c contrib_regression postgres
|
|
SELECT cartodb.CDB_Federated_Server_Grant_Access(server := 'loopback', db_role := 'cdb_fs_tester2'::name);
|
|
\c contrib_regression cdb_fs_tester2
|
|
Select * FROM cartodb.CDB_Federated_Server_List_Remote_Tables(server => 'loopback', remote_schema => 'remote_schema');
|
|
Select cartodb_id, ST_AsText(the_geom) from cdb_fs_loopback.localtable;
|
|
|
|
\echo '## A different user can unregister a table'
|
|
SELECT cartodb.CDB_Federated_Table_Unregister(
|
|
server => 'loopback',
|
|
remote_schema => 'remote_schema',
|
|
remote_table => 'remote_geom'
|
|
);
|
|
Select * FROM cartodb.CDB_Federated_Server_List_Remote_Tables(server => 'loopback', remote_schema => 'remote_schema');
|
|
|
|
\echo '## Only the owner can revoke permissions over the server'
|
|
SELECT cartodb.CDB_Federated_Server_Revoke_Access(server := 'loopback', db_role := 'cdb_fs_tester'::name);
|
|
|
|
-- ===================================================================
|
|
-- Cleanup
|
|
-- ===================================================================
|
|
|
|
\set QUIET on
|
|
\c contrib_regression postgres
|
|
SET client_min_messages TO error;
|
|
\set VERBOSITY terse
|
|
|
|
REVOKE CONNECT ON DATABASE contrib_regression FROM cdb_fs_tester2;
|
|
DROP ROLE cdb_fs_tester2;
|
|
|
|
SELECT 'D1', cartodb.CDB_Federated_Server_Unregister(server := 'loopback'::text);
|
|
DROP DATABASE cdb_fs_tester;
|
|
REVOKE CONNECT ON DATABASE contrib_regression FROM cdb_fs_tester;
|
|
DROP ROLE cdb_fs_tester;
|
|
DROP EXTENSION postgres_fdw;
|
|
\set QUIET off
|