Re: Upgrades and Error Messages - Mailing list pgsql-hackers
| From | David G. Johnston |
|---|---|
| Subject | Re: Upgrades and Error Messages |
| Date | |
| Msg-id | CAKFQuwZyc1Li0u=uAc2TzhcfFp7d+wkRm==vw2ncOdi_G_-8Aw@mail.gmail.com Whole thread Raw |
| In response to | Upgrades and Error Messages (Thangalin <thangalin@gmail.com>) |
| List | pgsql-hackers |
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.
pgsql-hackers by date: