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
