Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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!

share|improve this question
    
how are you connecting to the database? and, other command working fine such as select, delete and insert. – user234918 Feb 20 '14 at 12:43
    
I am connecting via node.js' pg module. Other commands are working fine... – JVerstry Feb 20 '14 at 14:50

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.