Skip to main content

PostgreSQL Database Security as Code (Declarative)

Managing database security through ad-hoc GRANT and REVOKE commands leads to drift, and permissions scattered across migration files make it hard to answer "who can access what?" Atlas lets you define roles, users, permissions, and row-level security policies as code - and automatically plans the changes needed to bring any database in line with the desired state.

This guide covers the declarative workflow. For versioned migrations, see the versioned security guide.

Atlas Pro Feature

Roles, users, and permissions are available only to Atlas Pro users. To use this feature, run:

atlas login

Prerequisites​

  1. Docker
  2. Atlas installed on your machine (installation guide)
  3. An Atlas Pro account (run atlas login to authenticate)

Configuring Atlas​

Start by spinning up a local PostgreSQL database using atlas tool docker:

export DATABASE_URL=$(atlas tool docker --url "docker://postgres/16/demo" --name my-db)
Cleaning up

When you're done, stop the container with atlas tool docker kill --name my-db.

Roles and permissions are excluded from inspection and schema management by default. To enable them, add a schema.mode block to your project configuration:

atlas.hcl
env "local" {
  url = getenv("DATABASE_URL")
  dev = "docker://postgres/16/dev"
  schema {
    src = "file://schema.pg.hcl"
    mode {
      roles       = true   // Inspect and manage roles and users
      permissions = true   // Inspect and manage GRANT / REVOKE
      sensitive   = ALLOW  // Allow password handling in declarative mode
    }
  }
}
  • roles = true - include roles and users in inspection and planning.
  • permissions = true - include GRANT / REVOKE statements.
  • sensitive = ALLOW - let Atlas manage passwords declaratively (they are always masked as <sensitive> in output).

Dev Database for Cloud Environments​

When targeting cloud-managed databases (Amazon RDS, Cloud SQL, etc.), the dev database must know about provider-specific roles that exist on the target but not in a vanilla PostgreSQL image. Use a docker block with a baseline script to pre-create them:

atlas.hcl
docker "postgres" "rds" {
  image = "postgres:16"
  baseline = <<-SQL
   CREATE ROLE rds_superuser;
   CREATE ROLE rds_replication;
   CREATE ROLE rds_iam;
   CREATE ROLE rdsadmin;
   GRANT rds_superuser TO postgres;
  SQL
}

env "rds" {
  url = getenv("DATABASE_URL")
  dev = docker.postgres.rds.url
  schema {
    src = "file://schema.pg.hcl"
    mode {
      roles       = true
      permissions = true
      sensitive   = ALLOW
    }
  }
}

If you prefer to use your own dev database instead of Docker, use a dev block:

atlas.hcl
dev "postgres" "rds" {
  url = getenv("DEV_URL")
  baseline = <<-SQL
   CREATE ROLE rds_superuser;
   CREATE ROLE rds_replication;
   CREATE ROLE rds_iam;
   CREATE ROLE rdsadmin;
   GRANT rds_superuser TO postgres;
  SQL
}

Defining Roles and Users​

Let's model a SaaS order management application with three tiers of access:

RolePurpose
app_readonlyRead-only access for reporting dashboards
app_writerRead-write access for the application backend, inherits from app_readonly
app_adminFull administrative access, inherits from app_writer

Two login users connect to the database at runtime:

UserRole membership
dashboard_userapp_readonly
api_userapp_writer
schema.pg.hcl
variable "api_password" {
  type = string
}

variable "dashboard_password" {
  type = string
}

role "app_readonly" {
  comment = "Read-only access for reporting"
}

role "app_writer" {
  comment   = "Read-write access for the application"
  member_of = [role.app_readonly]
}

role "app_admin" {
  create_role = true
  comment     = "Administrative access"
  member_of   = [role.app_writer]
}

user "dashboard_user" {
  password   = var.dashboard_password
  conn_limit = 5
  comment    = "Reporting dashboard service account"
  member_of  = [role.app_readonly]
}

user "api_user" {
  password   = var.api_password
  conn_limit = 20
  comment    = "Application API service account"
  member_of  = [role.app_writer]
}

  • Roles vs users - a user block implicitly has login = true. Roles cannot log in by default.

  • Inheritance - member_of creates a role hierarchy. app_writer inherits all privileges of app_readonly, and app_admin inherits everything from app_writer.

  • Passwords - defined through input variables, never hardcoded. Inject real values at runtime via environment variables, or use the runtimevar data source to read passwords directly from AWS Secrets Manager, GCP Secret Manager, HashiCorp Vault, AWS KMS, or any other supported secret store:

    atlas.hcl
    data "runtimevar" "api_pass" {
      url = "awssecretsmanager://api-db-password?region=us-east-1"
    }

    Then reference it in your schema: password = data.runtimevar.api_pass.

  • External roles - cloud-managed databases (Amazon RDS, Google Cloud SQL, Azure) include provider-specific roles like rds_superuser or cloudsqlsuperuser. PostgreSQL also ships with predefined roles like pg_read_all_data and pg_monitor. Mark these roles as external = true so Atlas knows they already exist and will only generate the GRANT, never a CREATE ROLE:

schema.pg.hcl
// PostgreSQL predefined role
role "pg_read_all_data" {
  external = true
}

// Cloud provider role (e.g., Amazon RDS)
role "rds_superuser" {
  external = true
}

role "app_readonly" {
  comment   = "Read-only access for reporting"
  member_of = [role.pg_read_all_data]
}

Defining Permissions​

With roles in place, grant privileges at the schema, table, and column level. First, define the tables that permissions will reference:

schema.pg.hcl
table "users" {
  schema = schema.public
  column "id" {
    type = serial
    null = false
  }
  column "name" {
    type = character_varying
    null = false
  }
  column "email" {
    type = character_varying
    null = false
  }
  primary_key {
    columns = [column.id]
  }
}

table "products" {
  schema = schema.public
  column "id" {
    type = serial
    null = false
  }
  column "name" {
    type = character_varying
    null = false
  }
  column "price" {
    type = numeric
    null = false
  }
  primary_key {
    columns = [column.id]
  }
}

table "orders" {
  schema = schema.public
  column "id" {
    type = serial
    null = false
  }
  column "user_id" {
    type = integer
    null = false
  }
  column "product_id" {
    type = integer
    null = false
  }
  column "tenant_id" {
    type = integer
    null = false
  }
  column "total" {
    type = numeric
    null = false
  }
  primary_key {
    columns = [column.id]
  }
  foreign_key "fk_order_user" {
    columns     = [column.user_id]
    ref_columns = [table.users.column.id]
  }
  foreign_key "fk_order_product" {
    columns     = [column.product_id]
    ref_columns = [table.products.column.id]
  }
}

schema "public" {
  comment = "standard public schema"
}

Next, add the permissions:

schema.pg.hcl
// Admin gets ALL on the schema with grant option
permission {
  to         = role.app_admin
  for        = schema.public
  privileges = [ALL]
  grantable  = true
}

// Read-only: SELECT on all tables
permission {
  for_each   = [table.orders, table.products, table.users]
  for        = each.value
  to         = role.app_readonly
  privileges = [SELECT]
}

// Writer: SELECT, INSERT, UPDATE on orders and products
permission {
  for_each   = [table.orders, table.products]
  for        = each.value
  to         = role.app_writer
  privileges = [SELECT, INSERT, UPDATE]
}

// Column-level: api_user can only UPDATE the email column
permission {
  to         = user.api_user
  for        = table.users.column.email
  privileges = [UPDATE]
}

for_each keeps permissions DRY - define the grant once, and Atlas expands it for every table at plan time. grantable = true maps to SQL's WITH GRANT OPTION, allowing the grantee to re-grant the same privileges.

Row-Level Security​

Row-level security (RLS) restricts which rows a role can see or modify. In a multi-tenant application this is critical - each tenant must only access its own data. Enable RLS on the table and define a policy:

schema.pg.hcl
table "orders" {
  schema = schema.public
  // ... columns and keys from above ...
  row_security {
    enabled  = true  // ENABLE ROW LEVEL SECURITY
    enforced = true  // FORCE ROW LEVEL SECURITY
  }
}

