Re: Including PL/PgSQL by default

>>> On Fri, Feb 22, 2008 at 10:09 AM, in message <47BEF3A1(dot)2010502(at)dunslane(dot)net>,
Andrew Dunstan <andrew(at)dunslane(dot)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