test_user_mapping_comments.sql

-- Cleanup/Preparation
\c postgres postgres
DROP FOREIGN DATA WRAPPER IF EXISTS dummy CASCADE;
DROP USER IF EXISTS foo;
DROP USER IF EXISTS bar;
DROP USER IF EXISTS buz;

CREATE USER foo LOGIN;
CREATE USER bar LOGIN;
CREATE USER buz LOGIN;

CREATE FOREIGN DATA WRAPPER dummy;
CREATE SERVER dummy_server FOREIGN DATA WRAPPER dummy;
ALTER SERVER dummy_server OWNER TO foo;

-- Create view for result examination
CREATE OR REPLACE VIEW v_user_mapping_comments AS
SELECT
	coalesce(a.rolname, 'public') AS rolname,
	s.srvname,
	d.description
FROM
	pg_description d
		join pg_user_mapping u ON (u.oid = d.objoid)
		join pg_foreign_server s ON (s.oid = u.umserver)
		left outer join pg_authid a ON (a.oid = u.umuser)
WHERE
	d.classoid = 'pg_user_mapping'::regclass
ORDER BY
	a.rolname,
	s.srvname
;
GRANT SELECT ON v_user_mapping_comments TO public;

-- Create SERVER as non-superuser, and grant USAGE to another non-superuser
\c postgres foo
GRANT USAGE ON FOREIGN SERVER dummy_server TO bar;
CREATE USER MAPPING FOR public SERVER dummy_server;
CREATE USER MAPPING FOR foo SERVER dummy_server;
CREATE USER MAPPING FOR bar SERVER dummy_server;
CREATE USER MAPPING FOR postgres SERVER dummy_server;

-- COMMENT by superuser, OK
\c postgres postgres
COMMENT ON USER MAPPING FOR foo SERVER dummy_server IS 'explicit mapping for foo(postgres)';
COMMENT ON USER MAPPING FOR bar SERVER dummy_server IS 'explicit mapping for bar(postgres)';
COMMENT ON USER MAPPING FOR postgres SERVER dummy_server IS 'explicit mapping for postgres(postgres)';
COMMENT ON USER MAPPING FOR USER SERVER dummy_server IS 'explicit mapping for postgres(postgres)';
COMMENT ON USER MAPPING FOR CURRENT_USER SERVER dummy_server IS 'explicit mapping for postgres(postgres)';
COMMENT ON USER MAPPING FOR public SERVER dummy_server IS 'implicit mapping(postgres)';
SELECT * FROM v_user_mapping_comments;

-- COMMENT by SERVER owner (foo), OK
\c postgres foo
COMMENT ON USER MAPPING FOR foo SERVER dummy_server IS 'explicit mapping for foo(foo)';
COMMENT ON USER MAPPING FOR bar SERVER dummy_server IS 'explicit mapping for bar(foo)';
COMMENT ON USER MAPPING FOR postgres SERVER dummy_server IS 'explicit mapping for postgres(foo)';
COMMENT ON USER MAPPING FOR USER SERVER dummy_server IS 'explicit mapping for foo(foo)';
COMMENT ON USER MAPPING FOR CURRENT_USER SERVER dummy_server IS 'explicit mapping for foo(foo)';
COMMENT ON USER MAPPING FOR public SERVER dummy_server IS 'implicit mapping(foo)';
SELECT * FROM v_user_mapping_comments;

-- COMMENT by SERVER USAGE grantee (bar), OK for only his own mapping
\c postgres bar
COMMENT ON USER MAPPING FOR foo SERVER dummy_server IS 'explicit mapping for foo(bar)';           -- ERROR
COMMENT ON USER MAPPING FOR bar SERVER dummy_server IS 'explicit mapping for bar(bar)';
COMMENT ON USER MAPPING FOR postgres SERVER dummy_server IS 'explicit mapping for postgres(bar)'; -- ERROR
COMMENT ON USER MAPPING FOR USER SERVER dummy_server IS 'explicit mapping for bar(bar)';
COMMENT ON USER MAPPING FOR CURRENT_USER SERVER dummy_server IS 'explicit mapping for bar(bar)';
COMMENT ON USER MAPPING FOR public SERVER dummy_server IS 'implicit mapping(bar)';                -- ERROR
SELECT * FROM v_user_mapping_comments;

-- COMMENT by non-related user, NG
\c postgres buz
COMMENT ON USER MAPPING FOR foo SERVER dummy_server IS 'explicit mapping for foo(bar)';           -- ERROR
COMMENT ON USER MAPPING FOR bar SERVER dummy_server IS 'explicit mapping for bar(bar)';           -- ERROR
COMMENT ON USER MAPPING FOR postgres SERVER dummy_server IS 'explicit mapping for postgres(bar)'; -- ERROR
COMMENT ON USER MAPPING FOR USER SERVER dummy_server IS 'explicit mapping for bar(bar)';          -- ERROR
COMMENT ON USER MAPPING FOR CURRENT_USER SERVER dummy_server IS 'explicit mapping for bar(bar)';  -- ERROR
COMMENT ON USER MAPPING FOR public SERVER dummy_server IS 'implicit mapping(bar)';                -- ERROR
SELECT * FROM v_user_mapping_comments;