Yeah, this is a known issue --- the various GRANTs for a specific object
are stored in a single "TOC entry" in the archive, which pg_restore will
send to the server in a single PQexec call, causing them to be effectively
one transaction. The easiest way to deal with it is to not send
pg_restore's
On 1/14/19 12:57 PM, Sherrylyn Branchaw wrote:
I don't see how that can work:
test=# \c prod_db
You are now connected to database "prod_db" as user "postgres".
prod_db=# CREATE SCHEMA test;
CREATE SCHEMA
prod_db=# GRANT ALL ON SCHEMA test TO prod_user;
GRANT
prod_db=# GRANT USAGE ON SCHEMA t
Sherrylyn Branchaw writes:
> I'm restoring the schema from one database (prod) to another (dev). There
> are users that exist in prod that don't exist in dev. When the restore job
> tries to grant privileges to nonexistent users in dev, I would like it to
> generate an error, which is safe to igno
I don't see how that can work:
test=# \c prod_db
You are now connected to database "prod_db" as user "postgres".
prod_db=# CREATE SCHEMA test;
CREATE SCHEMA
prod_db=# GRANT ALL ON SCHEMA test TO prod_user;
GRANT
prod_db=# GRANT USAGE ON SCHEMA test TO prod_and_dev_user;
GRANT
prod_db=# \c d
On 1/14/19 12:04 PM, Sherrylyn Branchaw wrote:
The above needs more information:
1) Are the dev_* databases on a different cluster?
2) If so did you run:
CREATE ROLE prod_user;
CREATE ROLE prod_and_dev_user;
on that cluster first?
I happened to put them all on the same cluster for my test ca
The above needs more information:
1) Are the dev_* databases on a different cluster?
2) If so did you run:
CREATE ROLE prod_user;
CREATE ROLE prod_and_dev_user;
on that cluster first?
I happened to put them all on the same cluster for my test case, in order
to reproduce the unexpected behavior
On 1/14/19 10:15 AM, Sherrylyn Branchaw wrote:
Hi,
I'm running two Postgres 9.6.11 databases on RHEL 6.9.
I'm restoring the schema from one database (prod) to another (dev).
There are users that exist in prod that don't exist in dev. When the
restore job tries to grant privileges to nonexiste
Hi,
I'm running two Postgres 9.6.11 databases on RHEL 6.9.
I'm restoring the schema from one database (prod) to another (dev). There
are users that exist in prod that don't exist in dev. When the restore job
tries to grant privileges to nonexistent users in dev, I would like it to
generate an err
