0

In database server Postgres 9.x or Postgres 10.x can I share the same sequence from 2 user?

For example in the same database dbTest there are 2 schemas

  1. userA is schema owner schemaA with a sequenceA
  2. userB is schema owner schemaB

Can userB use sequenceA sequence? What are the permits to be given?

After solution there is the situation:

userA: select nextval('sequenceA');
userB: select nextval('schemaA.sequenceA');

If I also want for the userB: select nextval('sequenceA');

Is there a solution?

sunrelax
  • 65
  • 9

1 Answers1

2

Your usage of "schema" and "db" in the description is a bit hard to follow, but the above only works inside the same database.

The following will only work across schemas (in the same database).

As documented in the manual you need to grant the USAGE privilege for the sequence:

grant usage on sequence schema_a.sequencea to userb;

If you want to allow userb to update the current value (via setval()) you also need to grant the update privilege.