Posgra

PostgreSQL

PostgreSQL roles, grants, and database privileges as code

Manages: PostgreSQL roles and grants
Language: Ruby
Package: posgra
CLI: posgra
Config file: pg_grants.rb

The codenize-tools/posgra repository is archived on GitHub (last activity 2018); the gem still works but is no longer developed.

pg_grants.rb

role "bob" do
  schema "main" do
    on "microposts" do
      grant "DELETE", grantable: true
      grant "INSERT"
      grant "SELECT"
      grant "UPDATE"
    end
    on "microposts_id_seq", expired: '2014/10/07' do
      grant "SELECT"
      grant "UPDATE"
    end
    on /^user/ do
      grant "SELECT"
    end
  end
end

In short

Posgra is a Ruby command-line tool that manages PostgreSQL roles, schema grants, and database grants as code. It uses three subcommands to export the current state, review edits in Git, and apply them idempotently. Every apply supports a dry-run that shows the exact statements first.

What is Posgra?

Posgra is a command-line tool that manages PostgreSQL permissions as code. You describe users, groups, schema grants, and database grants in a Ruby DSL. Posgra then runs the CREATE ROLE, GRANT, and REVOKE statements needed to match it. The work splits into three subcommands: posgra role, posgra grant, and posgra database.

Postgres permissions are easy to get wrong at scale. Privileges live per schema, per object, and per database. The psql \dp output is not something you can review in a pull request. Posgra turns that state into files you can diff. Access changes then move through review like any other config kept in version control.

It fits teams running self-managed PostgreSQL or Amazon Redshift. They get the same declarative, idempotent loop that Gratan gives MySQL grants.

Key facts

Manages PostgreSQL roles, schema grants, and database grants as code.
Splits work into three subcommands: role, grant, and database.
Also works with Amazon Redshift over the Postgres protocol.
Every apply has a dry-run, and applying twice makes no changes.
Pairs well with Miam for AWS IAM access as code.
The repository is archived (last active 2018); the gem still installs.

Illustration: PostgreSQL roles and grants managed as code with Posgra

Getting started

Install and run Posgra

Install

Install the posgra gem (Ruby required).

$ gem install posgra

Export current state

Pull the live PostgreSQL configuration into a pg_grants.rb.

$ posgra grant export pg_grants.rb

Dry-run, then apply

Preview the diff, then apply the change for real.

$ posgra grant apply --dry-run pg_grants.rb

Posgra runs the export, review, apply loop once per resource type. Export the current cluster with posgra role export, posgra grant export, and posgra database export. Commit those files so your existing roles and privileges become the baseline.

When access needs to change, edit the right file and preview the plan. posgra grant apply --dry-run prints the GRANT and REVOKE statements without touching the cluster. Once the diff is approved, run posgra grant apply (and the matching role or database apply). Applies are idempotent, so re-running is safe. A scheduled dry-run against production is an easy way to spot privilege drift.

Pro tip

Adopt Posgra one scope at a time. Start by managing schema grants with posgra grant, get comfortable with the dry-run, then bring roles and database grants under code later.

Capabilities

What Posgra can do

Roles, grants, and database grants

Three subcommands cover the whole surface. <code>posgra role</code> manages users and groups, <code>posgra grant</code> manages per-schema object privileges, and <code>posgra database</code> manages grants like <code>CONNECT</code> and <code>CREATE</code>.

Idempotent apply with dry-run

Every apply subcommand takes <code>--dry-run</code> and prints the exact statements first. Applying the same file twice makes no changes.

Regex object matching

Grant blocks accept regular expressions such as <code>on /^user/</code>. One rule can cover every table or sequence that matches a naming convention.

Grant expiration

Grants can carry an <code>expired:</code> date. Temporary access is explicit in review instead of depending on someone remembering to revoke it.

Templates

<code>template</code> and <code>include_template</code> define a standard privilege set once. You stamp it across roles and objects with context parameters.

Amazon Redshift support

Redshift speaks the Postgres protocol, so Posgra works there too. Set <code>POSGRA_DEFAULT_ACL_PRIVS</code> (for example <code>arwdRxt</code>) to match its default ACL privileges.

What each Posgra subcommand manages

Subcommand	Scope	Example privileges
`posgra role`	Users and groups	role creation, group membership
`posgra grant`	Per-schema objects	`SELECT`, `INSERT`, `UPDATE`
`posgra database`	Whole databases	`CONNECT`, `CREATE`, `TEMP`

In practice

When teams reach for Posgra

Access reviews on a schedule

With the role and grant files in Git, quarterly access reviews mean reading a file, not querying <code>pg_catalog</code>. Auditors get history free from the commit log.

Consistent grants across services

Teams with many microservice roles define one privilege template and include it per service. New services get the same least-privilege grants, checked by dry-run.

Managing Redshift privileges

Redshift clusters collect grants just like Postgres. Posgra exports and applies them with the same DSL, using <code>POSGRA_DEFAULT_ACL_PRIVS</code> for Redshift's ACL defaults.

Safe cleanup of legacy roles

Export the current state, delete a suspect role or grant from the file, and let <code>--dry-run</code> show exactly what would be revoked before you commit.

FAQ

Posgra questions, answered

Is Posgra still maintained?

No. The posgra repository is archived on GitHub and last saw activity in 2018. The gem still installs and works against Postgres-compatible databases. The Terraform PostgreSQL provider is the common maintained alternative.

How does Posgra connect to the database?

Through flags (-h, -p, -d, -U, -P) or the POSGRA_DB_* environment variables. The connecting user needs enough rights to read catalogs and issue GRANT and REVOKE.

Why are roles, grants, and database grants separate files?

Each subcommand manages a distinct scope. Separate files keep diffs small and let you adopt Posgra step by step. You can manage grants as code before touching role definitions.

What happens to grants that are not in the DSL?

Posgra is declarative, so privileges in the cluster but absent from the file get revoked on apply. Run apply with --dry-run first, especially on a fresh export where system objects may appear.

Does Posgra work with Amazon Redshift?

Yes. The README documents it. Export POSGRA_DEFAULT_ACL_PRIVS=arwdRxt before running the grant export so the tool reads Redshift's default ACL privileges correctly.