Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u4iy0-006Reb-EL for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Apr 2025 16:16:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u4ixy-00Crk1-CS for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Apr 2025 16:16:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u4ixx-00Crjs-Rr for pgsql-hackers@lists.postgresql.org; Tue, 15 Apr 2025 16:16:54 +0000 Received: from crab.cedar.relay.mailchannels.net ([23.83.210.42]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1u4ixu-000F2M-2O for pgsql-hackers@lists.postgresql.org; Tue, 15 Apr 2025 16:16:54 +0000 X-Sender-Id: dreamhost|x-authsender|nico@cryptonector.com Received: from relay.mailchannels.net (localhost [127.0.0.1]) by relay.mailchannels.net (Postfix) with ESMTP id 5D33A824DA; Tue, 15 Apr 2025 16:16:49 +0000 (UTC) Received: from pdx1-sub0-mail-a272.dreamhost.com (100-102-138-80.trex-nlb.outbound.svc.cluster.local [100.102.138.80]) (Authenticated sender: dreamhost) by relay.mailchannels.net (Postfix) with ESMTPA id 07F4C82AF6; Tue, 15 Apr 2025 16:16:49 +0000 (UTC) ARC-Seal: i=1; s=arc-2022; d=mailchannels.net; t=1744733809; a=rsa-sha256; cv=none; b=UMoaMsIabSWJ9otnG+jJgDJHohA6cNUT+KW/Pt1i9LEef5qaXF+vjvoHv5AGiRcLCE2x1K pMmrUo9XTbvVuhfRFFgzE3zqmC9BLtLzMb/+7a0/sMcdzxluTEHSBthz3M6yqC1ZMXSEiD LVc1AUn6+0CXcxxQg88xXwHOUs7YaSQCq1TXKSJYeiVgJZG0+TXDOOGBRINSg244aw6fc9 5DoMNCXxGbJFdcNTTjSqI6NF24ojlxbJGzNWm3DoJopCyNkx4HMqVzlaR50wIubbZ6O5g1 c86iMrr7C9tJWNqo0+J2T7G8j5x+pxqURtBWV0Dda5t8NfQr1VWZjDjdzIyR4A== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=mailchannels.net; s=arc-2022; t=1744733809; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references:dkim-signature; bh=8D2hvYTA8mvSASfy4DB7C7DMzmCJBl55D1J/7f7vW9o=; b=l4+8T3j5DA2/+9rS8WQmJKJlIyZZQyKWuDRUvQRHeZsDza5EoIsbJA+XFuDtlsq1DFTPca 0joGo6q5564Jy/5yOHDO6qka0pOBn5G4aA6lN6GJMLO0HhPt9O/wCU7x/hjCJBKq9OKPWM WMmnljb/1wZTIJAdXmPQRQS5DQAvz3NG+Z2QoUUi6hLkJgwDJOcQeGpvIaOLx9DkZk6FOi joWWrIQ8HUeWXpo1yVw2GCGWgVJ+0SKyEOrctTwqtIahmKrYG00gWsAlFMSiRZLN6qzmeH 8RAM3DAXUxY0Blk4m5+/SF8ZgGM1Qho83FQ/0PhZhCJlGRf+RH6rG3K0NgsPIQ== ARC-Authentication-Results: i=1; rspamd-66f96cc86b-7crms; auth=pass smtp.auth=dreamhost smtp.mailfrom=nico@cryptonector.com X-Sender-Id: dreamhost|x-authsender|nico@cryptonector.com X-MC-Relay: Bad X-MailChannels-SenderId: dreamhost|x-authsender|nico@cryptonector.com X-MailChannels-Auth-Id: dreamhost X-Descriptive-Whispering: 03e029400e3e5f3e_1744733809251_1086013260 X-MC-Loop-Signature: 1744733809251:2185425369 X-MC-Ingress-Time: 1744733809251 Received: from pdx1-sub0-mail-a272.dreamhost.com (pop.dreamhost.com [64.90.62.162]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384) by 100.102.138.80 (trex/7.0.3); Tue, 15 Apr 2025 16:16:49 +0000 Received: from ubby (syn-075-081-095-064.res.spectrum.com [75.81.95.64]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-256) server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) (Authenticated sender: nico@cryptonector.com) by pdx1-sub0-mail-a272.dreamhost.com (Postfix) with ESMTPSA id 4ZcTnD3f2Vz16; Tue, 15 Apr 2025 09:16:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cryptonector.com; s=dreamhost; t=1744733808; bh=8D2hvYTA8mvSASfy4DB7C7DMzmCJBl55D1J/7f7vW9o=; h=Date:From:To:Cc:Subject:Content-Type; b=iFBhSuyikum6XruuVfWBeivAH669GMJI75TJJiKk76VqyQ0RP4h8AfvKmStiCNSnq YFXBUWqzHLrVOYGID2Ck1n7HZPNL19ywKqUh/qCvwCdc6tweptfRbKjyZK6gOhvBwQ L1Sg7oSnu/fUd8Xa/VfWgCkZcgfeJ32GuFORxI8Oh1aEdEBa9SOHXjBkXXf4HhsLSV WrzM5K5ll+XBiy06WExBa8AYgPjxVltTBeVid7Pn08wjtXJ2cHvosPCgrl+5HgyIo2 HeiVKfD4zOMFZVCkpTgWaqeBWxfJpaP7+cd2RT7v4G0sTrnDi2n9ulS69B8R9fGuny P00w3FyuC3D2w== Date: Tue, 15 Apr 2025 11:16:46 -0500 From: Nico Williams To: Laurenz Albe Cc: pgsql-hackers@lists.postgresql.org Subject: Re: Wrong security context for deferred triggers? Message-ID: References: <77ee784cf248e842f74588418f55c2931e47bd78.camel@cybertec.at> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <77ee784cf248e842f74588418f55c2931e47bd78.camel@cybertec.at> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Mon, Nov 06, 2023 at 02:23:04PM +0100, Laurenz Albe wrote: > SET CONSTRAINTS ALL DEFERRED; Some years ago I wrote and submitted a patch to allow one to create constraints that are ALWAYS DEFERRED so that they cannot be made IMMEDIATE with SET CONSTRAINTS ALL IMMEDIATE. I do think that one could use SET CONSTRAINTS ALL ... to defeat [poorly-coded, perhaps] security measures taken by triggers. An example of where one might want triggers that are always deferred would be a ledger application requiring double-entry, where one might use deferred triggers to check that all debits have matching credits at commit-time. Nico --