CREATE SERVER s4 TYPE 'oracle' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');

CREATE SERVER s5 VERSION '15.0' FOREIGN DATA WRAPPER foo;

CREATE SERVER s6 VERSION '16.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');

CREATE SERVER "S6" FOREIGN DATA WRAPPER foo OPTIONS (mixed_case_names 'true');

CREATE SERVER s7 TYPE 'oracle' VERSION '17.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');

CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (foo '1'); -- ERROR

ERROR: invalid option "foo" to server

List of foreign servers

Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options

------+-------------------+----------------------+-------------------+--------+---------+------------------------------

S6 | foreign_data_user | foo | | | | {mixed_case_names=true}

s1 | foreign_data_user | foo | | | |

s2 | foreign_data_user | foo | | | | {host=a,dbname=b}

s3 | foreign_data_user | foo | | oracle | |

s6 | foreign_data_user | foo | | | 16.0 | {host=a,dbname=b}

s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}

s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db}

(9 rows)

(8 rows)

SET ROLE regress_test_role;

CREATE SERVER st1 FOREIGN DATA WRAPPER foo; -- ERROR: no usage on FDW

List of foreign servers

Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options

------+-------------------+----------------------+-------------------+--------+---------+------------------------------

S6 | foreign_data_user | foo | | | | {mixed_case_names=true}

s1 | foreign_data_user | foo | | | |

s2 | foreign_data_user | foo | | | | {host=a,dbname=b}

s3 | foreign_data_user | foo | | oracle | |

s7 | foreign_data_user | foo | | oracle | 17.0 | {host=a,dbname=b}

s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db}

st1 | regress_test_role | foo | | | |

(10 rows)

(9 rows)

REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;

GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect;

List of foreign servers

Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options

------+-------------------+----------------------+-------------------+--------+---------+------------------------------

S6 | foreign_data_user | foo | | | | {mixed_case_names=true}

s1 | foreign_data_user | foo | | | |

s2 | foreign_data_user | foo | | | | {host=a,dbname=b}

s3 | foreign_data_user | foo | | oracle | |

s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db}

st1 | regress_test_role | foo | | | |

st2 | regress_test_role | foo | | | |

(11 rows)

(10 rows)

RESET ROLE;

REVOKE regress_test_indirect FROM regress_test_role;

List of foreign servers

Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options

------+-------------------+----------------------+-----------------------------------------+--------+---------+------------------------------

S6 | foreign_data_user | foo | | | | {mixed_case_names=true}

s1 | foreign_data_user | foo | foreign_data_user=U/foreign_data_user | | 1.0 | {servername=s1}

: regress_test_role=U/foreign_data_user

s2 | foreign_data_user | foo | | | 1.1 | {host=a,dbname=b}

s8 | foreign_data_user | postgresql | | | | {host=localhost,dbname=s8db}

st1 | regress_test_role | foo | | | |

st2 | regress_test_role | foo | | | |

(11 rows)

(10 rows)

SET ROLE regress_test_role;

ALTER SERVER s1 VERSION '1.1'; -- ERROR

List of foreign servers

Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | Options

------+-----------------------+----------------------+-----------------------------------------+--------+---------+---------------------------------

S6 | foreign_data_user | foo | | | | {mixed_case_names=true}

s1 | regress_test_indirect | foo | foreign_data_user=U/foreign_data_user | | 1.1 | {servername=s1}

: regress_test_role=U/foreign_data_user

s2 | foreign_data_user | foo | | | 1.1 | {host=a,dbname=b}

s8 | foreign_data_user | postgresql | | | | {dbname=db1,connect_timeout=30}

st1 | regress_test_role | foo | | | |

st2 | regress_test_role | foo | | | |

(11 rows)

(10 rows)

-- DROP SERVER

DROP SERVER nonexistent; -- ERROR

List of foreign servers

Name | Owner | Foreign-data wrapper

------+-----------------------+----------------------

S6 | foreign_data_user | foo

s1 | regress_test_indirect | foo

s2 | foreign_data_user | foo

