Re: Need help extripating plpgsql

On 02/22/2013 07:25 AM, James B. Byrne wrote:
>
> On Thu, February 21, 2013 20:27, Adrian Klaver wrote:
>
>>
>> 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.
>>
>
> It seems strange to me that a trusted extension, one that can be added
> by any database owner, is prevented from being treated as trusted in
> the default configuration. I have no opinion on whether or not
> plpgsql should be included by default in newly created databases but,
> I do object that it is included in such a way as to make its
> management by the subsequent database owner impossible.

It would seem there is an interaction between the extension mechanism
and CREATE LANGUAGE that is not entirely clear to me. Looking at the
extension packaging for plpgsql shows that all it does is call CREATE
LANGUAGE and add the COMMENT. The control files has superuser = false
which as I understand it means the EXTENSION can be created by
non-superusers subject to privilege restrictions on the CREATE LANGUAGE
and COMMENT command. In the CREATE LANGUAGE docs there is this:
"The default is that trusted languages can be created by database
owners, but this can be adjusted by superusers by modifying the contents
of pg_pltemplate."

By default in pg_pltemplate plpgsql has tmpldbacreate = 't' which would
seem to mean it can be created by non-superusers. The issue from what I
am seeing is that when the cluster is created the template databases
have plpgsql created in them by the superuser(postgres) and that
ownership cannot really be transferred.

>
> Lacking the expertise myself might I impose upon you to suggest what
> configuration of roles would permit the plpgsql extension to be owned
> by the database owner when added from a template? I am quite willing
> to use a template2 of my own devising to create new databases but I
> would rather not have to create a template for every user that might
> be granted the DBCREATE privilege. This an issue because each project
> requires at least two separate userids that require the DBCREATE role
> and both are used to automatically drop and create test and
> development databases as part of the testing arrangements specific to
> their project.

At this point I am not sure how to do this with out creating role that
has superuser privileges.

>

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