Re: Need help extripating plpgsql

On 02/21/2013 03:18 PM, James B. Byrne wrote:
>
> On Thu, February 21, 2013 16:02, Adrian Klaver wrote:
>> On 02/21/2013 12:14 PM, James B. Byrne wrote:
>
>>>
>>> The current arrangement is not really satisfactory as it requires
>>> either separate template databases for each userid granted the
>>> DBCREATE role or the superuser role has to be granted in place of
>>> the DBCREATE role.
>>>
>>>
>>
>> That is sort of the point of the template system, different templates
>> for different situations.
>>
>
> Creating a new database from the system provided standard templates is
> not what I would consider a different situation requiring a
> specialized template for each and every user granted the DBCREATE
> role. Requiring that seems to me to be busywork and a complete waste
> of DBA resources.
>
> If all the elements contained in the standard templates had their
> ownerships changed to that of the owner of the new database then my
> problem would never have arisen. I do not understand why this is not
> the case. Is there a reason why this is so?

My previous not withstanding there is a reason I can see why this not
so. Just because a user does not own an object does not mean they cannot
use it. This allows a DBA to set up a template with a privilege scheme
that suits their needs and then can be replicated. Under your proposal
every time a database was created the privilege scheme would need to be
reestablished. You want the one user model which can be had by doing
everything as a superuser. This is why it is generally recommended to
have various roles defined in your database cluster. One role being
sufficiently privileged to do the superuser work and others for other tasks.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com