Re: [pgsql-patches] unprivileged contrib and pl install

On Wed, 24 Jan 2007, Tom Lane wrote:

> [ redirecting thread from -patches to -hackers for wider comment ]
>
> Jeremy Drake <pgsql(at)jdrake(dot)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.
>

I am digging through the code looking at this, and I have a question. As
far as I can tell, there is currently no owner for a pg_language entry.
Is this correct or is ownership information stored somewhere other than
the pg_language relation? Are you suggesting that a lanowner column would
need to be added?

As far as the column name referred to below as "pg_pltemplate.something",
for now I am calling it tmpldbaallowed. I am not particularly attached to
nor fond of that name, however, and am open to naming suggestions.

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
>

--
Save the Whales -- Harpoon a Honda.