Backend initialization may be blocked by locking the database relation id

From: Alexander Lakhin <exclusion(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Backend initialization may be blocked by locking the database relation id
Date: 2023-08-12 09:00:00
Message-ID: 91c8860a-a866-71a7-a060-3f07af531295@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 Content-Type Size
fe-misc-disable-read.patch text/x-patch 1.3 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Dmitry Dolgov 2023-08-12 11:20:03 Re: Schema variables - new implementation for Postgres 15
Previous Message Amit Kapila 2023-08-12 06:20:36 Re: [PoC] pg_upgrade: allow to upgrade publisher node