Re: error on pg_restore - Mailing list pgsql-general

On 12/08/2015 09:00 AM, anj patnaik wrote:
> I created a user pguser and gave it all the privileges on database RIC.
>
> |CREATE USER pguser WITH PASSWORD ||'xyz123!'||;|
> |GRANT ALL PRIVILEGES ON DATABASE ||"RIC"| |to pguser;|

The above does not mean what you think it does:

http://www.postgresql.org/docs/9.4/interactive/sql-grant.html

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [
PRIVILEGES ] }
     ON DATABASE database_name [, ...]
     TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

CREATE

     For databases, allows new schemas to be created within the database.

     For schemas, allows new objects to be created within the schema. To
rename an existing object, you must own the object and have this
privilege for the containing schema.

     For tablespaces, allows tables, indexes, and temporary files to be
created within the tablespace, and allows databases to be created that
have the tablespace as their default tablespace. (Note that revoking
this privilege will not alter the placement of existing objects.)
CONNECT

     Allows the user to connect to the specified database. This
privilege is checked at connection startup (in addition to checking any
restrictions imposed by pg_hba.conf).
TEMPORARY
TEMP

     Allows temporary tables to be created while using the specified
database.


My guess is that what you want is:

CREATE USER pguser WITH SUPERUSER PASSWORD 'xyz123!';

Which is can also be written as:

CREATE ROLE pguser WITH LOGIN SUPERUSER PASSWORD 'xyz123!';

OR

You may be wanting this:

http://www.postgresql.org/docs/9.4/interactive/app-pgrestore.html

-O
--no-owner

     Do not output commands to set ownership of objects to match the
original database. By default, pg_restore issues ALTER OWNER or SET
SESSION AUTHORIZATION statements to set ownership of created schema
elements. These statements will fail unless the initial connection to
the database is made by a superuser (or the same user that owns all of
the objects in the script). With -O, any user name can be used for the
initial connection, and this user will own all the created objects.


You will need to be more specific about what you wish to achieve to get
a more specific answer.


> |
> |
> |The database RIC was created by user postgres earlier.|
> |
> |
> |Now, when I run the pg_restore I get an error:|
> |
> |
> |
> xzcat "postgres-071215200001-database" | PGPASSWORD=xyz123!
> /opt/PostgreSQL/9.4/bin/pg_restore -h onxv0097 -U pguser -d RIC -c -p
> 5432 -v
> pg_restore: connecting to database for restore
> pg_restore: dropping CONSTRAINT recorder_pkey
> pg_restore: dropping TABLE DATA recorder
> pg_restore: dropping TABLE recorder
> pg_restore: creating TABLE recorder
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 173; 1259 58915 TABLE
> recorder postgres
> pg_restore: [archiver (db)] could not execute query: ERROR:  must be
> member of role "postgres"
>      Command was: ALTER TABLE recorder OWNER TO postgres;
>
> |


--
Adrian Klaver
adrian.klaver@aklaver.com