PostgreSQL: Permissions for Functions, Procedures & Materialized Views

Atlas now supports GRANT and REVOKE for PostgreSQL functions, procedures, and materialized views, including column-level grants and WITH GRANT OPTION.

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

Function Permissions

Use the permission block with for = function.<name> to grant EXECUTE on a function:

role "app" {
}
function "compute_score" {
  schema = schema.public
  lang   = SQL
  arg "x" {
    type = integer
  }
  return = integer
  as     = "SELECT x * 2"
}
permission {
  to         = role.app
  for        = function.compute_score
  privileges = [EXECUTE]
}

Procedure Permissions

Procedures work the same way. Reference them with for = procedure.<name>:

procedure "refresh_cache" {
  schema = schema.public
  lang   = SQL
  as     = "REFRESH MATERIALIZED VIEW cache"
}
permission {
  to         = role.app
  for        = procedure.refresh_cache
  privileges = [EXECUTE]
}

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        = function.compute_score
  privileges = [EXECUTE]
  grantable  = true
}

Generated SQL for Routines

Atlas generates GRANT EXECUTE ON FUNCTION and GRANT EXECUTE ON PROCEDURE statements, including the full argument signature for overloaded routines. All statements are reversible:

-- Grant execute on a function
GRANT EXECUTE ON FUNCTION "public"."compute_score"(integer) TO "app";
-- Revoke execute from a function
REVOKE EXECUTE ON FUNCTION "public"."compute_score"(integer) FROM "app";

-- Grant with admin option (grantable = true)
GRANT EXECUTE ON FUNCTION "public"."compute_score"(integer) TO "app" WITH GRANT OPTION;

-- Grant execute on a procedure
GRANT EXECUTE ON PROCEDURE "public"."refresh_cache"() TO "app";
-- Revoke execute from a procedure
REVOKE EXECUTE ON PROCEDURE "public"."refresh_cache"() FROM "app";

Materialized View Permissions

Materialized views now support both table-level and column-level grants. Reference them with for = materialized.<name>:

materialized "user_stats" {
  schema = schema.public
  column "id" {
    type = integer
  }
  column "total" {
    type = integer
  }
  as = "SELECT id, count(*) AS total FROM users GROUP BY id"
}
permission {
  to         = role.reader
  for        = materialized.user_stats
  privileges = [SELECT]
}

Column-Level Grants

For fine-grained access control, grant privileges on individual columns of a materialized view using for = materialized.<name>.column.<col>:

permission {
  to         = role.reader
  for        = materialized.user_stats.column.id
  privileges = [SELECT]
}

Generated SQL for Materialized Views

-- Grant on materialized view
GRANT SELECT ON "public"."user_stats" TO "reader";
GRANT DELETE, INSERT, SELECT, UPDATE ON "public"."user_stats" TO "writer";

-- Column-level grant on materialized view
GRANT UPDATE ("id") ON TABLE "public"."user_stats" TO "reader";

-- Revoke from materialized view
REVOKE SELECT ON "public"."user_stats" FROM "reader";

Setup

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

env "local" {
  schema {
    src = "file://schema.pg.hcl"
    mode {
      permissions = true
      roles       = true
    }
  }
}

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 (e.g., revoking permissions before dropping a role). This works with both the declarative workflow (atlas schema apply) and versioned migrations (atlas migrate diff).