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 ) id 1plAKq-00022u-3M for pgsql-general@arkaria.postgresql.org; Sat, 08 Apr 2023 15:18:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1plAKo-0008FR-Pf for pgsql-general@arkaria.postgresql.org; Sat, 08 Apr 2023 15:18:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1plAKm-0008FI-Va for pgsql-general@lists.postgresql.org; Sat, 08 Apr 2023 15:18:34 +0000 Received: from wout2-smtp.messagingengine.com ([64.147.123.25]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1plAKj-0001zO-Dx for pgsql-general@lists.postgresql.org; Sat, 08 Apr 2023 15:18:31 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.west.internal (Postfix) with ESMTP id E9ED8320051E; Sat, 8 Apr 2023 11:18:26 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute2.internal (MEProxy); Sat, 08 Apr 2023 11:18:27 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm1; t= 1680967106; x=1681053506; bh=PMwfrX5tjhqCXkhPnp5vdPOR4Bp+wdmYHVu Hxbr4xek=; b=NKfCo1ULZpCB11eyEOrMDjp/eZsieOqThEdXClgoLrsoZy7LCgx xkbfI9KNxnGnjZG7ZOZs+CbjQi5iWRRX8HnwFo+8mFZBazL3kpoKGQmHW0wUK4Bn pYpaw/F48j9jxzv7y210qDJy++0rnhWjCZkr3lvEuW+op7zuVpaNDb8YCaanTx/X VzRNrjPvg78B3DG9J2SHBYQosfPRO8aexsQComDdEhZjWqy8OUxsacCnFD/er8p/ Yo9fXgFV85CcPzlBxp7SNxNN5OP/cqHS21E7D/M7L5OwKM/wKcFZxnltglJXp5C8 Hgl4AAsgre4/HprD8zcPHAyVmVRqPdluT1A== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t=1680967106; x= 1681053506; bh=PMwfrX5tjhqCXkhPnp5vdPOR4Bp+wdmYHVuHxbr4xek=; b=C wOgo+9piiCmssFWVuTg4/crELAU5pt3s4Y+GpZsWJlrrc9PRLAp9rlvBvY1JGnQP 1u3oBt4yA8v7+sJQpznbh2rfBUPMjTSfexPpqC8H6ETJmg2gekyvBMPCW8n5wiLU 8+kDqJpJfI/flmxgiwyA9/xp4+mIQTl575ziFodSYn9NTlDIt9qLNozGdlvuaPrr za1sk3sQf153FfdDO8ZvP1xOT4Eyc9TocpwGsJYb1zTQ4MZcInkX0/tqSP56T81I yJT+vVezWKRzx6V8secB98aqOe9KAj5ifKpjce1dRKgXC12OV6bvcCMxq9X0SI5s HAkV9Z6H+9mxNb13VxEIw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrvdejjedgkeekucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvfhfhjggtgfesthekredttdefjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeelkeegjefhkeetheefudevteehiedvudehkeduleet fedvffeuhedtvdegtdeffeenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorhhgne cuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhi rghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Sat, 8 Apr 2023 11:18:25 -0400 (EDT) Message-ID: <9c8846fe-af91-a2ee-b565-9d55856b6081@aklaver.com> Date: Sat, 8 Apr 2023 08:18:24 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.9.1 Subject: Re: doc sql-grant.html Synopsis error? To: Achilleas Mantzios , pgsql-general@lists.postgresql.org References: <00828709-45d1-f82d-e270-7c9d4d52373e@cloud.gatewaynet.com> Content-Language: en-US From: Adrian Klaver In-Reply-To: <00828709-45d1-f82d-e270-7c9d4d52373e@cloud.gatewaynet.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 4/8/23 06:07, Achilleas Mantzios wrote: > Στις 8/4/23 15:58, ο/η jian he έγραψε: >> >> Hi. >> --work as intended. >> grant ALL PRIVILEGES on FUNCTION pg_catalog.pg_reload_conf() to test; >> grant ALL PRIVILEGES on FUNCTION pg_reload_conf() to test; >> >> -------------errors. it should be work, or I interpret the doc the >> wrong way? >> GRANT ALL PRIVILEGES ON FUNCTION pg_reload_conf() IN SCHEMA pg_catalog >> TO test; >> GRANT ALL PRIVILEGES ON FUNCTION pg_catalog.pg_reload_conf() IN SCHEMA >> pg_catalog TO test; >> >> doc: https://www.postgresql.org/docs/current/sql-grant.html >> GRANT { EXECUTE | ALL [ PRIVILEGES ] } >> ON { { FUNCTION | PROCEDURE | ROUTINE }/|routine_name|/ [ ( [ [/|argmode|/ ] [/|arg_name|/ ]/|arg_type|/ [, ...] ] ) ] [, ...] >> | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA/|schema_name|/ [, ...] } >> TO/|role_specification|/ [, ...] [ WITH GRANT OPTION ] >> [ GRANTED BY/|role_specification|/ ] >> I am using postgres 16, but the grant function part does not change. >> What did I miss? > > Hello, You missed the docs. The "IN SCHEMA" version always goes with ALL > FUNCTIONS in the beginning. What would be the point specifying > pg_catalog.pg_reload_conf() IN SCHEMA pg_catalog, i.e. giving the SCHEMA > twice ? To be a little clearer to use IN SCHEMA the command would be: GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA pg_catalog TO test; Where ALL FUNCTIONS IN SCHEMA is a bulk operation over the functions in the schema per the docs: https://www.postgresql.org/docs/current/sql-grant.html "There is also an option to grant privileges on all objects of the same type within one or more schemas. This functionality is currently supported only for tables, sequences, functions, and procedures. ALL TABLES also affects views and foreign tables, just like the specific-object GRANT command. ALL FUNCTIONS also affects aggregate and window functions, but not procedures, again just like the specific-object GRANT command. Use ALL ROUTINES to include procedures." Since you are looking to GRANT to a specific function you need to schema qualify the function name as IN SCHEMA is not available in that form of the command. > > -- > Achilleas Mantzios > IT DEV - HEAD > IT DEPT > Dynacom Tankers Mgmt > -- Adrian Klaver adrian.klaver@aklaver.com