Postgres sequence usage

Question

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.

Answer 1

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 [email protected] might be in order ...

Answer 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;