Search Postgresql Archives

Re: pg_restore restores privileges differently from psql

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 test TO prod_and_dev_user;


GRANT

prod_db=# \c dev_db_psql
You are now connected to database "dev_db_psql" as user "postgres".


dev_db_psql=# DROP ROLE prod_user;
ERROR:  role "prod_user" cannot be dropped because some objects depend
on it

DETAIL:  1 object in database prod_db
Yes, if you're going to put all your databases on the same cluster, you first have to remove dependent objects before dropping the role. There are multiple ways of going about that: dropping the database, revoking the privileges on the objects in question, etc. If you put the databases on different clusters and make sure you create only the prod_and_dev_user on the second cluster, you won't run into this issue.

The goal is to make sure the prod_user role exists when the dump is taken and doesn't exist when the restore is done. You can do this by putting the databases on separate clusters and creating the appropriate roles, or by dropping the user on the single cluster.

Sorry, I considered spelling all this out in the original post, because there are two different ways of going about making sure the user isn't

True. The thing is that there are a lot of moving parts to this and the more detail you give the better the chance that someone trying to replicate actually does replicate your setup. Removes some of the uncertainty when the results diverge.

present for the restore, but it seemed unnecessarily complicated, and I thought I would let people decide what makes sense in their own environment for testing. Sorry if that led to more confusion in the end. Just make sure the user exists when you need it to exist and doesn't exist when you need it not to exist, and test both the pg_restore and psql methods, and I expect you'll see the same behavior I did (and if not, I'll be very curious).'

Well if I use two clusters and:

DROP ROLE prod_user;

on the one that has the dev_* databases then I can replicate.

I am interested in this as I have noted another divergence between a text dump and a binary dump, which as yet remains unanswered:

https://www.postgresql.org/message-id/b7a24043-1c9c-1876-f06a-8f916293c142%40aklaver.com



Best,
Sherrylyn


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux