Horton

Re: MAINTAIN privilege -- what do we need to un-revert it?

Jeff Davis <pgsql@j-davis.com>

View thread View on postgr.es
From: Jeff Davis <pgsql@j-davis.com>
To: Noah Misch <noah@leadboat.com>
Cc: Michael Paquier <michael@paquier.xyz>, Nathan Bossart <nathandbossart@gmail.com>, pgsql-hackers@postgresql.org, Joe Conway <mail@joeconway.com>, Robert Haas <robertmhaas@gmail.com>
Date: 2024-07-12T21:50:52Z
Lists: pgsql-hackers

Attachments

On Thu, 2024-07-11 at 05:52 -0700, Noah Misch wrote:
> > I could try to refactor it into two statements and execute them
> > separately, or I could try to rewrite the statement to use a fully-
> > qualified destination table before execution. Thoughts?
> 
> Those sound fine.  Also fine: just adding a comment on why creation
> namespace
> considerations led to not doing it there.

Attached. 0002 separates the CREATE MATERIALIZED VIEW ... WITH DATA
into (effectively):

   CREATE MATERIALIZED VIEW ... WITH NO DATA;
   REFRESH MATERIALIZED VIEW ...;

Using refresh also achieves the stated goal more directly: to (mostly)
ensure that a subsequent REFRESH will succeed.

Note: the creation itself no longer executes in a security-restricted
context, but I don't think that's a problem. The only reason it's using
the security restricted context is so the following REFRESH will
succeed, right?

Regards,
	Jeff Davis

Commits

  1. doc: Note that CREATE MATERIALIZED VIEW restricts search_path.

  2. Small refactoring around ExecCreateTableAs().

  3. Add is_create parameter to RefreshMatviewByOid().

  4. Remove unused ParamListInfo argument from ExecRefreshMatView.

  5. When creating materialized views, use REFRESH to load data.

  6. Add missing RestrictSearchPath() calls.

  7. Fix search_path to a safe value during maintenance operations.

  8. Revert MAINTAIN privilege and pg_maintain predefined role.

  9. Avoid repeated name lookups during table and index DDL.

  10. Add a materialized view relations.