-- Summary -- Server 1: created sample database stgsample01 with owner stgsamplelogin01 -- Server 1: the sample database has 6 tables, each table has rights assigned to group roles sample_read_role and sample_write_role -- Server 1: used pg_dump to export the database -- Server 2: created sample empty database stgsample02 with owner stgsamplelogin02 -- Server 2: purposly did not create the old owner stgsamplelogin01 -- Server 2: used pg_restore to restore the dump -- Server 2: pg_restore gave an error when setting owner stgsamplelogin01 and then did not assign any rights to group roles sample_read_role and sample_write_role -- Server 3: created sample empty database stgsample03 with owner stgsamplelogin01 -- Server 3: purposly did not create group role sample_read_role -- Server 3: used pg_restore to restore the dump -- Server 3: pg_restore gave an error when group role sample_read_role was not found and then did not assign any rights to group roles sample_write_role ---- On SERVER_1 ---- CREATE ROLE sample_read_role NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; CREATE ROLE sample_write_role NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; CREATE ROLE stgsamplelogin01 LOGIN ENCRYPTED PASSWORD 'md5ea60c5e12ef0bd0e1344eb53e0078a67' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; GRANT sample_write_role TO stgsamplelogin01; CREATE DATABASE stgsample01 WITH OWNER = stgsamplelogin01 TEMPLATE=TEMPLATE0 ENCODING = 'SQL_ASCII' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = 20; psql stgsample01 < /var/lib/pgsql/data/dumps/sample_schema.sql pg_dump -v -p 5432 -Fc -s stgsample01 > /var/lib/pgsql/data/dumps/stgsample01_schema.dump pg_dump: reading extensions pg_dump: identifying extension members pg_dump: reading schemas pg_dump: reading user-defined tables pg_dump: reading user-defined functions pg_dump: reading user-defined types pg_dump: reading procedural languages pg_dump: reading user-defined aggregate functions pg_dump: reading user-defined operators pg_dump: reading user-defined operator classes pg_dump: reading user-defined operator families pg_dump: reading user-defined text search parsers pg_dump: reading user-defined text search templates pg_dump: reading user-defined text search dictionaries pg_dump: reading user-defined text search configurations pg_dump: reading user-defined foreign-data wrappers pg_dump: reading user-defined foreign servers pg_dump: reading default privileges pg_dump: reading user-defined collations pg_dump: reading user-defined conversions pg_dump: reading type casts pg_dump: reading transforms pg_dump: reading table inheritance information pg_dump: reading event triggers pg_dump: finding extension tables pg_dump: finding inheritance relationships pg_dump: reading column info for interesting tables pg_dump: finding the columns and types of table "public.country" pg_dump: finding the columns and types of table "public.country_alias" pg_dump: finding the columns and types of table "public.log" pg_dump: finding the columns and types of table "public.result" pg_dump: finding the columns and types of table "public.tariff" pg_dump: finding the columns and types of table "public.tariff_bk20140630" pg_dump: flagging inherited columns in subtables pg_dump: reading indexes pg_dump: reading indexes for table "public.country" pg_dump: reading indexes for table "public.country_alias" pg_dump: reading indexes for table "public.log" pg_dump: reading indexes for table "public.result" pg_dump: reading indexes for table "public.tariff" pg_dump: reading constraints pg_dump: reading foreign key constraints for table "public.country" pg_dump: reading foreign key constraints for table "public.country_alias" pg_dump: reading foreign key constraints for table "public.log" pg_dump: reading foreign key constraints for table "public.result" pg_dump: reading foreign key constraints for table "public.tariff" pg_dump: reading triggers pg_dump: reading triggers for table "public.country" pg_dump: reading triggers for table "public.country_alias" pg_dump: reading triggers for table "public.log" pg_dump: reading triggers for table "public.result" pg_dump: reading triggers for table "public.tariff" pg_dump: reading rewrite rules pg_dump: reading policies pg_dump: reading row security enabled for table "public.country" pg_dump: reading policies for table "public.country" pg_dump: reading row security enabled for table "public.country_alias" pg_dump: reading policies for table "public.country_alias" pg_dump: reading row security enabled for table "public.log" pg_dump: reading policies for table "public.log" pg_dump: reading row security enabled for table "public.result" pg_dump: reading policies for table "public.result" pg_dump: reading row security enabled for table "public.tariff" pg_dump: reading policies for table "public.tariff" pg_dump: reading row security enabled for table "public.tariff_bk20140630" pg_dump: reading policies for table "public.tariff_bk20140630" pg_dump: reading dependency data pg_dump: saving encoding = SQL_ASCII pg_dump: saving standard_conforming_strings = on pg_dump: saving database definition [postgres@server1 ~]$ psql psql (9.5.1) Type "help" for help. postgres=# \connect stgsample01 stgsamplelogin01 You are now connected to database "stgsample01" as user "stgsamplelogin01". stgsample01=> \dp Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+-------------------+-------+--------------------------------------------+-------------------+---------- public | country | table | stgsamplelogin01=arwdDxt/stgsamplelogin01 +| | | | | sample_read_role=r/stgsamplelogin01 +| | | | | sample_write_role=arwdDxt/stgsamplelogin01 | | public | country_alias | table | stgsamplelogin01=arwdDxt/stgsamplelogin01 +| | | | | sample_read_role=r/stgsamplelogin01 +| | | | | sample_write_role=arwdDxt/stgsamplelogin01 | | public | log | table | stgsamplelogin01=arwdDxt/stgsamplelogin01 +| | | | | sample_read_role=r/stgsamplelogin01 +| | | | | sample_write_role=arwdDxt/stgsamplelogin01 | | public | result | table | stgsamplelogin01=arwdDxt/stgsamplelogin01 +| | | | | sample_read_role=r/stgsamplelogin01 +| | | | | sample_write_role=arwdDxt/stgsamplelogin01 | | public | tariff | table | stgsamplelogin01=arwdDxt/stgsamplelogin01 +| | | | | sample_read_role=r/stgsamplelogin01 +| | | | | sample_write_role=arwdDxt/stgsamplelogin01 | | public | tariff_bk20140630 | table | | | (6 rows) ---- On SERVER_2 ---- -- transfer dump from server 1 CREATE ROLE sample_read_role NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; CREATE ROLE sample_write_role NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; CREATE ROLE stgsamplelogin02 LOGIN ENCRYPTED PASSWORD 'md5ea60c5e12ef0bd0e1344eb53e0078a67' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; GRANT sample_write_role TO stgsamplelogin02; CREATE DATABASE stgsample02 WITH OWNER = stgsamplelogin02 TEMPLATE=TEMPLATE0 ENCODING = 'SQL_ASCII' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = 20; [postgres@server2 ~]$ pg_restore -v -p 5432 -d stgsample02 /var/lib/pgsql/data/dumps/stgsample01_schema.dump pg_restore: connecting to database for restore pg_restore: creating SCHEMA "public" pg_restore: creating COMMENT "SCHEMA public" pg_restore: creating EXTENSION "plpgsql" pg_restore: creating COMMENT "EXTENSION plpgsql" pg_restore: creating FUNCTION "public.levenshtein(text, text)" pg_restore: creating FUNCTION "public.metaphone(text, integer)" pg_restore: creating FUNCTION "public.soundex(text)" pg_restore: creating FUNCTION "public.text_soundex(text)" pg_restore: creating TABLE "public.country" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 181; 1259 95214081 TABLE country stgsamplelogin01 pg_restore: [archiver (db)] could not execute query: ERROR: role "stgsamplelogin01" does not exist Command was: ALTER TABLE country OWNER TO stgsamplelogin01; pg_restore: creating TABLE "public.country_alias" pg_restore: [archiver (db)] Error from TOC entry 182; 1259 95214087 TABLE country_alias stgsamplelogin01 pg_restore: [archiver (db)] could not execute query: ERROR: role "stgsamplelogin01" does not exist Command was: ALTER TABLE country_alias OWNER TO stgsamplelogin01; pg_restore: creating TABLE "public.log" pg_restore: [archiver (db)] Error from TOC entry 183; 1259 95214093 TABLE log stgsamplelogin01 pg_restore: [archiver (db)] could not execute query: ERROR: role "stgsamplelogin01" does not exist Command was: ALTER TABLE log OWNER TO stgsamplelogin01; pg_restore: creating TABLE "public.result" pg_restore: [archiver (db)] Error from TOC entry 184; 1259 95214099 TABLE result stgsamplelogin01 pg_restore: [archiver (db)] could not execute query: ERROR: role "stgsamplelogin01" does not exist Command was: ALTER TABLE result OWNER TO stgsamplelogin01; pg_restore: creating TABLE "public.tariff" pg_restore: [archiver (db)] Error from TOC entry 185; 1259 95214105 TABLE tariff stgsamplelogin01 pg_restore: [archiver (db)] could not execute query: ERROR: role "stgsamplelogin01" does not exist Command was: ALTER TABLE tariff OWNER TO stgsamplelogin01; pg_restore: creating TABLE "public.tariff_bk20140630" pg_restore: creating CONSTRAINT "public.country_alias_pkey" pg_restore: creating CONSTRAINT "public.country_country_code_key" pg_restore: creating CONSTRAINT "public.country_pkey" pg_restore: creating CONSTRAINT "public.log_pkey" pg_restore: creating CONSTRAINT "public.result_pkey" pg_restore: creating CONSTRAINT "public.tariff_pkey" pg_restore: creating FK CONSTRAINT "public.$1" pg_restore: creating FK CONSTRAINT "public.$1" pg_restore: creating FK CONSTRAINT "public.$1" pg_restore: creating FK CONSTRAINT "public.$2" pg_restore: creating FK CONSTRAINT "public.$2" pg_restore: setting owner and privileges for DATABASE "stgsample01" pg_restore: setting owner and privileges for SCHEMA "public" pg_restore: setting owner and privileges for COMMENT "SCHEMA public" pg_restore: setting owner and privileges for ACL "public" pg_restore: setting owner and privileges for EXTENSION "plpgsql" pg_restore: setting owner and privileges for COMMENT "EXTENSION plpgsql" pg_restore: setting owner and privileges for FUNCTION "public.levenshtein(text, text)" pg_restore: setting owner and privileges for FUNCTION "public.metaphone(text, integer)" pg_restore: setting owner and privileges for FUNCTION "public.soundex(text)" pg_restore: setting owner and privileges for FUNCTION "public.text_soundex(text)" pg_restore: setting owner and privileges for TABLE "public.country" pg_restore: setting owner and privileges for ACL "public.country" pg_restore: [archiver (db)] Error from TOC entry 3067; 0 0 ACL country stgsamplelogin01 pg_restore: [archiver (db)] could not execute query: ERROR: role "stgsamplelogin01" does not exist Command was: REVOKE ALL ON TABLE country FROM PUBLIC; REVOKE ALL ON TABLE country FROM stgsamplelogin01; GRANT ALL ON TABLE country TO st... pg_restore: setting owner and privileges for TABLE "public.country_alias" pg_restore: setting owner and privileges for ACL "public.country_alias" pg_restore: [archiver (db)] Error from TOC entry 3068; 0 0 ACL country_alias stgsamplelogin01 pg_restore: [archiver (db)] could not execute query: ERROR: role "stgsamplelogin01" does not exist Command was: REVOKE ALL ON TABLE country_alias FROM PUBLIC; REVOKE ALL ON TABLE country_alias FROM stgsamplelogin01; GRANT ALL ON TABLE c... pg_restore: setting owner and privileges for TABLE "public.log" pg_restore: setting owner and privileges for ACL "public.log" pg_restore: [archiver (db)] Error from TOC entry 3069; 0 0 ACL log stgsamplelogin01 pg_restore: [archiver (db)] could not execute query: ERROR: role "stgsamplelogin01" does not exist Command was: REVOKE ALL ON TABLE log FROM PUBLIC; REVOKE ALL ON TABLE log FROM stgsamplelogin01; GRANT ALL ON TABLE log TO stgsamplelogin... pg_restore: setting owner and privileges for TABLE "public.result" pg_restore: setting owner and privileges for ACL "public.result" pg_restore: [archiver (db)] Error from TOC entry 3070; 0 0 ACL result stgsamplelogin01 pg_restore: [archiver (db)] could not execute query: ERROR: role "stgsamplelogin01" does not exist Command was: REVOKE ALL ON TABLE result FROM PUBLIC; REVOKE ALL ON TABLE result FROM stgsamplelogin01; GRANT ALL ON TABLE result TO stgsa... pg_restore: setting owner and privileges for TABLE "public.tariff" pg_restore: setting owner and privileges for ACL "public.tariff" pg_restore: [archiver (db)] Error from TOC entry 3071; 0 0 ACL tariff stgsamplelogin01 pg_restore: [archiver (db)] could not execute query: ERROR: role "stgsamplelogin01" does not exist Command was: REVOKE ALL ON TABLE tariff FROM PUBLIC; REVOKE ALL ON TABLE tariff FROM stgsamplelogin01; GRANT ALL ON TABLE tariff TO stgsa... pg_restore: setting owner and privileges for TABLE "public.tariff_bk20140630" pg_restore: setting owner and privileges for CONSTRAINT "public.country_alias_pkey" pg_restore: setting owner and privileges for CONSTRAINT "public.country_country_code_key" pg_restore: setting owner and privileges for CONSTRAINT "public.country_pkey" pg_restore: setting owner and privileges for CONSTRAINT "public.log_pkey" pg_restore: setting owner and privileges for CONSTRAINT "public.result_pkey" pg_restore: setting owner and privileges for CONSTRAINT "public.tariff_pkey" pg_restore: setting owner and privileges for FK CONSTRAINT "public.$1" pg_restore: setting owner and privileges for FK CONSTRAINT "public.$1" pg_restore: setting owner and privileges for FK CONSTRAINT "public.$1" pg_restore: setting owner and privileges for FK CONSTRAINT "public.$2" pg_restore: setting owner and privileges for FK CONSTRAINT "public.$2" WARNING: errors ignored on restore: 10 [postgres@server2 ~]$ psql psql (9.5.1) Type "help" for help. postgres=# \connect stgsample02 stgsamplelogin02 You are now connected to database "stgsample02" as user "stgsamplelogin02". stgsample02=> \dp Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+-------------------+-------+-------------------+-------------------+---------- public | country | table | | | public | country_alias | table | | | public | log | table | | | public | result | table | | | public | tariff | table | | | public | tariff_bk20140630 | table | | | (6 rows) ---- On SERVER_3 ---- -- transfer dump from server 1 -- sample_read_role not created on purpose CREATE ROLE sample_write_role NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; CREATE ROLE stgsamplelogin01 LOGIN ENCRYPTED PASSWORD 'md5ea60c5e12ef0bd0e1344eb53e0078a67' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; GRANT sample_write_role TO stgsamplelogin01; CREATE DATABASE stgsample03 WITH OWNER = stgsamplelogin01 TEMPLATE=TEMPLATE0 ENCODING = 'SQL_ASCII' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = 20; [postgres@server3 ~]$ pg_restore -v -p 5432 -d stgsample03 /var/lib/pgsql/data/dumps/stgsample01_schema.dump pg_restore: connecting to database for restore pg_restore: creating SCHEMA "public" pg_restore: creating COMMENT "SCHEMA public" pg_restore: creating EXTENSION "plpgsql" pg_restore: creating COMMENT "EXTENSION plpgsql" pg_restore: creating FUNCTION "public.levenshtein(text, text)" pg_restore: creating FUNCTION "public.metaphone(text, integer)" pg_restore: creating FUNCTION "public.soundex(text)" pg_restore: creating FUNCTION "public.text_soundex(text)" pg_restore: creating TABLE "public.country" pg_restore: creating TABLE "public.country_alias" pg_restore: creating TABLE "public.log" pg_restore: creating TABLE "public.result" pg_restore: creating TABLE "public.tariff" pg_restore: creating TABLE "public.tariff_bk20140630" pg_restore: creating CONSTRAINT "public.country_alias_pkey" pg_restore: creating CONSTRAINT "public.country_country_code_key" pg_restore: creating CONSTRAINT "public.country_pkey" pg_restore: creating CONSTRAINT "public.log_pkey" pg_restore: creating CONSTRAINT "public.result_pkey" pg_restore: creating CONSTRAINT "public.tariff_pkey" pg_restore: creating FK CONSTRAINT "public.$1" pg_restore: creating FK CONSTRAINT "public.$1" pg_restore: creating FK CONSTRAINT "public.$1" pg_restore: creating FK CONSTRAINT "public.$2" pg_restore: creating FK CONSTRAINT "public.$2" pg_restore: setting owner and privileges for DATABASE "stgsample01" pg_restore: setting owner and privileges for SCHEMA "public" pg_restore: setting owner and privileges for COMMENT "SCHEMA public" pg_restore: setting owner and privileges for ACL "public" pg_restore: setting owner and privileges for EXTENSION "plpgsql" pg_restore: setting owner and privileges for COMMENT "EXTENSION plpgsql" pg_restore: setting owner and privileges for FUNCTION "public.levenshtein(text, text)" pg_restore: setting owner and privileges for FUNCTION "public.metaphone(text, integer)" pg_restore: setting owner and privileges for FUNCTION "public.soundex(text)" pg_restore: setting owner and privileges for FUNCTION "public.text_soundex(text)" pg_restore: setting owner and privileges for TABLE "public.country" pg_restore: setting owner and privileges for ACL "public.country" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3067; 0 0 ACL country stgsamplelogin01 pg_restore: [archiver (db)] could not execute query: ERROR: role "sample_read_role" does not exist Command was: REVOKE ALL ON TABLE country FROM PUBLIC; REVOKE ALL ON TABLE country FROM stgsamplelogin01; GRANT ALL ON TABLE country TO st... pg_restore: setting owner and privileges for TABLE "public.country_alias" pg_restore: setting owner and privileges for ACL "public.country_alias" pg_restore: [archiver (db)] Error from TOC entry 3068; 0 0 ACL country_alias stgsamplelogin01 pg_restore: [archiver (db)] could not execute query: ERROR: role "sample_read_role" does not exist Command was: REVOKE ALL ON TABLE country_alias FROM PUBLIC; REVOKE ALL ON TABLE country_alias FROM stgsamplelogin01; GRANT ALL ON TABLE c... pg_restore: setting owner and privileges for TABLE "public.log" pg_restore: setting owner and privileges for ACL "public.log" pg_restore: [archiver (db)] Error from TOC entry 3069; 0 0 ACL log stgsamplelogin01 pg_restore: [archiver (db)] could not execute query: ERROR: role "sample_read_role" does not exist Command was: REVOKE ALL ON TABLE log FROM PUBLIC; REVOKE ALL ON TABLE log FROM stgsamplelogin01; GRANT ALL ON TABLE log TO stgsamplelogin... pg_restore: setting owner and privileges for TABLE "public.result" pg_restore: setting owner and privileges for ACL "public.result" pg_restore: [archiver (db)] Error from TOC entry 3070; 0 0 ACL result stgsamplelogin01 pg_restore: [archiver (db)] could not execute query: ERROR: role "sample_read_role" does not exist Command was: REVOKE ALL ON TABLE result FROM PUBLIC; REVOKE ALL ON TABLE result FROM stgsamplelogin01; GRANT ALL ON TABLE result TO stgsa... pg_restore: setting owner and privileges for TABLE "public.tariff" pg_restore: setting owner and privileges for ACL "public.tariff" pg_restore: [archiver (db)] Error from TOC entry 3071; 0 0 ACL tariff stgsamplelogin01 pg_restore: [archiver (db)] could not execute query: ERROR: role "sample_read_role" does not exist Command was: REVOKE ALL ON TABLE tariff FROM PUBLIC; REVOKE ALL ON TABLE tariff FROM stgsamplelogin01; GRANT ALL ON TABLE tariff TO stgsa... pg_restore: setting owner and privileges for TABLE "public.tariff_bk20140630" pg_restore: setting owner and privileges for CONSTRAINT "public.country_alias_pkey" pg_restore: setting owner and privileges for CONSTRAINT "public.country_country_code_key" pg_restore: setting owner and privileges for CONSTRAINT "public.country_pkey" pg_restore: setting owner and privileges for CONSTRAINT "public.log_pkey" pg_restore: setting owner and privileges for CONSTRAINT "public.result_pkey" pg_restore: setting owner and privileges for CONSTRAINT "public.tariff_pkey" pg_restore: setting owner and privileges for FK CONSTRAINT "public.$1" pg_restore: setting owner and privileges for FK CONSTRAINT "public.$1" pg_restore: setting owner and privileges for FK CONSTRAINT "public.$1" pg_restore: setting owner and privileges for FK CONSTRAINT "public.$2" pg_restore: setting owner and privileges for FK CONSTRAINT "public.$2" WARNING: errors ignored on restore: 5 [postgres@tstpgdb02 ~]$ psql psql (9.5.1) Type "help" for help. postgres=# \connect stgsample03 stgsamplelogin01 You are now connected to database "stgsample03" as user "stgsamplelogin01". stgsample03=> \dp Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+-------------------+-------+-------------------+-------------------+---------- public | country | table | | | public | country_alias | table | | | public | log | table | | | public | result | table | | | public | tariff | table | | | public | tariff_bk20140630 | table | | | (6 rows)