Recently I submitted a patch for review that allows a non-superuser to practically use a backup taken from pg_dump.
Currently it is a kludge (and well known at that - Ref 1 / Ref 2 / Ref 3 / Ref 4) but since it's (the ideal solution) too big a byte to chew and not in high-demand, it has seen little to no traction in the past decade.
This patch should allow the above. But more importantly, it should also allow regular users of AWS RDS Postgres as well as Google Cloud Postgres databases (who do not get SuperUser access by-design) to reliably use the backups, instead of tinkering with the backup SQLs and remove things like COMMENT ON EXTENSION for it to even run during restoration.
The bad news is that since Postgres 10 has already branched off, I doubt this would see the light of the day (if at all) any time before Postgres 11 (unless there is consensus that it's helpful enough and gets 'back-patched' to Postgres 10 to be released around September 2017).
Update (3rd Oct 2017):
This is now a part of my PsqlForks branch. You can check the related commit here.
Update (26th Jan 2018):
This is now part of the official Postgres v11 branch. You can check the related commit here.
How to get Database identifier using SQL Often there is a need for client programs to find "where am I logged into?". This blog po...
My patch (allowing a non-super-user to dump Globals) just got committed to Postgres 10.0. Besides the use mentioned above, this patch al...
(Please scroll down to the read the entire-post to know more) Documents required for Christian Marriage Registration (a.k.a. Documents...
At times we need to do a DROP USER and are unable to do so, because of existing "DEFAULT PRIVILEGES" that exist associated to the ...