s3 | foreign_data_user | foo

s8 | foreign_data_user | postgresql

st1 | regress_test_role | foo

st2 | regress_test_role | foo

(11 rows)

(10 rows)

SET ROLE regress_test_role;

DROP SERVER s2; -- ERROR

List of foreign servers

Name | Owner | Foreign-data wrapper

------+-------------------+----------------------

S6 | foreign_data_user | foo

s2 | foreign_data_user | foo

s3 | foreign_data_user | foo

s4 | foreign_data_user | foo

s8 | foreign_data_user | postgresql

st1 | regress_test_role | foo

st2 | regress_test_role | foo

(10 rows)

(9 rows)

ALTER SERVER s2 OWNER TO regress_test_role;

SET ROLE regress_test_role;

List of foreign servers

Name | Owner | Foreign-data wrapper

------+-------------------+----------------------

S6 | foreign_data_user | foo

s3 | foreign_data_user | foo

s4 | foreign_data_user | foo

s5 | foreign_data_user | foo

s8 | foreign_data_user | postgresql

st1 | regress_test_role | foo

st2 | regress_test_role | foo

(9 rows)

(8 rows)

CREATE USER MAPPING FOR current_user SERVER s3;

\deu

List of foreign servers

Name | Owner | Foreign-data wrapper

------+-------------------+----------------------

S6 | foreign_data_user | foo

s4 | foreign_data_user | foo

s5 | foreign_data_user | foo

s6 | foreign_data_user | foo

s7 | foreign_data_user | foo

s8 | foreign_data_user | postgresql

st1 | regress_test_role | foo

st2 | regress_test_role | foo

(8 rows)

(7 rows)

\deu

List of user mappings

CREATE USER MAPPING FOR public SERVER s8; -- ERROR

ERROR: must be owner of foreign server s8

RESET ROLE;

CREATE USER MAPPING FOR current_user SERVER "S6" OPTIONS (username 'test_mixed_case');

ALTER SERVER st1 OWNER TO regress_test_indirect;

SET ROLE regress_test_role;

CREATE USER MAPPING FOR current_user SERVER st1 OPTIONS (username 'bob', password 'boo');

List of user mappings

Server | Username

--------+-------------------

S6 | foreign_data_user

s4 | foreign_data_user

s4 | public

s5 | regress_test_role

s6 | regress_test_role

s8 | foreign_data_user

st1 | public

st1 | regress_test_role

(8 rows)

(7 rows)

-- ALTER USER MAPPING

ALTER USER MAPPING FOR bob SERVER s4 OPTIONS (gotcha 'true'); -- ERROR

List of user mappings

Server | Username | Options

--------+-------------------+-----------------------------

S6 | foreign_data_user | {username=test_mixed_case}

s4 | foreign_data_user |

s4 | public | {"mapping=is public"}

s5 | regress_test_role | {modified=1}

s6 | regress_test_role | {username=test}

s8 | foreign_data_user | {password=public}

st1 | public | {modified=1}

st1 | regress_test_role | {username=bob,password=boo}

(8 rows)

(7 rows)

-- DROP USER MAPPING

DROP USER MAPPING FOR bob SERVER s4; -- ERROR

List of user mappings

Server | Username

--------+-------------------

S6 | foreign_data_user

s4 | foreign_data_user

s4 | public

s5 | regress_test_role

s8 | public

st1 | public

st1 | regress_test_role

(9 rows)

(8 rows)

-- Information schema

SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;

SELECT * FROM information_schema.foreign_servers ORDER BY 1, 2;

foreign_server_catalog | foreign_server_name | foreign_data_wrapper_catalog | foreign_data_wrapper_name | foreign_server_type | foreign_server_version | authorization_identifier

------------------------+---------------------+------------------------------+---------------------------+---------------------+------------------------+--------------------------

regression | S6 | regression | foo | | | foreign_data_user

regression | s4 | regression | foo | oracle | | foreign_data_user

regression | s5 | regression | foo | | 15.0 | regress_test_role

regression | s6 | regression | foo | | 16.0 | regress_test_indirect

