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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
