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
