I have granted priviledges to a user for testing in PostGreSql:
GRANT USAGE ON SCHEMA "MySchema" TO "Tester";
GRANT ALL ON ALL TABLES IN SCHEMA "MySchema" TO "Tester";
GRANT ALL ON ALL SEQUENCES IN SCHEMA "MySchema" TO "Tester";
Yet, when I run the following statement;
TRUNCATE TABLE "MySchema"."USER_ACCOUNT" RESTART IDENTITY;
I get the following error message:
[error: must be owner of relation USER_ACCOUNT_ID_seq]
What else do I need to grant to my Tester user, and how? Thanks!
From http://www.postgresql.org/docs/9.5/static/sql-altersequence.html
You must own the sequence to use ALTER SEQUENCE. To change a sequence's schema, you must also have CREATE privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the sequence's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the sequence. However, a superuser can alter ownership of any sequence anyway.)
Restarting the sequence on table truncation is essentially the same as altering the sequence in a separate statement
TRUNCATE TABLE "MySchema"."USER_ACCOUNT";
ALTER SEQUENCE my_seq RESTART;
So, the above restriction applies to your original TRUNCATE with RESTART statement. You must be the owner of that sequence or be a super user.
You can GRANT the sequence owner's role to the role that's going to execute your statement by doing
GRANT roleA TO roleB;
assuming that roleA owns the sequence you are trying to restart.
Once you are done with all the TRUNCATE/ALTER SEQUENCE statements you can REVOKE the sequence's owning role from roleB
REVOKE roleA FROM roleB;
