CODEWITHSUNDEEP
postgresql
rohanagarwal
rohanagarwal

Reputation: 834

Postgres permissions on database created from dump file

I want to create a database ABC. I want to load data into ABC using a dump file say ABC_DUMP. I want an user ABC_USER to have all access on database ABC including create, select, alter, update. I login to psql using:

postgres@ubuntu:~$ psql
postgres=# CREATE DATABASE ABC;
postgres=# GRANT ALL PRIVELEGES ON DATABASE ABC TO ABC_USER;
postgres=# \q
postgres@ubuntu:~$ psql -h localhost ABC -U ABC_USER < ABC_DUMP
Password for user ABC_USER xxxxxx
CREATE FUNCTION
ERROR:  must be member of role "postgres"
CREATE FUNCTION
ERROR:  must be member of role "postgres"
CREATE FUNCTION
ERROR:  must be member of role "postgres"
CREATE FUNCTION
ERROR:  must be member of role "postgres"
CREATE AGGREGATE
ERROR:  must be member of role "postgres"
SET
SET
CREATE TABLE
ERROR:  must be member of role "postgres"
CREATE TABLE
ERROR:  must be member of role "postgres"
CREATE SEQUENCE
ERROR:  must be member of role "postgres"
ALTER SEQUENCE
CREATE SEQUENCE
ERROR:  must be member of role "postgres"
...

How can I avoid these errors? I have tried giving all privileges on schema public to user ABC_USER but still I am getting errors.

Role name : ABC_USER, Role Attributes list - Create DB, Create role
Role name : Postgres, Role Attributes list - Superuser, Create role, Create DB, Replication, Bypass RLS

Using postgres version 9.5.6

Upvotes: 1

Views: 2282

Answers (1)

rohanagarwal
rohanagarwal

Reputation: 834

The problem was with the dump file. It had owner set, so I can't dump the data using user ABC_USER. I created another dump file but this time using --no-owner flag and used that dump file and it worked.

pg_dump ABC --no-owner > ABC_DUMP

Upvotes: 5

Related Questions