Search code examples
postgresqldatabase-permissions

Postgres users, roles and permissions

I have recently moved hosting environments and have had to backup the old Postgres database and copied it to the new server.

On the new server, I imported the database and now I receive an error django.db.utils.ProgrammingError: permission denied for relation django_site when accessing the site.

I believe this is because of owner on the tables if I do a \l I can see the owner is the correct user userW2E

      Name      |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
----------------+----------+----------+------------+------------+-----------------------
 hypersrvdjango | userW2E  | UTF8     | en_US.utf8 | en_US.utf8 |

However if I do \dt

List of relations
 Schema |               Name               | Type  |  Owner

--------+----------------------------------+-------+----------
 public | account_registeremailaddress     | table | postgres

So I am trying

ALTER TABLE account_registeremailaddress OWNER TO userW2E;

And get error

ERROR:  role "userw2e" does not exist

Im new to postgres and a bit unsure on how to get my tables owned by user userw2e

Can anyone help?

Solution

  • userW2E is identifier here, in case of mixed case, use double quotes:

    ALTER TABLE account_registeremailaddress OWNER TO "userW2E";

    https://www.postgresql.org/docs/current/static/tutorial-table.html

    SQL is case insensitive about key words and identifiers, except when identifiers are double-quoted to preserve the case

    emphasis mine

    Also https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS