missing public on schema public

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Bo
Reply | Threaded
Open this post in threaded view
|

missing public on schema public

Bo

Hi

 

We recently upgraded from 9.1 to 9.6 (now 9.6.6) and have, after dump/restore on 9.6, experienced the loss of public priviliges on schema public.

 

Is this a “feature” or some kind of bug ?

 

I have found a thread here that looks sortof similar with subject: “[GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public”

But it is from march 2017 and it looks like it ends with a fix being pushed..

 

Version() is

"PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit"

Binary from EDB

 

Thank you for your time.

 

Kind regards

 

Bo Thorbjørn Jensen

Udvikler hos budget123

Bo
Reply | Threaded
Open this post in threaded view
|

Re: missing public on schema public

Bo

I have some additional info and a fix.

 

Firstly steps to reproduce:

 

1.  create database:

CREATE DATABASE test WITH ENCODING='UTF8' OWNER=postgres CONNECTION LIMIT=-1;

-- here public has access to public

 

2. dump:

pg_dump -f testfile.dump -F c -h localhost -U postgres test

 

3. restore:

pg_restore -c -d testfile.dump -h localhost -U postgres test

-- here public no longer has access to schema public

 

It is easily fixable with:

GRANT ALL ON SCHEMA public TO public;

 

And the issue goes away.. (privilege stays after next dump/restore)

 

So. What am I missing?

Is this intentional functionality ?

 

Kind regards and again thank you for your time

 

Bo Thorbjørn Jensen

Reply | Threaded
Open this post in threaded view
|

Re: missing public on schema public

Tom Lane-2
=?iso-8859-1?Q?Bo_Thorbj=F8rn_Jensen?= <[hidden email]> writes:
> I have some additional info and a fix.
> Firstly steps to reproduce:

Yeah, I can reproduce this.  I suspect it got broken by Stephen's hacking
around with default ACLs.  A simple example is

$ pg_dump -c -U postgres postgres | grep -i public
DROP SCHEMA public;
-- Name: public; Type: SCHEMA; Schema: -; Owner: postgres
CREATE SCHEMA public;
ALTER SCHEMA public OWNER TO postgres;
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
COMMENT ON SCHEMA public IS 'standard public schema';
-- Name: public; Type: ACL; Schema: -; Owner: postgres
GRANT ALL ON SCHEMA public TO PUBLIC;

That's fine, but if I shove it through an archive file:

$ pg_dump -f p.dump -Fc -U postgres postgres

$ pg_restore -c p.dump | grep -i public
DROP SCHEMA public;
-- Name: public; Type: SCHEMA; Schema: -; Owner: postgres
CREATE SCHEMA public;
ALTER SCHEMA public OWNER TO postgres;
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
COMMENT ON SCHEMA public IS 'standard public schema';

This is *REALLY BAD*.  Quite aside from the restore being wrong,
those two sequences should never ever give different results.
Stephen, you put some filtering logic in the wrong place in pg_dump.

                        regards, tom lane


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|

Re: missing public on schema public

Stephen Frost
Tom, all,

* Tom Lane ([hidden email]) wrote:
> =?iso-8859-1?Q?Bo_Thorbj=F8rn_Jensen?= <[hidden email]> writes:
> > I have some additional info and a fix.
> > Firstly steps to reproduce:
>
> Yeah, I can reproduce this.  I suspect it got broken by Stephen's hacking
> around with default ACLs.  A simple example is

Yes, it's related to the work I did with pg_dump's ACL handling, because
we're no longer just always including the whole revoke/grant set of ACLs
for everything in the output.

> $ pg_dump -c -U postgres postgres | grep -i public
> DROP SCHEMA public;
> -- Name: public; Type: SCHEMA; Schema: -; Owner: postgres
> CREATE SCHEMA public;
> ALTER SCHEMA public OWNER TO postgres;
> -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
> COMMENT ON SCHEMA public IS 'standard public schema';
> -- Name: public; Type: ACL; Schema: -; Owner: postgres
> GRANT ALL ON SCHEMA public TO PUBLIC;
>
> That's fine, but if I shove it through an archive file:
This works because I added into pg_dump.c a check based on if the output
is clean (and therefore the public schema is being recreated or not).

In hindsight, that wasn't really the right thing to do because it ends
up only working when pg_dump is run with -c and doesn't consider the
case where pg_dump is run without -c but pg_restore is.

> $ pg_dump -f p.dump -Fc -U postgres postgres
>
> $ pg_restore -c p.dump | grep -i public

This doesn't work because pg_dump isn't run with -c, while pg_restore
is.  If the archive is created with pg_dump -c (as the above was), then
the results match up between the two runs.  Note also that if pg_dump is
run with -c then a pg_restore without -c would actually still include
the GRANT statement, which isn't really correct either.

That's obviously a change from what we had before and wasn't
intentional.

> This is *REALLY BAD*.  Quite aside from the restore being wrong,
> those two sequences should never ever give different results.
> Stephen, you put some filtering logic in the wrong place in pg_dump.

I do wish it was that simple.

Unfortunately, the public schema is just ridiculously special, both in
the way it's a 'user' object but is created by initdb and that it's got
special non-default ACLs on it and how it has explicit special code to
skip over it when a restore is happening, unless -c is used.

What I'm afraid we need to do here is basically continue to hack on that
code in pg_backup_archiver.c's _printTocEntry() to teach it to issue the
default GRANT ALL ON SCHEMA public TO PUBLIC; when we are processing the
TOC entry for CREATE SCHEMA public;.

That would make the recreation of the public schema when pg_dump or
pg_restore is being run with -c actually match how the public schema is
created by initdb, and the rest would end up falling into place, I
think.

One complication, however, is what happens when a user drops and
recreates the public schema.  If that's done, we'll end up not dumping
out the delta from the public schema's initial ACLs, which wouldn't be
correct if you're restoring into a newly initdb'd cluster.  I'm thinking
that we need to forcibly look at the delta from
public-as-installed-by-initdb and whatever-public-is-now, regardless of
if the public schema was recreated by the user or not, because on
restore we are expecting a newly initdb'd cluster with the public schema
as originally installed (or as installed by pg_dump/pg_restore following
the logic above).

I'll play around with this approach and see if things end up working out
in a better fashion with it.  Baking this knowledge into
pg_backup_archiver.c is certainly ugly, but handling of public has
always been hard-coded into that, and we even added more special
handling to that code 10 years ago to deal with the COMMENT on the
public schema, so this is really just more of the same.

Thanks!

Stephen

signature.asc (836 bytes) Download Attachment
Previous Thread Next Thread