pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: doc sql-grant.html Synopsis error?
Date: Sat, 8 Apr 2023 08:18:24 -0700
Message-ID: <9c8846fe-af91-a2ee-b565-9d55856b6081@aklaver.com> (raw)
In-Reply-To: <00828709-45d1-f82d-e270-7c9d4d52373e@cloud.gatewaynet.com>
References: <CACJufxFjD1=HzPUYC=03G-iUPtj+_NYHD+qpSfPrjmPz3hyLPQ@mail.gmail.com>
	<00828709-45d1-f82d-e270-7c9d4d52373e@cloud.gatewaynet.com>

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






view thread (3+ messages)

Message-ID: <9c8846fe-af91-a2ee-b565-9d55856b6081@aklaver.com>
Permalink:  ../9c8846fe-af91-a2ee-b565-9d55856b6081@aklaver.com/
Also on:    postgresql.org/message-id/9c8846fe-af91-a2ee-b565-9d55856b6081@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, a.mantzios@cloud.gatewaynet.com
  Subject: Re: doc sql-grant.html Synopsis error?
  In-Reply-To: <9c8846fe-af91-a2ee-b565-9d55856b6081@aklaver.com>
  References: <CACJufxFjD1=HzPUYC=03G-iUPtj+_NYHD+qpSfPrjmPz3hyLPQ@mail.gmail.com> <00828709-45d1-f82d-e270-7c9d4d52373e@cloud.gatewaynet.com> <9c8846fe-af91-a2ee-b565-9d55856b6081@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox