(8 rows)

SET ROLE regress_test_role;

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

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

ERROR: permission denied for foreign-data wrapper foo

RESET ROLE;

GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;

SET ROLE regress_test_role;

CREATE SERVERst1 FOREIGN DATA WRAPPER foo;

CREATE SERVERt1 FOREIGN DATA WRAPPER foo;

RESET ROLE;

\des+

List of foreign servers

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}

st1 | regress_test_role | foo | | | |

t1 | regress_test_role | foo | | | |

(9 rows)

REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;

GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect;

SET ROLE regress_test_role;

CREATE SERVERst2 FOREIGN DATA WRAPPER foo; -- ERROR

CREATE SERVERt2 FOREIGN DATA WRAPPER foo; -- ERROR

ERROR: permission denied for foreign-data wrapper foo

RESET ROLE;

GRANT regress_test_indirect TO regress_test_role;

SET ROLE regress_test_role;

CREATE SERVERst2 FOREIGN DATA WRAPPER foo;

CREATE SERVERt2 FOREIGN DATA WRAPPER foo;

\des+

List of foreign servers

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

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}

st1 | regress_test_role | foo | | | |

st2 | regress_test_role | foo | | | |

t1 | regress_test_role | foo | | | |

t2 | regress_test_role | foo | | | |

(10 rows)

RESET ROLE;

: regress_test_role2=U*/foreign_data_user

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 | | | |

st2 | regress_test_role | foo | | | |

t1 | regress_test_role | foo | | | |

t2 | regress_test_role | foo | | | |

(10 rows)

SET ROLE regress_test_role;

: regress_test_role2=U*/foreign_data_user

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

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

st1 | regress_test_role | foo | | | |

st2 | regress_test_role | foo | | | |

t1 | regress_test_role | foo | | | |

t2 | regress_test_role | foo | | | |

(10 rows)

-- DROP SERVER

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

t1 | regress_test_role | foo

t2 | regress_test_role | foo

(10 rows)

SET ROLE regress_test_role;

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

t1 | regress_test_role | foo

t2 | regress_test_role | foo

(9 rows)

ALTER SERVER s2 OWNER TO regress_test_role;

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

t1 | regress_test_role | foo

t2 | regress_test_role | foo

(8 rows)

CREATE USER MAPPING FOR current_user SERVER s3;

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

t1 | regress_test_role | foo

t2 | regress_test_role | foo

(7 rows)

\deu

CREATE USER MAPPING FOR public SERVER s8; -- ERROR

ERROR: must be owner of foreign server s8

RESET ROLE;

ALTER SERVERst1 OWNER TO regress_test_indirect;

ALTER SERVERt1 OWNER TO regress_test_indirect;

SET ROLE regress_test_role;

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

CREATE USER MAPPING FOR public SERVERst1;

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

CREATE USER MAPPING FOR public SERVERt1;

RESET ROLE;

\deu

List of user mappings

s5 | regress_test_role

s6 | regress_test_role

s8 | foreign_data_user

st1 | public

st1 | regress_test_role

t1 | public

t1 | regress_test_role

(7 rows)

-- ALTER USER MAPPING

ALTER USER MAPPING FOR current_user SERVER s5 OPTIONS (ADD modified '1');

ALTER USER MAPPING FOR public SERVER s4 OPTIONS (ADD modified '1'); -- ERROR

ERROR: must be owner of foreign server s4

ALTER USER MAPPING FOR public SERVERst1 OPTIONS (ADD modified '1');

ALTER USER MAPPING FOR public SERVERt1 OPTIONS (ADD modified '1');

RESET ROLE;

\deu+

List of user mappings

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}

t1 | public | {modified=1}

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

(7 rows)

-- DROP USER MAPPING

s6 | regress_test_role

s8 | foreign_data_user

s8 | public

st1 | public

st1 | regress_test_role

t1 | public

t1 | regress_test_role

(8 rows)

-- Information schema

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

regression |t1 | regression | foo | | | regress_test_indirect

regression |t2 | regression | foo | | | regress_test_role

(6 rows)

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

foreign_data_user | regression | s8

PUBLIC | regression | s4

PUBLIC | regression | s8

PUBLIC | regression |st1

PUBLIC | regression |t1

regress_test_role | regression | s5

regress_test_role | regression | s6

regress_test_role | regression |st1

regress_test_role | regression |t1

(8 rows)

SELECT * FROM information_schema.user_mapping_options ORDER BY lower(authorization_identifier), 2, 3, 4;

authorization_identifier | foreign_server_catalog | foreign_server_name | option_name | option_value

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

foreign_data_user | regression | s8 | password | public

PUBLIC | regression | s4 | mapping | is public

PUBLIC | regression |st1 | modified | 1

PUBLIC | regression |t1 | modified | 1

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

regress_test_role | regression |t1 | password | boo

regress_test_role | regression |t1 | username | bob

(7 rows)

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

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

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

PUBLIC | regression |st1 | modified | 1

PUBLIC | regression |t1 | modified | 1

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

regress_test_role | regression |t1 | password | boo

regress_test_role | regression |t1 | username | bob

(5 rows)

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

foreign_data_user | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES

(2 rows)

DROP USER MAPPING FOR current_user SERVERst1;

DROP USER MAPPING FOR current_user SERVERt1;

SET ROLE regress_test_role2;

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

owner of user mapping for regress_test_role

owner of user mapping for regress_test_role

owner of server s5

owner of serverst2

owner of servert2

DROP SERVER s5 CASCADE;

NOTICE: drop cascades to user mapping for regress_test_role

DROP SERVERst1 CASCADE;

DROP SERVERt1 CASCADE;

NOTICE: drop cascades to user mapping for public

DROP SERVERst2;

DROP SERVERt2;

DROP USER MAPPING FOR regress_test_role SERVER s6;

DROP FOREIGN DATA WRAPPER foo CASCADE;

NOTICE: drop cascades to 5 other objects

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

\des+

SET ROLE regress_test_role;

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

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

RESET ROLE;

GRANT USAGEON FOREIGN DATA WRAPPER foo TO regress_test_role;

SET ROLE regress_test_role;

CREATE SERVERst1 FOREIGN DATA WRAPPER foo;

CREATE SERVERt1 FOREIGN DATA WRAPPER foo;

RESET ROLE;

\des+

REVOKE USAGEON FOREIGN DATA WRAPPER fooFROM regress_test_role;

GRANT USAGEON FOREIGN DATA WRAPPER foo TO regress_test_indirect;

SET ROLE regress_test_role;

CREATE SERVERst2 FOREIGN DATA WRAPPER foo;-- ERROR

CREATE SERVERt2 FOREIGN DATA WRAPPER foo;-- ERROR

RESET ROLE;

GRANT regress_test_indirect TO regress_test_role;

SET ROLE regress_test_role;

CREATE SERVERst2 FOREIGN DATA WRAPPER foo;

CREATE SERVERt2 FOREIGN DATA WRAPPER foo;

\des+

RESET ROLE;

REVOKE regress_test_indirectFROM regress_test_role;

CREATEUSERMAPPING FOR public SERVER s8;-- ERROR

RESET ROLE;

ALTER SERVERst1 OWNER TO regress_test_indirect;

ALTER SERVERt1 OWNER TO regress_test_indirect;

SET ROLE regress_test_role;

CREATEUSERMAPPING FORcurrent_user SERVERst1 OPTIONS (username'bob', password'boo');

CREATEUSERMAPPING FOR public SERVERst1;

CREATEUSERMAPPING FORcurrent_user SERVERt1 OPTIONS (username'bob', password'boo');

CREATEUSERMAPPING FOR public SERVERt1;

RESET ROLE;

\deu

SET ROLE regress_test_role;

ALTERUSER MAPPING FORcurrent_user SERVER s5 OPTIONS (ADD modified'1');

ALTERUSER MAPPING FOR public SERVER s4 OPTIONS (ADD modified'1');-- ERROR

ALTERUSER MAPPING FOR public SERVERst1 OPTIONS (ADD modified'1');

ALTERUSER MAPPING FOR public SERVERt1 OPTIONS (ADD modified'1');

RESET ROLE;

\deu+

SELECT*FROMinformation_schema.user_mapping_optionsORDER BY1,2,3,4;

SELECT*FROMinformation_schema.usage_privilegesWHERE object_typeLIKE'FOREIGN%'ORDER BY1,2,3,4,5;

SELECT*FROMinformation_schema.role_usage_grantsWHERE object_typeLIKE'FOREIGN%'ORDER BY1,2,3,4,5;

DROPUSER MAPPING FORcurrent_user SERVERst1;

DROPUSER MAPPING FORcurrent_user SERVERt1;

SET ROLE regress_test_role2;

SELECT*FROMinformation_schema.user_mapping_optionsORDER BY1,2,3,4;

RESET ROLE;

DROP ROLE regress_test_role;-- ERROR

DROP SERVER s5 CASCADE;

DROP SERVERst1 CASCADE;

DROP SERVERst2;

DROP SERVERt1 CASCADE;

DROP SERVERt2;

DROPUSER MAPPING FOR regress_test_role SERVER s6;

DROP FOREIGN DATA WRAPPER foo CASCADE;

DROP SERVER s8 CASCADE;