Re: Including PL/PgSQL by default

>>> On Fri, Feb 22, 2008 at 10:09 AM, in message <47BEF3A1.2010502@dunslane.net>,
Andrew Dunstan <andrew@dunslane.net> wrote:

> Roberts, Jon wrote:
>> However, you can not create anything in Oracle without being given
>> permission to create it.  The notion that you can create a function
>> because you have connect rights to the database is foreign to me.
>> Connect should mean connect, not connect AND create.
>>
>> Include the language by default and remove CREATE on the public schema.
>
> You'd need more than that.
>
> For example, since we don't support temp functions, we should probably
> ban the creation of functions in temp schemas (which I found was possible).
I also found the default rights within a database surprising.
It is now our standard practice to adjust the rights in a database
along these lines when it is created:
postgres=# create database dtr with owner dtrowner;
CREATE DATABASE
postgres=# \c dtr
You are now connected to database "dtr".
dtr=# revoke create on database dtr from public;
REVOKE
dtr=# revoke create on schema public from public;
REVOKE
dtr=# grant create on schema public to dtrowner;
GRANT
The goal is to restrict creation of non-temporary objects to the
database owner.  If you know of some way to create any object as a
user other than the database owner or a superuser in a database set
up this way, please elaborate.  If I need to follow up on it, I'll
take it over to the ADMIN list.
-Kevin