Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eyKa1-0000UA-Py for pgsql-admin@arkaria.postgresql.org; Tue, 20 Mar 2018 16:57:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1eyKa0-000872-EV for pgsql-admin@arkaria.postgresql.org; Tue, 20 Mar 2018 16:57:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eyKa0-00086r-1v for pgsql-admin@lists.postgresql.org; Tue, 20 Mar 2018 16:57:44 +0000 Received: from mail-io0-x242.google.com ([2607:f8b0:4001:c06::242]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eyKZs-0002z5-8G for pgsql-admin@lists.postgresql.org; Tue, 20 Mar 2018 16:57:39 +0000 Received: by mail-io0-x242.google.com with SMTP id l12so3186175ioc.10 for ; Tue, 20 Mar 2018 09:57:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=educationalmeasures-com.20150623.gappssmtp.com; s=20150623; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=+SQFKEj8w3jMwrlx+Me3kp0MV4o9Dw7gmAgOoLxsZbc=; b=Kdb6RoxTK57XIsBz1zDNZFqO7O3FmQLsIGG1i3goY4/n04Azbr660jVBDlbOk8AvsT d9u3KihKb7fo8+gQIEbFr3bF5eAWKRaAwra8F7To8yjzpuY9PYG2Inew4mBVEJfCOhsI gKMbKMoNU/2ERZtwLmIPYcC+yXqm+DXgMLehtmYJXKJCnYqJJTiZFvdUuMkWt1TYnEqA p8qKlME5ksftajgWoia+4fNTqtr9pPWpPR73pYOP4H/yNR6RcjwHQAVnWpinU3E7QMiL iVw2QSQwwNPDZ7B/5LRPR0BmwBUVoK4dtto2OioRsuZrOQxbnazN2vuaGbsdrxkfXU3q pQ2Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=+SQFKEj8w3jMwrlx+Me3kp0MV4o9Dw7gmAgOoLxsZbc=; b=BZXG3SPCd9UX8O03xJcGTprmwcBUVmibVmU5JseUO5f8m5+VJGlhHO77/wX6CFfppT waDn6K17sfyCBiPsonFMp1/Ht+E4d07ficoWnZhU18JwK9B8ZSs2KMu8tvM8bj52hSI4 D0k45Tp54PYdvstZjL8iwKCy2yzlLJsAc+MXyqiZHUC9LvhBcXtW8Wo1fdmitVSYSx66 jS/52a1yeoYpAnJrCzxd1heEytCXT4JWe13PbVXAcjImVCrUkoUReXjdE+3MZMZICuF8 GksTj3xhuUFIwbOpoE5Mpq2TRXfriWLqLldvSL9rW9F81/G4SWGLOPkv3oT/VzZ98gh0 Xf4Q== X-Gm-Message-State: AElRT7HdKd3eMNV6f34tAqoauU56GX75Eyf3A5S+b5VBg9CeNT6WpKH3 k0D3yVjlKyNbzvOJLLvXf9wva5uiJvc= X-Google-Smtp-Source: AG47ELsBUoSxQqTvPqu1qCAfOdqSf/6cg6M6nrL6u36WTjLYYsVmiw2PSwP05Up0cLEULaI3Hbt6ig== X-Received: by 10.107.154.78 with SMTP id c75mr17104246ioe.106.1521565052504; Tue, 20 Mar 2018 09:57:32 -0700 (PDT) Received: from EM-LAPTOP143.local (96-81-52-113-static.hfc.comcastbusiness.net. [96.81.52.113]) by smtp.gmail.com with ESMTPSA id r6sm1450190ioe.85.2018.03.20.09.57.31 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 20 Mar 2018 09:57:31 -0700 (PDT) Subject: Re: Newbie question To: Ron Watkins , pgsql-admin@lists.postgresql.org References: <2a9a01d3c06b$8bb5d6a0$a32183e0$@gmail.com> From: Andrew Foster Message-ID: Date: Tue, 20 Mar 2018 10:57:30 -0600 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:52.0) Gecko/20100101 Thunderbird/52.6.0 MIME-Version: 1.0 In-Reply-To: <2a9a01d3c06b$8bb5d6a0$a32183e0$@gmail.com> Content-Type: multipart/alternative; boundary="------------CA718448B9805F7A0926593E" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------CA718448B9805F7A0926593E Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit Hi Ron, If you GRANT CREATE and USAGE on schema FOO for the developer's user, your developer should be able to create new objects (in this case, functions) in schema FOO. My suggestion for a best approach would be to consider what your default privileges should be (https://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html) as well as setting up groups so that you don't have to modify permissions for each individual. Hope this helps! Andrew Foster | Educational Measures, LLC Sr. Database Administrator On 3/20/18 10:50 AM, Ron Watkins wrote: > > I am pretty new to Postgresql. > > I have a database with a schema, ill call it ‘FOO’ here in the message. > > I also have a developer, who want’s to create a function for use in > the ‘FOO’ schema. > > Im not finding the appropriate command to allow that user to create a > function in the FOO schema (or elsewhere). > > Is there such a command? I was looking under the ‘GRANT’ but I don’t > see a way to grant “CREATE or REPLACE FUNCTION”. > > Am I missing anything here? What is the best approach? > --------------CA718448B9805F7A0926593E Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: 8bit

Hi Ron,

If you GRANT CREATE and USAGE on schema FOO for the developer's user, your developer should be able to create new objects (in this case, functions) in schema FOO.

My suggestion for a best approach would be to consider what your default privileges should be (https://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html) as well as setting up groups so that you don't have to modify permissions for each individual.


Hope this helps!

Andrew Foster | Educational Measures, LLC
Sr. Database Administrator
On 3/20/18 10:50 AM, Ron Watkins wrote:

I am pretty new to Postgresql.

I have a database with a schema, ill call it ‘FOO’ here in the message.

I also have a developer, who want’s to create a function for use in the ‘FOO’ schema.

Im not finding the appropriate command to allow that user to create a function in the FOO schema (or elsewhere).

Is there such a command? I was looking under the ‘GRANT’ but I don’t see a way to grant “CREATE or REPLACE FUNCTION”.

Am I missing anything here? What is the best approach?

 


--------------CA718448B9805F7A0926593E--