Re: Upgrades and Error Messages

On Sat, Jun 18, 2016 at 4:52 PM, Thangalin <thangalin(at)gmail(dot)com> wrote:

​tl/dr; Observations, thoughts, and questions present inline as well.​

​Note, I'm drawing a conclusion below but do not have the code knowledge to
know for certain that what I describe doesn't have holes in it. I'm also
assuming a bit about what you've done since you haven't provided a
self-contained example:

In short there must be some kind of test during REVOKE (and likely GRANT)
that prevents its operation on untrusted languages. pg_dump knows about
this so when it encounters an untrusted language it doesn't try and play
with permissions But you've circumvented that by partially marking an
untrusted language as trusted and then revoked the USAGE privilege that
PUBLIC users get by default. pg_dump sees this and does what you requested
- places a REVOKE into the dump. But the restoration creates the plperlu
language in its original form - untrusted - and the attempt to perform the
REVOKE then fails as it is acting upon the untrusted language.

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...
>
>
Did the log file really get chopped off after "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';
> ​+
>

At this point you've started playing with fire.
​

> ​
>
> REVOKE ALL ON LANGUAGE "plperlu" FROM PUBLIC;
>
> These worked as expected.

> When I re-ran the upgrade script, the same error appeared.
>
>
​I suspect that you are doing something that PostgreSQL should be
preventing earlier on but fails to. Instead you get failures later on and
removed from the original undefined behavior.​ Or, rather, from your
actions above, you are doing something that is not supported.

I'm not sure why you did anything to the 9.3 database since you are just
going to restore the 9.1 database into it.

--
>
> 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.)
>
>
Really? I'd rather be allowed to inspect the failed upgrade as part of
diagnostics. If you are failing enough times that this matters add a
"clean" option to your upgrade.sh wrapper script.​

--
>
> 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:
>

​I don't see this changing any time soon - not with any kind of bulk effort
at least. Patches are welcome though project style variations are going to
be problematic if the errors being fixed are cherry-picked.

> pg_restore: [archiver (db)] could not execute query: ERROR: language
> "plperlu" is not trusted
>
> What do I need to do to make it trusted?

You cannot. It is an inherent property of the language. I supposed you
can update catalogs (like you show) but I wouldn't be surprised if that
simply introduced more problems.​ By the way, if you read the
documentation regarding languages more closely you will likely find another
aspect of this you seem to have overlooked.

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?

​In reality it takes complaints like this to motivate someone to work on
improving documentation (and possibly code) in the manner you describe.
And, frankly, the scarcity and general uniqueness of many of these
questions ends up making the public mailing list the last place some of
these things are presented. If/When you get your issues resolved you can
give back to the community for their helping you understand by requesting
Wiki edit permissions and writing up a document for the next person. That
general flow is about the only realistic way the situation you describe is
apt to improve. Very few people are going to want to volunteer time to
perform a massive improvement that you seem to envision.

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.
>

Just to be clear, you are running the upgrade as the <postgres> OS and DB
user, right?

David J.
​
​