Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-novice-owner+archive@lists.postgresql.org>)
	id 1m8Xlj-0000qA-9Z
	for pgsql-novice@arkaria.postgresql.org; Wed, 28 Jul 2021 00:49:55 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-novice-owner+archive@lists.postgresql.org>)
	id 1m8Xli-0004Px-AT
	for pgsql-novice@arkaria.postgresql.org; Wed, 28 Jul 2021 00:49:54 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <mitchelln@posteo.net>)
	id 1m8Xli-0004Pp-33
	for pgsql-novice@lists.postgresql.org; Wed, 28 Jul 2021 00:49:54 +0000
Received: from mout02.posteo.de ([185.67.36.66])
	by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <mitchelln@posteo.net>)
	id 1m8Xlf-0002Sp-TQ
	for pgsql-novice@lists.postgresql.org; Wed, 28 Jul 2021 00:49:53 +0000
Received: from submission (posteo.de [89.146.220.130])
	by mout02.posteo.de (Postfix) with ESMTPS id 65FF3240103
	for <pgsql-novice@lists.postgresql.org>;
 Wed, 28 Jul 2021 02:49:50 +0200 (CEST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=posteo.net; s=2017;
	t=1627433390; bh=Zw930vkApnUpFWMuUYCWWSnG7tTXV+pvpgreO4nS2lk=;
	h=From:To:Cc:Subject:Date:From;
	b=C5p9ul4Ls1yfNYqitRR5p3sZ7HgZyh8tXQa/JQzgZCGGEBFKs1CpdIJMkGl60IZr+
	 X+t63qjFUAO8QGJ7THyWebMhJsncw0EmXdHgn1H1QR3xkA0RsKSM+4Ax2fXD4DiwIE
	 RlbsHQ/6PAUlIaLD0H8OlbWTttKTnHyt1csbd03I2plUHtLHTNjiBxZaKvLNSdCxaq
	 O1GkmBY43PTLTjBVFtJDZIYUixUHQtpvN5bQANodq9B3VFmEmFoiNDZ6E4x5CrXcA8
	 keNHmCRQTHgfhIyECwBtSfiCryJipi0eeETKljDIwqM2ubE5Sx2S0FhEMtkV5P81aO
	 e8IuulF5H8vxw==
Received: from customer (localhost [127.0.0.1])
	by submission (posteo.de) with ESMTPSA id 4GZFRx3n25z6tmH;
	Wed, 28 Jul 2021 02:49:49 +0200 (CEST)
From: "Nicolas Mitchell" <mitchelln@posteo.net>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "hubert depesz lubaczewski" <depesz@depesz.com>,
 pgsql-novice <pgsql-novice@lists.postgresql.org>
Subject: Re: Trigger function
Date: Wed, 28 Jul 2021 00:49:48 +0000
Message-ID: <5EA2BFB2-6A89-4402-8F8E-898EF0BB8D20@posteo.net>
In-Reply-To: 
 <CAKFQuwY72k5ngAb+XXwKjBDtqhmqjDXPoAfYGv6AxGXTCsM3NQ@mail.gmail.com>
References: <30B8A3D0-8A87-40E2-BE40-31A17BA4B944@posteo.net>
 <20210727063805.GA28983@depesz.com>
 <2D1DF30A-25BC-48CA-B502-D8B3147B9BEB@posteo.net>
 <CAKFQuwazmQBDYdt+zcw2WV+3bONRjbe7qoHiMnREYj+VRXEXjA@mail.gmail.com>
 <53180757-855C-4FFF-B5CC-572BAEFD9397@posteo.net>
 <CAKFQuwY-1SH+2aGbceazDw+MvrBLC1raD951vBA7LivE2QmNzQ@mail.gmail.com>
 <09E00165-C826-44D5-9CD2-E767DFFEC099@posteo.net>
 <CAKFQuwY72k5ngAb+XXwKjBDtqhmqjDXPoAfYGv6AxGXTCsM3NQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 8bit
List-Id: <pgsql-novice.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-novice@lists.postgresql.org>
List-Owner: <mailto:pgsql-novice-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-novice>
Archived-At: 
 <https://www.postgresql.org/message-id/5EA2BFB2-6A89-4402-8F8E-898EF0BB8D20%40posteo.net>
Precedence: bulk

On 28 Jul 2021, at 1:46, David G. Johnston wrote:

> I'd probably do "CREATE PROCEDURE create_new_host(new_host_name text,
> new_host_domain integer) ... SECURITY DEFINER"
>
> Then grant permission to call that procedure to roles that need to create
> new host records.  Your original CTE would then be executed within the
> procedure.  Roles would not be given permission to insert directly into the
> host or related tables - but the owner of the procedure would.
>

Okay, that’s something I can get my teeth into.

Many thanks,

Nic