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@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@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@sss.pgh.pa.us> wrote:

> Alexander Spiteri <alexander@spiteri.org> writes:
> > I tried the command as you suggested but still had the same issue.
> > [postgres@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
>