policy "tenant_isolation" {
  on    = table.orders
  for   = ALL
  to    = ["app_writer"]
  using = "(tenant_id = current_setting('app.current_tenant')::integer)"
  check = "(tenant_id = current_setting('app.current_tenant')::integer)"
}
  • enabled = true activates RLS on the table. Without it, policies are defined but not enforced.
  • enforced = true applies RLS even to the table owner (maps to FORCE ROW LEVEL SECURITY).
  • using filters existing rows (reads). check validates new or updated rows (writes).
info

For using RLS with ORMs, see the dedicated guides for GORM, Prisma, SQLAlchemy, and Sequelize.

Applying Changes​

Run atlas schema apply to diff the desired state against the live database and execute the changes:

atlas schema apply --env local \
 --var "api_password=s3cret" \
 --var "dashboard_password=r3port"

Atlas produces a plan showing every role, grant, table, and policy it will create:

Planning migration statements (21 in total):

 -- create role "app_readonly":
   -> CREATE ROLE "app_readonly";
 -- create role "app_writer":
   -> CREATE ROLE "app_writer";
 -- grant role "app_readonly" to "app_writer":
   -> GRANT "app_readonly" TO "app_writer";
 -- create role "app_admin":
   -> CREATE ROLE "app_admin" WITH CREATEROLE;
 -- grant role "app_writer" to "app_admin":
   -> GRANT "app_writer" TO "app_admin";
 -- grant on schema "public" to "app_admin":
   -> GRANT CREATE, USAGE ON SCHEMA "public" TO "app_admin" WITH GRANT OPTION;
 -- create user "api_user":
 -- atlas:sensitive:
   -> CREATE ROLE "api_user" WITH LOGIN CONNECTION LIMIT 20 PASSWORD (sensitive);
 -- grant role "app_writer" to "api_user":
   -> GRANT "app_writer" TO "api_user";
 -- create user "dashboard_user":
 -- atlas:sensitive:
   -> CREATE ROLE "dashboard_user" WITH LOGIN CONNECTION LIMIT 5 PASSWORD (sensitive);
 -- grant role "app_readonly" to "dashboard_user":
   -> GRANT "app_readonly" TO "dashboard_user";
 -- create "products" table:
   -> CREATE TABLE "public"."products" ( ... );
 -- grant on table "products" to "app_readonly":
   -> GRANT SELECT ON TABLE "public"."products" TO "app_readonly";
 -- grant on table "products" to "app_writer":
   -> GRANT INSERT, SELECT, UPDATE ON TABLE "public"."products" TO "app_writer";
 -- create "users" table:
   -> CREATE TABLE "public"."users" ( ... );
 -- grant on table "users" to "app_readonly":
   -> GRANT SELECT ON TABLE "public"."users" TO "app_readonly";
 -- grant on column "email" of table "users" to "api_user":
   -> GRANT UPDATE ("email") ON TABLE "public"."users" TO "api_user";
 -- create "orders" table:
   -> CREATE TABLE "public"."orders" ( ... );
 -- enable and enforce row-level security for "orders" table:
   -> ALTER TABLE "public"."orders" ENABLE ROW LEVEL SECURITY, FORCE ROW LEVEL SECURITY;
 -- grant on table "orders" to "app_readonly":
   -> GRANT SELECT ON TABLE "public"."orders" TO "app_readonly";
 -- grant on table "orders" to "app_writer":
   -> GRANT INSERT, SELECT, UPDATE ON TABLE "public"."orders" TO "app_writer";
 -- create policy "tenant_isolation":
   -> CREATE POLICY "tenant_isolation" ON "public"."orders" AS PERMISSIVE FOR ALL TO "app_writer"
       USING (...) WITH CHECK (...);

-------------------------------------------

? Approve or abort the plan:
  ▸ Approve and apply
    Abort

After approving, verify with atlas schema inspect:

atlas schema inspect --env local

The output reflects every role, user, permission, and policy - a single, readable snapshot of your entire security posture. Run this on any environment to confirm it matches the desired state.

Next Steps​

Have questions? Feedback? Find our team on our Discord server or schedule a demo.