update_pg_pwd trigger does not work very well

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: update_pg_pwd trigger does not work very well
Date: 2000-02-27 04:48:49
Message-ID: 11585.951626929@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I was looking at the trigger function that's been added to try to
update pg_pwd automatically if pg_shadow is updated via standard
SQL commands. It's got some problems:

1. Since the trigger is executed as soon as a tuple is inserted/
updated/deleted, it will write pg_pwd before the transaction is
committed. If you then abort the transaction, pg_pwd contains wrong
data. Even if you don't abort, the postmaster may read and act on the
updated pg_pwd before you've committed, which could have bad
consequences (logging in a user who doesn't exist yet, for example).

2. The trigger tries to grab AccessExclusiveLock on pg_shadow.
Since this is being done in the middle of a transaction that has
previously grabbed some lower level of lock on pg_shadow, it's
very easy to create a deadlock situation. All you need is two
different transactions modifying pg_shadow concurrently, and
it'll fail.

3. CREATE USER and friends refuse to run inside a transaction block
in the vain hope of making life safe for the trigger. It's vain
since the above problems will occur anyway, if one simply alters
pg_shadow using ordinary SQL commands. (And if we're not going to
support that, why bother with the trigger?) I think this is a rather
unpleasant restriction, especially so when it isn't buying any
safety at all.

A possible solution for these problems is to have the trigger procedure
itself do nothing except set a flag variable. The flag is examined
somewhere in xact.c after successful completion of a transaction,
and if it's set then we run a new transaction cycle in which we
read pg_shadow and write pg_pwd. (A new transaction is needed so
that it's safe to demand AccessExclusiveLock on pg_shadow --- we
have to release all our old locks before we can do that.) Note that
*only* this second transaction would need AccessExclusiveLock; CREATE
USER and friends would not.

I am not quite certain that this is completely bulletproof when there
are multiple backends concurrently updating pg_shadow, but I have not
been able to think of a case where it'd fail. The worst possibility
is that a committed update in pg_shadow might not get propagated to
pg_pwd for a while because some other transaction is holding a lock on
pg_shadow. (But pg_pwd updates can be delayed for that reason now,
so it's certainly no worse than before.)

Comments?

regards, tom lane

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2000-02-27 05:35:42 Re: [HACKERS] update_pg_pwd trigger does not work very well
Previous Message Tom Lane 2000-02-27 01:47:08 Re: [INTERFACES] Date: Sat, 26 Feb 2000 17:01:55 -0800