Обсуждение: Failure loading materialized view with pg_restore

Hi,

If I run this set of commands against PostgreSQL 9.4.1 I pg_restore
throws an error with a permission problem. Why it does so is a mystery
to me, given that the user performing the restore is a superuser:

    # superuser creates database and materialized view
    createuser -s super
    createdb --username super orig
    psql --username super -c "select 'USING:' || version();" orig
    psql --username super -c 'CREATE TABLE x (y int);' orig
    psql --username super -c 'CREATE MATERIALIZED VIEW myview AS select * from x' orig

    # change the owner of the view to myview
    createuser -S nobody
    psql --username super -c 'ALTER TABLE myview OWNER TO "nobody";' orig

    # dump and reload
    pg_dump --username super --format c -f dump.dump orig
    createdb copied

    # pg_restore errors
    pg_restore --username super -d copied dump.dump

The error I get is:

    pg_restore: [archiver (db)] Error while PROCESSING TOC:
    pg_restore: [archiver (db)] Error from TOC entry 2260; 0 16569 MATERIALIZED VIEW DATA myview nobody
    pg_restore: [archiver (db)] could not execute query: ERROR:  permission denied for relation x
        Command was: REFRESH MATERIALIZED VIEW myview;

In pg_hba I am using the "trust" method for everything (this is a test
cluster).

Is this expected behaviour or a bug?

--
Brian Sutherland

Brian Sutherland <brian@vanguardistas.net> writes:
> If I run this set of commands against PostgreSQL 9.4.1 I pg_restore
> throws an error with a permission problem. Why it does so is a mystery
> to me, given that the user performing the restore is a superuser:

The same thing would happen without any dump and restore:

regression=# create user nobody;
CREATE ROLE
regression=# CREATE TABLE x (y int);
CREATE TABLE
regression=# CREATE MATERIALIZED VIEW myview AS select * from x;
SELECT 0
regression=# ALTER TABLE myview OWNER TO "nobody";
ALTER TABLE
regression=# REFRESH MATERIALIZED VIEW myview;
ERROR:  permission denied for relation x

User "nobody" does not have permission to read table x, so the REFRESH
fails, because the view's query executes as the view's owner.

            regards, tom lane

On Wed, Feb 18, 2015 at 10:34:33AM -0500, Tom Lane wrote:
> Brian Sutherland <brian@vanguardistas.net> writes:
> > If I run this set of commands against PostgreSQL 9.4.1 I pg_restore
> > throws an error with a permission problem. Why it does so is a mystery
> > to me, given that the user performing the restore is a superuser:
>
> The same thing would happen without any dump and restore:
>
> regression=# create user nobody;
> CREATE ROLE
> regression=# CREATE TABLE x (y int);
> CREATE TABLE
> regression=# CREATE MATERIALIZED VIEW myview AS select * from x;
> SELECT 0
> regression=# ALTER TABLE myview OWNER TO "nobody";
> ALTER TABLE
> regression=# REFRESH MATERIALIZED VIEW myview;
> ERROR:  permission denied for relation x
>
> User "nobody" does not have permission to read table x, so the REFRESH
> fails, because the view's query executes as the view's owner.

If you grant select permission for the user nobody on x, pg_restore
still fails even though a REFRESH succeeds:

    # superuser creates database and materialized view
    createuser -s super

    createdb --username super orig
    psql --username super -c "select 'USING:' || version();" orig
    psql --username super -c 'CREATE TABLE x (y int);' orig
    psql --username super -c 'CREATE MATERIALIZED VIEW myview AS select * from x' orig

    # change the owner of the view to myview and grant SELECT to nobody
    createuser -S nobody
    psql --username super -c 'GRANT SELECT ON x TO nobody' orig
    psql --username super -c 'ALTER TABLE myview OWNER TO "nobody";' orig

    # refresh does work if you are nobody
    psql --username nobody -c 'REFRESH MATERIALIZED VIEW myview;' orig

    # dump and reload
    pg_dump --username super --format c -f dump.dump orig
    createdb copied

    # pg_restore errors
    pg_restore --username super -d copied dump.dump

I guess I provided a too-minimal example...

--
Brian Sutherland

Brian Sutherland <brian@vanguardistas.net> writes:
> On Wed, Feb 18, 2015 at 10:34:33AM -0500, Tom Lane wrote:
>> User "nobody" does not have permission to read table x, so the REFRESH
>> fails, because the view's query executes as the view's owner.

> If you grant select permission for the user nobody on x, pg_restore
> still fails even though a REFRESH succeeds:

Oooh.  Yeah: the problem is that pg_dump dumps the REFRESH before it dumps
the ACLs for the tables:

--
-- Name: x; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--

CREATE TABLE x (
    y integer
);


ALTER TABLE x OWNER TO postgres;

--
-- Name: myview; Type: MATERIALIZED VIEW; Schema: public; Owner: nobody; Tablespace:
--

CREATE MATERIALIZED VIEW myview AS
 SELECT x.y
   FROM x
  WITH NO DATA;


ALTER TABLE myview OWNER TO nobody;

--
-- Data for Name: x; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY x (y) FROM stdin;
\.


--
-- Name: myview; Type: MATERIALIZED VIEW DATA; Schema: public; Owner: nobody
--

REFRESH MATERIALIZED VIEW myview;


--
-- Name: x; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE x FROM PUBLIC;
REVOKE ALL ON TABLE x FROM postgres;
GRANT ALL ON TABLE x TO postgres;
GRANT SELECT ON TABLE x TO nobody;


We need to rethink the ordering rules here.  I believe that not dumping
ACLs till late is an intentional choice to avoid corner cases with regular
tables (eg what if user has revoked INSERT on a table), but it doesn't
work so well for matviews.

One possible avenue to a fix is to also postpone the assignment of the
matview's owner, but I'm not sure that that's a great idea from a security
standpoint.

A possibly safer idea is just to put all REFRESHes after all ACL updates.
If things fail then, well, they'd have failed anyway.

            regards, tom lane