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.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
