PostgreSQL throws error: "ERROR: permission denied for relation table_name"

Question

I'm trying to import a schema-dump into PostgreSQL with 'psql -U username -W dbname < migrations/schema.psql', the Schema is imported partly, but the console throws errors like "ERROR: permission denied for relation table_name" and "ERROR: relation table_name does not exist".

I've created the database like this: "createdb -O username dbname"

There are only 7 tables to import, and it breaks with just importing 3 of them.

Anybody a hint, what to do?

Answer 1

If the backup was in the "custom" format (-Fc), you could use pg_restore instead so you can tell it to not apply ownership changes:

pg_restore -U username -W --no-owner --dbname=dbname migrations/schema.psql

All the objects will created with "username" as the owner.

Barring that, try to grep out the ALTER OWNER and SET SESSION AUTHORIZATION commands into new file (or through send grep output via pipe to psql). Those commands should always be on a single line in the plain-text output format.

Answer 2

Sometimes this kind of problem is caused by case-sensitivity issues. PostgreSQL folds to lowercase all unquoted identifiers; if the tables are created with quoted names containing uppercase letters, later commands that don't quote the names may fail to find the table.

The permission errors may be related to the same thing, or it may be something else entirely. Hard to tell without seeing the failing commands.