[ redirecting thread from -patches to -hackers for wider comment ]
Jeremy Drake <pgsql@jdrake.com> writes:
> On Wed, 24 Jan 2007, Tom Lane wrote:
>> Note I'm not arguing against allowing it to be "on" by default, I just
>> want to be sure there is a way for paranoid DBAs to turn it off. Maybe
>> it'd be sufficient if the flag bit was there but "UPDATE pg_pltemplate"
>> was the only way to manipulate it --- we've gotten along with treating
>> datistemplate and datallowconn that way.
> That sounds reasonable to me. I'll try to put together a patch like this
> (adding a boolean column to pg_pltemplate) and see if this is acceptable.
> I assume that only superusers can modify pg_pltemplate already ;)
I had a further thought about this: if we allow random users to create
languages, then without any further tweaking the instance of the
language in their DB would be owned by them and they could grant or deny
USAGE on it to others in their DB. This is probably not good. Given
the current structure of pg_language, a language is effectively a
one-time-per-DB resource and so random users could obstruct others from
using a language.
Perhaps it'd make sense to limit this to the DB owner, who would then be
able to grant or deny language usage to the other users in his database.
In detail, it'd look something like:
* For an untrusted language: must be superuser to either create or use
the language (no change from current rules). Ownership of the
pg_language entry is really irrelevant, as is its ACL.
* For a trusted language:
* if pg_pltemplate.something is ON: either a superuser or the current
DB's owner can CREATE the language. In either case the pg_language
entry will be marked as owned by the DB owner (pg_database.datdba),
which means that subsequently he (or a superuser) can grant or deny
USAGE within his DB.
* if pg_pltemplate.something is OFF: must be superuser to CREATE the
language; subsequently it will be owned by you, so only you or another
superuser can grant or deny USAGE (same behavior as currently).
Comments? The bit about assigning the datdba as the owner might seem
a bit odd, but I'm worried about the case where someone has the DBA
privilege as a role but issues the create under his own ID. If it's
owned directly by him, you'd end up in a situation where other holders
of the DBA role couldn't manipulate the language, which seems
undesirable.
regards, tom lane
