Обсуждение: Upgrades and Error Messages

Hi,

~$ cat upgrade.sh
#!/bin/bash

/usr/lib/postgresql/9.3/bin/pg_upgrade \ -b /usr/lib/postgresql/9.1/bin/  \ -B /usr/lib/postgresql/9.3/bin/  \ -d
/var/lib/postgresql/9.1/main \ -D /var/lib/postgresql/9.3/main  \ -o ' -c
config_file=/etc/postgresql/9.1/main/postgresql.conf'\ -O ' -c config_file=/etc/postgresql/9.3/main/postgresql.conf'
 

$ ./upgrade.sh
...
Adding support functions to new cluster                     ok
Restoring database schemas in the new cluster postgres
*failure*

Consult the last few lines...

$ tail pg_upgrade_dump_11955.log
pg_restore: setting owner and privileges for ACL plperlu
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1955; 0 0 ACL plperlu postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  language
"plperlu" is not trusted
HINT:  Only superusers can use untrusted languages.   Command was: REVOKE ALL ON LANGUAGE "plperlu" FROM PUBLIC;
REVOKE ALL ON LANGUAGE "plperlu" FROM "postgres";
GRANT ALL ON LANGUAGE "plper...

---

At this point, I restarted the process. Then, in both databases (9.1 and 9.3):

CREATE OR REPLACE TRUSTED LANGUAGE plperlu;
UPDATE pg_language set lanpltrusted = true where lanname='plperlu';
REVOKE ALL ON LANGUAGE "plperlu" FROM PUBLIC;

These worked as expected.

When I re-ran the upgrade script, the same error appeared.

--

Note: It would be nice if any databases and users that were created
during the upgrade process were deleted on error. (I keep forgetting
to delete them from the 9.3 database, which results in another error.)

--

Speaking of errors, most of the errors returned from PostgreSQL are
technically accurate but provide the user with no clue as to how to
diagnose the problem. Here's a simple error message as an example:

pg_upgrade: cannot be run as root

Instead of stating what has gone wrong, state how to correct it. For
example, the pg_upgrade can easily determine the owner of the file and
can likely determine the user needed to run the program. This
information could be provided:

pg_upgrade: run as postgres (cannot be run as root)

Or even:

pg_upgrade: cannot be run as root (must be run as database owner)

Or:

pg_upgrade: ERROR 15363: Cannot be run as root
pg_upgrade: See https://postgresql.org/errors/15363/

These types of error messages exist throughout the entire code base
and make PostgreSQL a little frustrating to work with when anything
goes awry. Here's another example:

pg_restore: [archiver (db)] could not execute query: ERROR:  language
"plperlu" is not trusted

What do I need to do to make it trusted? Telling me "Only superusers
can use untrusted languages" isn't sufficient. Maybe point to a WIKI
that describes the problem in detail and offers ways to diagnose the
issue a bit deeper? For my purposes, as far as I can tell, the
languages *are* trusted in both databases, but I have no idea where to
go from here.

Meanwhile, if anyone knows how to solve/diagnose this issue, I'd
appreciate the help.

T X

Thangalin <thangalin@gmail.com> writes:
> $ tail pg_upgrade_dump_11955.log
> pg_restore: setting owner and privileges for ACL plperlu
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 1955; 0 0 ACL plperlu postgres
> pg_restore: [archiver (db)] could not execute query: ERROR:  language
> "plperlu" is not trusted
> HINT:  Only superusers can use untrusted languages.
>     Command was: REVOKE ALL ON LANGUAGE "plperlu" FROM PUBLIC;
> REVOKE ALL ON LANGUAGE "plperlu" FROM "postgres";
> GRANT ALL ON LANGUAGE "plper...

Hmm, that's odd.  Maybe you have the language marked as owned by a
non-superuser in the original database?  Or had you done this:

> CREATE OR REPLACE TRUSTED LANGUAGE plperlu;
> UPDATE pg_language set lanpltrusted = true where lanname='plperlu';

in the original database beforehand?

If the latter, I'm just going to shrug and say that that's unsupported.
Pretending that a language is trusted when it is not is a recipe for
security holes and worse.  By and large, *any* direct update on a system
catalog is a situation where we're going to say that any bad consequences
are your own fault.

(I think this theory does explain the failure, FWIW.  pg_dump would have
seen the language marked trusted in the source database, so it would have
dumped suitable GRANT/REVOKE commands for it.  But in the new database,
CREATE LANGUAGE would have forced the language to untrusted since it knows
that's what it should be by consulting pg_pltemplate.  Then you'd have
gotten this error because GRANT/REVOKE are disallowed on untrusted
languages.)


> Speaking of errors, most of the errors returned from PostgreSQL are
> technically accurate but provide the user with no clue as to how to
> diagnose the problem.

We do what we can.  In many cases, it's really impossible for the
code author to anticipate every cause of a possible failure, and even
if he could, listing them all would render error messages unusable.
Surely we could not, for example, mention the possibility of "you made
an ill-advised manual change to the system catalogs" every single place
where that could be a contributing factor.

In the particular case at hand, I think the HINT is a bit off base
--- it'd be more useful to say something like "GRANT and REVOKE are
not allowed on untrusted languages".  But it's really hard to see
how the error message could drill down to the ultimate cause.

> pg_upgrade: ERROR 15363: Cannot be run as root
> pg_upgrade: See https://postgresql.org/errors/15363/

This has been proposed before, but I haven't seen any army of
well-informed volunteers ready to populate such a website with
complete and reliable information.  A quick count says there
are order-of-10000 distinct errors in our current sources, so
you would need an army if you wanted it done any time soon.
        regards, tom lane