regression | s8 | regression | postgresql | | | foreign_data_user

regression | st1 | regression | foo | | | regress_test_indirect

regression | st2 | regression | foo | | | regress_test_role

(7 rows)

(6 rows)

SELECT * FROM information_schema.foreign_server_options ORDER BY 1, 2, 3;

foreign_server_catalog | foreign_server_name | option_name | option_value

------------------------+---------------------+------------------+--------------

regression | S6 | mixed_case_names | true

regression | s4 | dbname | b

regression | s4 | host | a

regression | s6 | dbname | b

regression | s6 | host | a

regression | s8 | connect_timeout | 30

regression | s8 | dbname | db1

(7 rows)

foreign_server_catalog | foreign_server_name | option_name | option_value

------------------------+---------------------+-----------------+--------------

regression | s4 | dbname | b

regression | s4 | host | a

regression | s6 | dbname | b

regression | s6 | host | a

regression | s8 | connect_timeout | 30

regression | s8 | dbname | db1

(6 rows)

SELECT * FROM information_schema.user_mappings ORDER BY 1, 2, 3;

authorization_identifier | foreign_server_catalog | foreign_server_name

--------------------------+------------------------+---------------------

PUBLIC | regression | s4

PUBLIC | regression | s8

PUBLIC | regression | st1

foreign_data_user | regression | S6

foreign_data_user | regression | s4

foreign_data_user | regression | s8

regress_test_role | regression | s5

regress_test_role | regression | s6

regress_test_role | regression | st1

(9 rows)

(8 rows)

SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;

authorization_identifier | foreign_server_catalog | foreign_server_name | option_name |option_value

--------------------------+------------------------+---------------------+-------------+-----------------

authorization_identifier | foreign_server_catalog | foreign_server_name | option_name | option_value

--------------------------+------------------------+---------------------+-------------+--------------

PUBLIC | regression | s4 | mapping | is public

PUBLIC | regression | st1 | modified | 1

foreign_data_user | regression | S6 | username | test_mixed_case

foreign_data_user | regression | s8 | password | public

regress_test_role | regression | s5 | modified | 1

regress_test_role | regression | s6 | username | test

regress_test_role | regression | st1 | password | boo

regress_test_role | regression | st1 | username | bob

(8 rows)

(7 rows)

SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;

grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable

DROP SERVER st2;

DROP USER MAPPING FOR regress_test_role SERVER s6;

DROP FOREIGN DATA WRAPPER foo CASCADE;

NOTICE: drop cascades to6 other objects

NOTICE: drop cascades to4 other objects

DETAIL: drop cascades to server s4

drop cascades to user mapping for foreign_data_user

drop cascades to server s6

drop cascades to server S6

drop cascades to user mapping for foreign_data_user

drop cascades to server s9

DROP SERVER s8 CASCADE;

NOTICE: drop cascades to 2 other objects

CREATE SERVER s4 TYPE'oracle' FOREIGN DATA WRAPPER foo OPTIONS (host'a', dbname'b');

CREATE SERVER s5 VERSION'15.0' FOREIGN DATA WRAPPER foo;

CREATE SERVER s6 VERSION'16.0' FOREIGN DATA WRAPPER foo OPTIONS (host'a', dbname'b');

CREATE SERVER"S6" FOREIGN DATA WRAPPER foo OPTIONS (mixed_case_names'true');

CREATE SERVER s7 TYPE'oracle' VERSION'17.0' FOREIGN DATA WRAPPER foo OPTIONS (host'a', dbname'b');

CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (foo'1');-- ERROR

CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host'localhost', dbname's8db');

CREATEUSERMAPPING FORcurrent_user SERVER s7;-- ERROR

CREATEUSERMAPPING FOR public SERVER s8;-- ERROR

RESET ROLE;

CREATEUSERMAPPING FORcurrent_user SERVER"S6" OPTIONS (username'test_mixed_case');

ALTER SERVER st1 OWNER TO regress_test_indirect;

SET ROLE regress_test_role;