Take the 2-minute tour ×
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. It's 100% free, no registration required.

I have an Postgres database with two users: dev and junit. Junit and dev objects are dropped and created regularly.

They both have the same objects unshared for tables, however, sequences are shared, and dev is the owner. I just use normal create statements.

I want to grant all to junit for the sequence but this fails with:

dev=> grant all on sequence serial to junit;
WARNING:  no privileges were granted for "serial"
GRANT

After this junit has no select rights on the sequence, how can I fix this?

I also would like to know whether there are recipes for this kind of setups, where I can use the same DDL for the two different users without modifying the script? I've been looking into schemas but I haven't found the solution so far.

share|improve this question
    
We generally prefer to have two individual questions for the two decisively distinct questions you presented. The second part shouldn't be here. You can always link to a previous question for context. –  Erwin Brandstetter Nov 22 '13 at 2:01

2 Answers 2

SERIAL is an alias datatype for an INTEGER type column with a defult coming from a sequence. If you defined your table as having a SERIAL type column, then you need to get the actual name of the sequence from the table.

Have a look at the output of \d your_table and retry your GRANT command with the proper sequence name. If you did name your sequence "serial" (which would be unwise), then you should double-quote "serial" in your GRANT command.

Regarding your other question, you can collect the object definitions into one (or more) file. Create a schema for dev and another for junit, and then create the objects with an other script like

SET ROLE TO dev;
SET search_path TO dev;

\i object_definitions.sql

SET ROLE TO junit;
SET search_path TO junit;

\i object_definitions.sql

RESET ROLE;
share|improve this answer
1  
@dezso, you could have created a new answer to garner the points for yourself (I will +1 if you do). Either way, thank you for properly finishing my answer, that was good of you. –  bma Nov 21 '13 at 19:21
1  
I would just repeat yours anyway, this is more economical :) –  dezso Nov 21 '13 at 20:20

The warning

WARNING:  no privileges were granted for "serial"

indicates that the granting role doesn't have the necessary privilege to grant privileges on the object in question.

  • Either execute the GRANT command as the role owning the sequence.
  • Or do the same as superuser (default superuser is postgres).
  • Or GRANT the necessary privilege to the role dev so that this role can in turn grant it to others:
GRANT ALL ON SEQUENCE serial TO dev WITH GRANT OPTION;

The name serial for a sequence isn't particularly wise, but since serial is not a name of a base type and not a reserved word, it wouldn't cause any immediate trouble.

Aside:
Unfortunately the manual uses the name serial in the CREATE SEQUENCE and ALTER SEQUENCE examples. I feel like a suggestion to pgsql-docs@postgresql.org might be in order ...

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.