pg_dump schema in pg11 without global permissions
Hi,
I'm trying to copy a schema from one PG database (ver 11) to PG 10.
Previously the first database version was 9.6 and the way I did the copying was
ssh -o Compression=no user@host '/opt/pgsql/bin/pg_dump --no-tablespaces -n schemaname -Fc -U dbadmin dbname' | pg_restore -U dbadmin -h localhost -1 -d dbnme
However after migrating from PG 9.6 to 11, when I did the same thing as before, I started getting a bunch of commands in the dump like this
GRANT CONNECT ON DATABASE dbname TO usernameXX;
which don't work for me because the list of users is different between different machines.
It is clear that the change is related to the way pg_dump is implemented now in PG11 that global objects are dumped.
But the question is how do I duplicate the previous behaviour, i.e. Dump just the schema and permission on the schema, not on the database.
I do know that I could grep the dump, but that's very annoying when dumping hundreds of millions of rows.
Thanks in advance for the help.
Sergey
On 7/23/19 3:19 PM, Sergey Koposov wrote:
Hi,
I'm trying to copy a schema from one PG database (ver 11) to PG 10.
Previously the first database version was 9.6 and the way I did the copying wasssh -o Compression=no user@host '/opt/pgsql/bin/pg_dump --no-tablespaces -n schemaname -Fc -U dbadmin dbname' | pg_restore -U dbadmin -h localhost -1 -d dbnme
However after migrating from PG 9.6 to 11, when I did the same thing as before, I started getting a bunch of commands in the dump like this
GRANT CONNECT ON DATABASE dbname TO usernameXX;
which don't work for me because the list of users is different between different machines.
It is clear that the change is related to the way pg_dump is implemented now in PG11 that global objects are dumped.
But the question is how do I duplicate the previous behaviour, i.e. Dump just the schema and permission on the schema, not on the database.
https://www.postgresql.org/docs/11/app-pgdump.html
-x
--no-privileges
--no-acl
Prevent dumping of access privileges (grant/revoke commands).
I do know that I could grep the dump, but that's very annoying when dumping hundreds of millions of rows.
Thanks in advance for the help.
Sergey
--
Adrian Klaver
adrian.klaver@aklaver.com
On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wrote:
On 7/23/19 3:19 PM, Sergey Koposov wrote:
Hi,
I'm trying to copy a schema from one PG database (ver 11) to PG 10.
Previously the first database version was 9.6 and the way I did the copying wasssh -o Compression=no user@host '/opt/pgsql/bin/pg_dump --no-tablespaces -n schemaname -Fc -U dbadmin dbname' | pg_restore -U dbadmin -h localhost -1 -d dbnme
However after migrating from PG 9.6 to 11, when I did the same thing as before, I started getting a bunch of commands in the dump like this
GRANT CONNECT ON DATABASE dbname TO usernameXX;
which don't work for me because the list of users is different between different machines.
It is clear that the change is related to the way pg_dump is implemented now in PG11 that global objects are dumped.
But the question is how do I duplicate the previous behaviour, i.e. Dump just the schema and permission on the schema, not on the database.https://www.postgresql.org/docs/11/app-pgdump.html
-x
--no-privileges
--no-aclPrevent dumping of access privileges (grant/revoke commands).
Yes I saw that, but that will not dump privileges on the schema itself, which were dumped before as far as I understand ...
S
On 7/23/19 3:23 PM, Sergey Koposov wrote:
On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wrote:
On 7/23/19 3:19 PM, Sergey Koposov wrote:
Hi,
I'm trying to copy a schema from one PG database (ver 11) to PG 10.
Previously the first database version was 9.6 and the way I did the copying wasssh -o Compression=no user@host '/opt/pgsql/bin/pg_dump --no-tablespaces -n schemaname -Fc -U dbadmin dbname' | pg_restore -U dbadmin -h localhost -1 -d dbnme
However after migrating from PG 9.6 to 11, when I did the same thing as before, I started getting a bunch of commands in the dump like this
GRANT CONNECT ON DATABASE dbname TO usernameXX;
which don't work for me because the list of users is different between different machines.
It is clear that the change is related to the way pg_dump is implemented now in PG11 that global objects are dumped.
But the question is how do I duplicate the previous behaviour, i.e. Dump just the schema and permission on the schema, not on the database.https://www.postgresql.org/docs/11/app-pgdump.html
-x
--no-privileges
--no-aclPrevent dumping of access privileges (grant/revoke commands).
Yes I saw that, but that will not dump privileges on the schema itself, which were dumped before as far as I understand ...
So the roles for the schema don't change, but everything else does?
S
--
Adrian Klaver
adrian.klaver@aklaver.com
On Tue, 2019-07-23 at 15:37 -0700, Adrian Klaver wrote:
On 7/23/19 3:23 PM, Sergey Koposov wrote:
On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wrote:
On 7/23/19 3:19 PM, Sergey Koposov wrote:
Hi,
I'm trying to copy a schema from one PG database (ver 11) to PG 10.
Previously the first database version was 9.6 and the way I did the copying wasssh -o Compression=no user@host '/opt/pgsql/bin/pg_dump --no-tablespaces -n schemaname -Fc -U dbadmin dbname' | pg_restore -U dbadmin -h localhost -1 -d dbnme
However after migrating from PG 9.6 to 11, when I did the same thing as before, I started getting a bunch of commands in the dump like this
GRANT CONNECT ON DATABASE dbname TO usernameXX;
which don't work for me because the list of users is different between different machines.
It is clear that the change is related to the way pg_dump is implemented now in PG11 that global objects are dumped.
But the question is how do I duplicate the previous behaviour, i.e. Dump just the schema and permission on the schema, not on the database.https://www.postgresql.org/docs/11/app-pgdump.html
-x
--no-privileges
--no-aclPrevent dumping of access privileges (grant/revoke commands).
Yes I saw that, but that will not dump privileges on the schema itself, which were dumped before as far as I understand ...
So the roles for the schema don't change, but everything else does?
The schema permissions are granted to a generic user 'dbuser'. And a bunch of users are members of this role and that's how they access the schema.
The database permissions on the other hand are granted specifically per individual user.
I am not saying this is optimal, but it would be very annoying if now it became impossible to deal with this...
S
On 7/23/19 3:42 PM, Sergey Koposov wrote:
On Tue, 2019-07-23 at 15:37 -0700, Adrian Klaver wrote:
On 7/23/19 3:23 PM, Sergey Koposov wrote:
On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wrote:
On 7/23/19 3:19 PM, Sergey Koposov wrote:
Hi,
I'm trying to copy a schema from one PG database (ver 11) to PG 10.
Previously the first database version was 9.6 and the way I did the copying wasssh -o Compression=no user@host '/opt/pgsql/bin/pg_dump --no-tablespaces -n schemaname -Fc -U dbadmin dbname' | pg_restore -U dbadmin -h localhost -1 -d dbnme
However after migrating from PG 9.6 to 11, when I did the same thing as before, I started getting a bunch of commands in the dump like this
GRANT CONNECT ON DATABASE dbname TO usernameXX;
which don't work for me because the list of users is different between different machines.
It is clear that the change is related to the way pg_dump is implemented now in PG11 that global objects are dumped.
But the question is how do I duplicate the previous behaviour, i.e. Dump just the schema and permission on the schema, not on the database.https://www.postgresql.org/docs/11/app-pgdump.html
-x
--no-privileges
--no-aclPrevent dumping of access privileges (grant/revoke commands).
Yes I saw that, but that will not dump privileges on the schema itself, which were dumped before as far as I understand ...
So the roles for the schema don't change, but everything else does?
The schema permissions are granted to a generic user 'dbuser'. And a bunch of users are members of this role and that's how they access the schema.
The database permissions on the other hand are granted specifically per individual user.
What version of pg_dump are you using?
When I dump a version 10 database using a version 11 pg_dump I do not
see: GRANT CONNECT ON DATABASE
pg_dump -Fc -n utility -s -d production -U postgres -p 5422 -f
schema_test.out
pg_restore -f schema_test_fc.sql schema_test.out
aklaver@ranger:~> grep CONNECT schema_test_fc.sql
aklaver@ranger:~>
I am not saying this is optimal, but it would be very annoying if now it became impossible to deal with this...
S
--
Adrian Klaver
adrian.klaver@aklaver.com
On Tue, 2019-07-23 at 15:52 -0700, Adrian Klaver wrote:
On 7/23/19 3:42 PM, Sergey Koposov wrote:
On Tue, 2019-07-23 at 15:37 -0700, Adrian Klaver wrote:
On 7/23/19 3:23 PM, Sergey Koposov wrote:
On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wrote:
On 7/23/19 3:19 PM, Sergey Koposov wrote:
Hi,
I'm trying to copy a schema from one PG database (ver 11) to PG 10.
Previously the first database version was 9.6 and the way I did the copying wasssh -o Compression=no user@host '/opt/pgsql/bin/pg_dump --no-tablespaces -n schemaname -Fc -U dbadmin dbname' | pg_restore -U dbadmin -h localhost -1 -d dbnme
However after migrating from PG 9.6 to 11, when I did the same thing as before, I started getting a bunch of commands in the dump like this
GRANT CONNECT ON DATABASE dbname TO usernameXX;
which don't work for me because the list of users is different between different machines.
It is clear that the change is related to the way pg_dump is implemented now in PG11 that global objects are dumped.
But the question is how do I duplicate the previous behaviour, i.e. Dump just the schema and permission on the schema, not on the database.https://www.postgresql.org/docs/11/app-pgdump.html
-x
--no-privileges
--no-aclPrevent dumping of access privileges (grant/revoke commands).
Yes I saw that, but that will not dump privileges on the schema itself, which were dumped before as far as I understand ...
So the roles for the schema don't change, but everything else does?
The schema permissions are granted to a generic user 'dbuser'. And a bunch of users are members of this role and that's how they access the schema.
The database permissions on the other hand are granted specifically per individual user.What version of pg_dump are you using?
When I dump a version 10 database using a version 11 pg_dump I do not
see: GRANT CONNECT ON DATABASEpg_dump -Fc -n utility -s -d production -U postgres -p 5422 -f
schema_test.outpg_restore -f schema_test_fc.sql schema_test.out
I'm dumping version 11 database using version 11 pg_dump. I double checked this.
I don't know if in your test-case you have custom users whom you granted connect permissions. I do have them.
Also what I'm seeing matches perfectly the release notes on pgdump which explicitely mention dumping of the global properties.
https://www.postgresql.org/docs/11/release-11.html#id-1.11.6.9.4
S
On 7/23/19 3:58 PM, Sergey Koposov wrote:
On Tue, 2019-07-23 at 15:52 -0700, Adrian Klaver wrote:
On 7/23/19 3:42 PM, Sergey Koposov wrote:
On Tue, 2019-07-23 at 15:37 -0700, Adrian Klaver wrote:
On 7/23/19 3:23 PM, Sergey Koposov wrote:
On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wrote:
On 7/23/19 3:19 PM, Sergey Koposov wrote:
Hi,
I'm trying to copy a schema from one PG database (ver 11) to PG 10.
Previously the first database version was 9.6 and the way I did the copying wasssh -o Compression=no user@host '/opt/pgsql/bin/pg_dump --no-tablespaces -n schemaname -Fc -U dbadmin dbname' | pg_restore -U dbadmin -h localhost -1 -d dbnme
However after migrating from PG 9.6 to 11, when I did the same thing as before, I started getting a bunch of commands in the dump like this
GRANT CONNECT ON DATABASE dbname TO usernameXX;
which don't work for me because the list of users is different between different machines.
It is clear that the change is related to the way pg_dump is implemented now in PG11 that global objects are dumped.
But the question is how do I duplicate the previous behaviour, i.e. Dump just the schema and permission on the schema, not on the database.https://www.postgresql.org/docs/11/app-pgdump.html
-x
--no-privileges
--no-aclPrevent dumping of access privileges (grant/revoke commands).
Yes I saw that, but that will not dump privileges on the schema itself, which were dumped before as far as I understand ...
So the roles for the schema don't change, but everything else does?
The schema permissions are granted to a generic user 'dbuser'. And a bunch of users are members of this role and that's how they access the schema.
The database permissions on the other hand are granted specifically per individual user.What version of pg_dump are you using?
When I dump a version 10 database using a version 11 pg_dump I do not
see: GRANT CONNECT ON DATABASEpg_dump -Fc -n utility -s -d production -U postgres -p 5422 -f
schema_test.outpg_restore -f schema_test_fc.sql schema_test.out
I'm dumping version 11 database using version 11 pg_dump. I double checked this.
I don't know if in your test-case you have custom users whom you granted connect permissions. I do have them.
Also what I'm seeing matches perfectly the release notes on pgdump which explicitely mention dumping of the global properties.
https://www.postgresql.org/docs/11/release-11.html#id-1.11.6.9.4
That is only supposed to happen if you use -C(--create) and I am seeing
that in your examples.
S
--
Adrian Klaver
adrian.klaver@aklaver.com
On 7/23/19 4:04 PM, Adrian Klaver wrote:
On 7/23/19 3:58 PM, Sergey Koposov wrote:
On Tue, 2019-07-23 at 15:52 -0700, Adrian Klaver wrote:
On 7/23/19 3:42 PM, Sergey Koposov wrote:
On Tue, 2019-07-23 at 15:37 -0700, Adrian Klaver wrote:
On 7/23/19 3:23 PM, Sergey Koposov wrote:
On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wrote:
On 7/23/19 3:19 PM, Sergey Koposov wrote:
Hi,
I'm trying to copy a schema from one PG database (ver 11) to PG 10.
Previously the first database version was 9.6 and the way I did
the copying wasssh -o Compression=no user@host '/opt/pgsql/bin/pg_dump
--no-tablespaces -n schemaname -Fc -U dbadmin dbname' |
pg_restore -U dbadmin -h localhost -1 -d dbnmeHowever after migrating from PG 9.6 to 11, when I did the same
thing as before, I started getting a bunch of commands in the
dump like thisGRANT CONNECT ON DATABASE dbname TO usernameXX;
which don't work for me because the list of users is different
between different machines.
It is clear that the change is related to the way pg_dump is
implemented now in PG11 that global objects are dumped.
But the question is how do I duplicate the previous behaviour,
i.e. Dump just the schema and permission on the schema, not on
the database.https://www.postgresql.org/docs/11/app-pgdump.html
-x
--no-privileges
--no-aclPrevent dumping of access privileges (grant/revoke
commands).Yes I saw that, but that will not dump privileges on the schema
itself, which were dumped before as far as I understand ...So the roles for the schema don't change, but everything else does?
The schema permissions are granted to a generic user 'dbuser'. And a
bunch of users are members of this role and that's how they access
the schema.
The database permissions on the other hand are granted specifically
per individual user.What version of pg_dump are you using?
When I dump a version 10 database using a version 11 pg_dump I do not
see: GRANT CONNECT ON DATABASEpg_dump -Fc -n utility -s -d production -U postgres -p 5422 -f
schema_test.outpg_restore -f schema_test_fc.sql schema_test.out
I'm dumping version 11 database using version 11 pg_dump. I double
checked this.I don't know if in your test-case you have custom users whom you
granted connect permissions. I do have them.
Also what I'm seeing matches perfectly the release notes on pgdump
which explicitely mention dumping of the global properties.
https://www.postgresql.org/docs/11/release-11.html#id-1.11.6.9.4That is only supposed to happen if you use -C(--create) and I am seeing
^not
that in your examples.
S
--
Adrian Klaver
adrian.klaver@aklaver.com
On Tue, 2019-07-23 at 16:04 -0700, Adrian Klaver wrote:
On 7/23/19 3:58 PM, Sergey Koposov wrote:
On Tue, 2019-07-23 at 15:52 -0700, Adrian Klaver wrote:
On 7/23/19 3:42 PM, Sergey Koposov wrote:
On Tue, 2019-07-23 at 15:37 -0700, Adrian Klaver wrote:
On 7/23/19 3:23 PM, Sergey Koposov wrote:
On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wrote:
On 7/23/19 3:19 PM, Sergey Koposov wrote:
Hi,
I'm trying to copy a schema from one PG database (ver 11) to PG 10.
Previously the first database version was 9.6 and the way I did the copying wasssh -o Compression=no user@host '/opt/pgsql/bin/pg_dump --no-tablespaces -n schemaname -Fc -U dbadmin dbname' | pg_restore -U dbadmin -h localhost -1 -d dbnme
However after migrating from PG 9.6 to 11, when I did the same thing as before, I started getting a bunch of commands in the dump like this
GRANT CONNECT ON DATABASE dbname TO usernameXX;
which don't work for me because the list of users is different between different machines.
It is clear that the change is related to the way pg_dump is implemented now in PG11 that global objects are dumped.
But the question is how do I duplicate the previous behaviour, i.e. Dump just the schema and permission on the schema, not on the database.https://www.postgresql.org/docs/11/app-pgdump.html
-x
--no-privileges
--no-aclPrevent dumping of access privileges (grant/revoke commands).
Yes I saw that, but that will not dump privileges on the schema itself, which were dumped before as far as I understand ...
So the roles for the schema don't change, but everything else does?
The schema permissions are granted to a generic user 'dbuser'. And a bunch of users are members of this role and that's how they access the schema.
The database permissions on the other hand are granted specifically per individual user.What version of pg_dump are you using?
When I dump a version 10 database using a version 11 pg_dump I do not
see: GRANT CONNECT ON DATABASEpg_dump -Fc -n utility -s -d production -U postgres -p 5422 -f
schema_test.outpg_restore -f schema_test_fc.sql schema_test.out
I'm dumping version 11 database using version 11 pg_dump. I double checked this.
I don't know if in your test-case you have custom users whom you granted connect permissions. I do have them.
Also what I'm seeing matches perfectly the release notes on pgdump which explicitely mention dumping of the global properties.
https://www.postgresql.org/docs/11/release-11.html#id-1.11.6.9.4That is only supposed to happen if you use -C(--create) and I am seeing
that in your examples.
For some reason I see when I just have
a command like this it doesnt' have a grant connect on database
~/soft/pgsql_install/bin/pg_dump -U skoposov -h localhost -n xx --no-tablespaces test1
but when I add '-Fc' flag to pg_dump, I can see 'grant connect' inside the output (interdispersed with binary stuff)
I don't get it
S
Show quoted text
S
[ hey guys, please trim your replies ]
Sergey Koposov <skoposov@cmu.edu> writes:
On Tue, 2019-07-23 at 16:04 -0700, Adrian Klaver wrote:
That is only supposed to happen if you use -C(--create) and I am seeing
that in your examples.
For some reason I see when I just have
a command like this it doesnt' have a grant connect on database
~/soft/pgsql_install/bin/pg_dump -U skoposov -h localhost -n xx --no-tablespaces test1
but when I add '-Fc' flag to pg_dump, I can see 'grant connect' inside the output (interdispersed with binary stuff)
The GRANT CONNECT will be in the archive, but pg_restore should only
print it if you specified -C.
Experimenting, however, I see that that only works as intended if
I use v11 pg_restore. I can replicate your result if I use v10
pg_restore. So ... don't do that.
regards, tom lane
On Tue, 2019-07-23 at 19:24 -0400, Tom Lane wrote:
[ hey guys, please trim your replies ]
sorry
Experimenting, however, I see that that only works as intended if
I use v11 pg_restore. I can replicate your result if I use v10
pg_restore. So ... don't do that.
Thanks very much!
I've started the pg_dump|pg_restore using the v11 pg_restore. I'll see it works in a few hours.
S
On 7/23/19 4:17 PM, Sergey Koposov wrote:
For some reason I see when I just have
a command like this it doesnt' have a grant connect on database
~/soft/pgsql_install/bin/pg_dump -U skoposov -h localhost -n xx --no-tablespaces test1
but when I add '-Fc' flag to pg_dump, I can see 'grant connect' inside the output (interdispersed with binary stuff)I don't get it
As Tom explained the custom format will include a lot of information
that you can retrieve later or not. For this case:
https://www.postgresql.org/docs/11/app-pgdump.html
"-C
--create
Begin the output with a command to create the database itself and
reconnect to the created database. (With a script of this form, it
doesn't matter which database in the destination installation you
connect to before running the script.) If --clean is also specified, the
script drops and recreates the target database before reconnecting to it.
With --create, the output also includes the database's comment if
any, and any configuration variable settings that are specific to this
database, that is, any ALTER DATABASE ... SET ... and ALTER ROLE ... IN
DATABASE ... SET ... commands that mention this database. Access
privileges for the database itself are also dumped, unless --no-acl is
specified.
This option is only meaningful for the plain-text format. For the
archive formats, you can specify the option when you call pg_restore.
"
NOTE the last paragraph.
S
S
--
Adrian Klaver
adrian.klaver@aklaver.com
On 7/23/19 4:32 PM, Sergey Koposov wrote:
On Tue, 2019-07-23 at 19:24 -0400, Tom Lane wrote:
[ hey guys, please trim your replies ]
sorry
Experimenting, however, I see that that only works as intended if
I use v11 pg_restore. I can replicate your result if I use v10
pg_restore. So ... don't do that.Thanks very much!
I've started the pg_dump|pg_restore using the v11 pg_restore. I'll see it works in a few hours.
For future use. To test this sort of thing use the -s switch to limit
the restore to objects only(no data). This will speed the process and
confirm the permissions on the objects.
S
--
Adrian Klaver
adrian.klaver@aklaver.com