-- Permissions: a synonym never gives more access than its target
\pset null '(null)'
\set SHOW_CONTEXT never
CREATE ROLE regress_dono LOGIN;      -- owns the data
CREATE ROLE regress_app LOGIN;       -- creates synonyms in its own schema
CREATE ROLE regress_leitor LOGIN;    -- only reads
CREATE ROLE regress_intruso LOGIN;   -- has no rights at all
CREATE SCHEMA erp AUTHORIZATION regress_dono;
CREATE SCHEMA app AUTHORIZATION regress_app;
GRANT USAGE ON SCHEMA erp TO regress_app, regress_leitor;
GRANT USAGE ON SCHEMA app TO regress_leitor, regress_intruso;
SET ROLE regress_dono;
CREATE TABLE erp.salarios (nome text, valor numeric);
INSERT INTO erp.salarios VALUES ('Ana', 1000), ('Bia', 2000);
GRANT SELECT ON erp.salarios TO regress_leitor;
RESET ROLE;
-- regress_app creates a synonym in its own schema, even without access to the data
SET ROLE regress_app;
SELECT synonym.create('app.salario', 'erp.salarios');
 create 
--------
 
(1 row)

SELECT pg_get_userbyid(relowner) AS owner FROM pg_class WHERE oid = 'app.salario'::regclass;
    owner    
-------------
 regress_app
(1 row)

SELECT * FROM app.salario;                -- denied: regress_app cannot read erp.salarios
ERROR:  permission denied for table salarios
RESET ROLE;
-- whoever uses the synonym needs the target's privileges
SET ROLE regress_leitor;
SELECT * FROM app.salario ORDER BY nome;  -- allowed
 nome | valor 
------+-------
 Ana  |  1000
 Bia  |  2000
(2 rows)

INSERT INTO app.salario VALUES ('X', 1);  -- denied: only SELECT was granted on the target
ERROR:  permission denied for table salarios
RESET ROLE;
SET ROLE regress_intruso;
SELECT * FROM app.salario;                -- denied
ERROR:  permission denied for table salarios
RESET ROLE;
-- row-level security on the target applies through the synonym
SET ROLE regress_dono;
ALTER TABLE erp.salarios ENABLE ROW LEVEL SECURITY;
CREATE POLICY so_ana ON erp.salarios FOR SELECT USING (nome = 'Ana');
RESET ROLE;
SET ROLE regress_leitor;
SELECT * FROM app.salario;
 nome | valor 
------+-------
 Ana  |  1000
(1 row)

RESET ROLE;
-- nobody can create synonyms where they cannot create objects
SET ROLE regress_intruso;
SELECT synonym.create('app.hack', 'erp.salarios');
ERROR:  permission denied for schema app
SELECT synonym.create('erp.hack', 'erp.salarios');
ERROR:  permission denied for schema erp
-- nor write to the catalog directly
INSERT INTO synonym.catalog (syn_schema, syn_name, tgt_schema, tgt_name, owner)
VALUES ('app', 'hack', 'erp', 'salarios', 'regress_app');
ERROR:  new row violates row-level security policy for table "catalog"
-- nor drop someone else's synonym
SELECT synonym.drop('app.salario');
ERROR:  must be owner of synonym app.salario
DELETE FROM synonym.catalog RETURNING syn_name;
 syn_name 
----------
(0 rows)

-- nor call the internal function used before DROP TABLE
SELECT synonym._detach(ARRAY[0::oid], true);
ERROR:  permission denied for function _detach
RESET ROLE;
SELECT synonym, target, status FROM synonym.list;
   synonym   |    target    | status 
-------------+--------------+--------
 app.salario | erp.salarios | ok
(1 row)

-- the target's owner can change the table even though the synonym belongs to someone else
SET ROLE regress_dono;
ALTER TABLE erp.salarios ADD COLUMN cargo text;
ALTER TABLE erp.salarios ALTER COLUMN valor TYPE numeric(12,2);
DROP POLICY so_ana ON erp.salarios;
RESET ROLE;
SELECT a.attname, format_type(a.atttypid, a.atttypmod)
FROM pg_attribute a WHERE a.attrelid = 'app.salario'::regclass AND a.attnum > 0 ORDER BY a.attnum;
 attname |  format_type  
---------+---------------
 nome    | text
 valor   | numeric(12,2)
 cargo   | text
(3 rows)

SELECT pg_get_userbyid(relowner) AS owner_after_rebuild FROM pg_class WHERE oid = 'app.salario'::regclass;
 owner_after_rebuild 
---------------------
 regress_app
(1 row)

-- a failed DROP TABLE (not the owner) leaves the synonym untouched
SET ROLE regress_app;
DROP TABLE erp.salarios;
ERROR:  must be owner of table salarios
RESET ROLE;
SELECT synonym, target, status FROM synonym.list;
   synonym   |    target    | status 
-------------+--------------+--------
 app.salario | erp.salarios | ok
(1 row)

-- the table's owner can drop it without CASCADE; the other user's synonym waits and comes back with the table
SET ROLE regress_dono;
DROP TABLE erp.salarios;
CREATE TABLE erp.salarios (nome text);
RESET ROLE;
SELECT synonym, target, status FROM synonym.list;
   synonym   |    target    | status 
-------------+--------------+--------
 app.salario | erp.salarios | ok
(1 row)

SELECT pg_get_userbyid(relowner) AS owner_after_recreate FROM pg_class WHERE oid = 'app.salario'::regclass;
 owner_after_recreate 
----------------------
 regress_app
(1 row)

-- the owner drops the synonym
SET ROLE regress_app;
SELECT synonym.drop('app.salario');
 drop 
------
 
(1 row)

RESET ROLE;
DROP SCHEMA erp CASCADE;
NOTICE:  drop cascades to table erp.salarios
DROP SCHEMA app CASCADE;
DROP ROLE regress_dono, regress_app, regress_leitor, regress_intruso;