Re: BUG #14009: pg_restore not restoring privilegs when one login/group role is missing

I need the -d to specify the target database, without it restore is made to
the current user which in my case was "postgres".

moving -d to psql worked fine :

pg_restore /var/lib/pgsql/data/dumps/stgsample01_schema.dump | psql -d
stgsample05

---- On SERVER_5 ----

-- 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 stgsample05
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(at)server5 ~]$ pg_restore
/var/lib/pgsql/data/dumps/stgsample01_schema.dump | psql -d stgsample05

SET
SET
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
ERROR: role "sample_read_role" does not exist
GRANT
REVOKE
REVOKE
GRANT
ERROR: role "sample_read_role" does not exist
GRANT
REVOKE
REVOKE
GRANT
ERROR: role "sample_read_role" does not exist
GRANT
REVOKE
REVOKE
GRANT
ERROR: role "sample_read_role" does not exist
GRANT
REVOKE
REVOKE
GRANT
ERROR: role "sample_read_role" does not exist
GRANT

[postgres(at)server5 ~]$ psql
psql (9.5.1)
Type "help" for help.

postgres=# \connect stgsample05 stgsamplelogin01
You are now connected to database "stgsample05" as user "stgsamplelogin01".
stgsample05=> \dp
Access privileges
Schema | Name | Type | Access
privileges | Column privileges | Policies
--------+-------------------+-------+--------------------------------------------+-------------------+----------
public | country | table |
stgsamplelogin01=arwdDxt/stgsamplelogin01 +| |
| | |
sample_write_role=arwdDxt/stgsamplelogin01 | |
public | country_alias | table |
stgsamplelogin01=arwdDxt/stgsamplelogin01 +| |
| | |
sample_write_role=arwdDxt/stgsamplelogin01 | |
public | log | table |
stgsamplelogin01=arwdDxt/stgsamplelogin01 +| |
| | |
sample_write_role=arwdDxt/stgsamplelogin01 | |
public | result | table |
stgsamplelogin01=arwdDxt/stgsamplelogin01 +| |
| | |
sample_write_role=arwdDxt/stgsamplelogin01 | |
public | tariff | table |
stgsamplelogin01=arwdDxt/stgsamplelogin01 +| |
| | |
sample_write_role=arwdDxt/stgsamplelogin01 | |
public | tariff_bk20140630 | table
| | |
(6 rows)

Regards,
Alexander Spiteri

On 15 March 2016 at 03:57, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Alexander Spiteri <alexander(at)spiteri(dot)org> writes:
> > I tried the command as you suggested but still had the same issue.
> > [postgres(at)server4 ~]$ pg_restore -p 5432 -d stgsample04
> > /var/lib/pgsql/data/dumps/stgsample01_schema.dump | psql
>
> Uh, no, you need to drop the -d switch from the pg_restore call ...
> (and the -p switch is useless as well)
>
> regards, tom lane
>