restore error - language "plperlu" is not trusted

On 17 Dec 2003, Christopher Murtagh wrote:

Greetings,

I just had to dump and restore one of my DBs (7.4RC2), and I got an
interesting message.

I first did:

pg_dump dbname > db_restore.sql

Then at console did the following:

\i db_restpre.sql

which performed everything as expected with the following ERROR
message:

ERROR: language "plperlu" is not trusted

and it gave a line number, which contained the following:

GRANT ALL ON LANGUAGE plperlu TO postgres WITH GRANT OPTION;

Now, my plperlu functions seem to behaving as expected (they read from
and write to /tmp). Should I be worried?

Did you install plperlu ahead of time as the super user, then run your
restore as the regular database owner?

On Wed, 2003-12-17 at 10:55, scott.marlowe wrote:

which performed everything as expected with the following ERROR
message:

ERROR: language "plperlu" is not trusted

and it gave a line number, which contained the following:

GRANT ALL ON LANGUAGE plperlu TO postgres WITH GRANT OPTION;

Now, my plperlu functions seem to behaving as expected (they read from
and write to /tmp). Should I be worried?

Did you install plperlu ahead of time as the super user, then run your
restore as the regular database owner?

No, I hadn't, I guess I had assumed that the restore would do that.
More interestingly, I did the restore on a different
machine/architecture (PPC instead of Intel) running 7.4 final release
(instead of RC2) and I didn't get the error message. However all of my
plperlu triggers seem to behaving normally. When I tried to install
plperlu afterwards I got this:

createlang plperlu a
createlang: language "plperlu" is already installed in database "a"

Hrm... Ah well. I'll know for next time to install the language first,
I just hope it won't bit me in the ass before then. :-)

Cheers,

Chris

--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Communications Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax: (514) 398-2017

Christopher Murtagh <christopher.murtagh@mcgill.ca> writes:

[ pg_dump script failed with ]
ERROR: language "plperlu" is not trusted
and it gave a line number, which contained the following:
GRANT ALL ON LANGUAGE plperlu TO postgres WITH GRANT OPTION;

Hm. The backend flat-out rejects all attempts at GRANT on untrusted
languages, even if you are superuser and the grantee is too. I'm not
totally sure about the rationale for that (Peter?) but in any case
pg_dump has evidently not gotten the word. If we think the backend's
behavior is right then we'd better change pg_dump to suppress trying
to GRANT permissions on untrusted languages.

regards, tom lane

Tom Lane wrote:

Hm. The backend flat-out rejects all attempts at GRANT on untrusted
languages, even if you are superuser and the grantee is too. I'm not
totally sure about the rationale for that (Peter?)

Why would you need it? It's only going to create fuss about useless
functionality.

but in any case
pg_dump has evidently not gotten the word. If we think the backend's
behavior is right then we'd better change pg_dump to suppress trying
to GRANT permissions on untrusted languages.

There should not be any permissions, so there should be nothing to dump.

Peter Eisentraut <peter_e@gmx.net> writes:

Tom Lane wrote:

pg_dump has evidently not gotten the word. If we think the backend's
behavior is right then we'd better change pg_dump to suppress trying
to GRANT permissions on untrusted languages.

There should not be any permissions, so there should be nothing to dump.

Uh, no, because you can say something like
revoke all on language plperlu from public;
and end up with non-null lanacl (because it instantiates the default
assumption that the owner has all privileges).

We could possibly hack the backend to avoid that, but I think pg_dump
will need the special-case test anyway since it has to be able to cope
with existing databases, wherein lanacl may be non-null.

regards, tom lane

Tom Lane wrote:

Uh, no, because you can say something like
revoke all on language plperlu from public;
and end up with non-null lanacl (because it instantiates the default
assumption that the owner has all privileges).

OK, that needs to be disallowed.

We could possibly hack the backend to avoid that, but I think pg_dump
will need the special-case test anyway since it has to be able to
cope with existing databases, wherein lanacl may be non-null.

So far we know of 1 such database. I'd like to see some more before we
bother about it.

Peter Eisentraut <peter_e@gmx.net> writes:

Tom Lane wrote:

Uh, no, because you can say something like
revoke all on language plperlu from public;
and end up with non-null lanacl (because it instantiates the default
assumption that the owner has all privileges).

OK, that needs to be disallowed.

Fair enough. I thought it was a bit odd to disallow GRANT but allow
REVOKE anyway.

We could possibly hack the backend to avoid that, but I think pg_dump
will need the special-case test anyway since it has to be able to
cope with existing databases, wherein lanacl may be non-null.

So far we know of 1 such database. I'd like to see some more before we
bother about it.

It's a one-line addition --- just put the dumpACL call inside
"if (lanpltrusted)". I think it is a reasonable change. We'd have to
do something anyway, because the existing pg_dump code is certainly
broken for dumping untrusted languages from pre-7.3 databases (it
assumes a nonempty lanacl setting in that case).

regards, tom lane

Attached is my proposed patch for this problem, to be put in 7.4.1.
Please someone give it a quick check.

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

Attached is my proposed patch for this problem, to be put in 7.4.1.
Please someone give it a quick check.

The aclchk change looks okay if that's the behavior you want, but I
wonder why you don't just make it raise error in both the GRANT and
REVOKE cases.

The pg_dump change looks okay for the 7.4 branch. It will not apply to
HEAD but I think you can just add

if (plang->lanpltrusted)

before the dumpACL call in that case.

regards, tom lane

Am Donnerstag, 18. Dezember 2003 21:45 schrieb Tom Lane:

The aclchk change looks okay if that's the behavior you want, but I
wonder why you don't just make it raise error in both the GRANT and
REVOKE cases.

Yes, that seems better.

The pg_dump change looks okay for the 7.4 branch. It will not apply to
HEAD but I think you can just add

if (plang->lanpltrusted)

before the dumpACL call in that case.

OK, both branches patched. As far as I'm concerned, we can release 7.4.1.