Re: pg_upgrade check for invalid role-specific default config

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Charlie Hornsby <charlie(dot)hornsby(at)hotmail(dot)co(dot)uk>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_upgrade check for invalid role-specific default config
Date: 2021-04-12 21:46:35
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

I wrote:
> I'm not sure I buy the premise that "it is possible to write a query
> to identify these cases". It seems to me that the general problem is
> that ALTER ROLE/DATABASE SET values might have become incorrect since
> they were installed and would thus fail when reloaded in dump/restore.
> We're not going to be able to prevent that in the general case, and
> it's not obvious to me what special case might be worth going after.

Actually, after thinking about that a bit more, this is a whole lot
like the issues we have with reloading function bodies and function
SET clauses. The solution we've adopted for that is to allow dumps
to turn off validation via the check_function_bodies GUC. Maybe there
should be a GUC to disable validation of ALTER ROLE/DATABASE SET values.
If you fat-finger a setting, you might not be able to log in, but you
couldn't log in in the old database either.

Another answer is that maybe the processing of the "role" case
in particular is just broken. Compare the behavior here:

regression=# create role joe;
regression=# alter role joe set role = 'notthere';
ERROR: role "notthere" does not exist
regression=# alter role joe set default_text_search_config to 'notthere';
NOTICE: text search configuration "notthere" does not exist
# \drds
List of settings
Role | Database | Settings
joe | | default_text_search_config=notthere

despite the fact that a direct SET fails:

regression=# set default_text_search_config to 'notthere';
ERROR: invalid value for parameter "default_text_search_config": "notthere"

It's intentional that we don't throw a hard error for
default_text_search_config, because that would create
a problematic ordering dependency for pg_dump: the
desired config might just not have been reloaded yet.
Maybe the right answer here is that the processing of
"set role" in particular failed to get that memo.

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Munro 2021-04-12 22:08:07 Re: Curious test case added by collation version tracking patch
Previous Message Andres Freund 2021-04-12 21:35:27 Re: Allowing to create LEAKPROOF functions to non-superuser