Backend initialization may be blocked by locking the database relation id - Mailing list pgsql-hackers

From Alexander Lakhin
Subject Backend initialization may be blocked by locking the database relation id
Date
Msg-id 91c8860a-a866-71a7-a060-3f07af531295@gmail.com
Whole thread Raw
List pgsql-hackers
Hello hackers,

This issue was discussed some time ago as a possible security problem, but
it was concluded that it is not something extraordinary from the security
point of view and it may be a subject for a public discussion.

The issue is that during the backend initialization procedure, the function
InitPostgres() tries to lock the database relation id for the current database
(LockSharedObject(DatabaseRelationId, MyDatabaseId, 0, RowExclusiveLock))
and there is a way for any authenticated user to hold a lock for the database
entry as long as they want. Thus, InitProgress() may be effectively blocked
by that lock.

To observe such blocking, you can apply the patch attached on a client side,
and then do the following with a network-accessible server:
(echo "SELECT '=DISABLE_READ_MARKER=';";
for i in {1..200000}; do echo "ALTER DATABASE postgres SET TABLESPACE xxx;"; done;
) >/tmp/ad.sql

psql postgres -h 10.0.2.2 -U user 2>/dev/null

postgres=> \i /tmp/ad.sql
       ?column?
-----------------------
=DISABLE_READ_MARKER=
(1 row)
...

Several seconds later, try in another console:
psql postgres -h 10.0.2.2 -c "SELECT 1"
You'll get:
psql: FATAL:  canceling statement due to lock timeout

In this case the first client locks the database relation due to:
1. table_open(DatabaseRelationId, RowExclusiveLock) is called in movedb()
before pg_database_ownercheck(), so every user can acquire that lock;
2. the transaction is rolled back (and the lock released) in PostgresMain()
after EmitErrorReport(), so a client can suspend all the transaction-related
activity by blocking a write operation.

Perhaps, that exactly case can be prevented by placing object_ownercheck()
before table_open() in movedb(), but the possibility to stall a transaction
abort looks dangerous too.

Best regards,
Alexander
Attachment

pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: [PoC] pg_upgrade: allow to upgrade publisher node
Next
From: Dmitry Dolgov
Date:
Subject: Re: Schema variables - new implementation for Postgres 15