PostgreSQL: Permissions for Sequences

Atlas now inspects, diffs, and migrates GRANT and REVOKE ... ON SEQUENCE for PostgreSQL, including the sequences that back serial columns.

Atlas permission management for PostgreSQL previously covered tables, views, columns, functions, procedures, and materialized views. This release extends GRANT and REVOKE support to sequences, including the sequences that back serial and bigserial columns.

Sequence Permissions

Use the permission block with for = sequence.<name> to grant USAGE, SELECT, or UPDATE on a sequence. Grantees can be PUBLIC, a role, or a user:

sequence "s1" {
  schema = schema.public
}
permission {
  to         = PUBLIC
  for        = sequence.s1
  privileges = [SELECT, USAGE]
}

WITH GRANT OPTION

Set grantable = true to allow the grantee to re-grant the privilege to other roles. Atlas generates the WITH GRANT OPTION clause automatically:

permission {
  to         = role.app
  for        = sequence.s2
  privileges = [USAGE]
  grantable  = true
}

Generated SQL

Privileges for a grantee are merged into a single GRANT or REVOKE, emitted in sorted order. Modifying a grant emits statements on the existing sequence, with no CREATE or DROP:

-- Grant on a sequence
GRANT SELECT, USAGE ON SEQUENCE "public"."s1" TO PUBLIC;

-- Grant with grant option (grantable = true)
GRANT USAGE ON SEQUENCE "public"."s2" TO "app" WITH GRANT OPTION;

-- Revoke a privilege from a grantee
REVOKE USAGE ON SEQUENCE "public"."s1" FROM "reader";

Grants on Serial Sequences

The sequence implicitly created behind a serial or bigserial column is normally hidden and managed as part of the column. If that implicit sequence carries a grant, Atlas stops treating it as implicit: it surfaces the sequence as an explicitly managed standalone object and demotes the owning column from bigserial to a plain bigint with a nextval(...) default, so both the sequence's DDL and its grants are managed. A serial column without any grant is unchanged, it stays bigserial and its sequence remains implicit.

Given this desired schema:

CREATE SEQUENCE s1;
GRANT USAGE, SELECT ON SEQUENCE s1 TO PUBLIC;

CREATE TABLE plain (id bigserial PRIMARY KEY);

CREATE TABLE users (id bigserial PRIMARY KEY);
GRANT USAGE ON SEQUENCE users_id_seq TO PUBLIC;

atlas migrate diff promotes the granted users_id_seq to an explicit sequence and demotes users.id to bigint, while plain.id (no grant) stays bigserial:

-- Create sequence "s1"
CREATE SEQUENCE "public"."s1";
-- Grant on sequence "s1" to "PUBLIC"
GRANT SELECT, USAGE ON SEQUENCE "public"."s1" TO PUBLIC;
-- Create sequence "users_id_seq"
CREATE SEQUENCE "public"."users_id_seq";
-- Grant on sequence "users_id_seq" to "PUBLIC"
GRANT USAGE ON SEQUENCE "public"."users_id_seq" TO PUBLIC;
-- Create "users" table
CREATE TABLE "public"."users" ("id" bigint NOT NULL DEFAULT nextval('public.users_id_seq'), PRIMARY KEY ("id"));
-- Modify sequence "users_id_seq"
ALTER SEQUENCE "public"."users_id_seq" OWNED BY "public"."users"."id";
-- Create "plain" table
CREATE TABLE "public"."plain" ("id" bigserial NOT NULL, PRIMARY KEY ("id"));

Inspecting the same schema as HCL surfaces the sequence, its owner, and the grant as a permission block:

table "users" {
  schema = schema.public
  column "id" {
    null    = false
    type    = bigint
    default = sql("nextval('public.users_id_seq')")
  }
  primary_key { columns = [column.id] }
}
sequence "users_id_seq" {
  schema = schema.public
  owner  = table.users.column.id
}
permission {
  to         = PUBLIC
  for        = sequence.users_id_seq
  privileges = [USAGE]
}

Setup

To manage permissions, enable the permissions and roles schema modes in your project configuration:

env "local" {
  dev = "docker://postgres/18/dev"
  schema {
    src = "file://schema.sql"
    mode {
      permissions = true
      roles       = true
    }
  }
  migration {
    dir = "file://migrations"
  }
}

Atlas handles the full lifecycle: inspecting existing ACLs from the database, computing the diff against your desired state, and generating GRANT/REVOKE statements with correct dependency ordering. This works with both the declarative workflow (atlas schema apply) and versioned migrations (atlas migrate diff).