Create & Alter Schema Permissions for a Login role in Postgresql database

Question

I have a Postgresql database with 3 Schemas (2 user defined and 1 public).

I have script that takes backup of the user defined schema's and then drops the schema's.

Now after doing some operations I want to restore the user defined schemas from the schema backup(schema_backup.out).

When I execute

cat schema_backup.out | psql xx_db_name_xx

I am getting the following error message

ERROR:  permission denied for database xx_db_name_xx
ERROR:  schema "aaaa" does not exist 
ERROR:  permission denied for database xx_db_name_xx
ERROR:  schema "bbbb" does not exist 
ERROR:  must be owner of database xx_db_name_xx

When I execute the same command as Admin user its executing just fine. But, when I execute as a 'xxxxxx' user its failing.

Can someone guide me, what permissions/rights should be added to 'xxxxxx' to restore the schema?

Answer 1

Postgres already answered this for you: ERROR:must be owner of databasexx_db_name_xx.

You need to ALTER the DATABASE and SET OWNER to be the user you're trying to make schema changes as.
(See the Postgres manual for information on the ALTER DATABASE SQL Statement)

---

Generally it's easier to restore databases as the Postgres superuser - This avoids any permissions issues, and normally ownership/permissions are restored when you restore the database.