sqlauthz - Declarative permissions management for PostgreSQL

sqlauthz allows you to manage your permissions in PostgresSQL in a declarative way using simple rules written in the Polar language. Polar is a language designed by Oso specifically for writing authorization rules, so its syntax is a great fit for declaring permissions. As an example of what this might look like, see the examples below:

# Give `user1` the `USAGE` permission on schema `myschema`;
allow("user1", "usage", "myschema");

# Allow `user1` to run `SELECT` queries on the columns "id" and "othercolumn"
# and rows where the "owner" column is equal to "owner1" in table "myschema.mytable"
allow("user1", action, resource)
    if action in ["select"]
    and resource == "myschema.mytable"
    and resource.col in ["id", "othercolumn"]
    and resource.row.owner == "owner1";

# Give `user2`, `user3`, and `user4` `USAGE` on the `test` schema
# And all permissions on all tables within the `test` schema
allow(actor, _, resource)
    if isInTestGroup(actor)
    and resource.schema == "test";
    
isInTestGroup(user) if user in ["user2", "user3"];
isInTestGroup("user4");

Currently sqlauthz support PostgreSQL as a backend, and it allows you to define:

• Schema permissions
• Table permissions including column and row-level security
• View permissions
• Function and procedure permissions
• Sequence permissions

To get started, check out the Table of Contents below.

Table of Contents

• Installation
• Compatibility
• CLI
  • CLI Configuration
  • User revoke strategies
• Using sqlauthz as a library
• Writing rules
  • Using SQL functions in row-level security clauses
  • Available constants
  • Permissions that depend on one another
• Incremental Adoption
• Examples
  • A complete example
  • Grant a user all permissions on all schemas
  • Grant a group of users all permissions on a schema
  • Grant a user read-only access on a schema
  • Grant a user read permissions on a limited set of rows and columns in a table
• Integrating into a production application
  • Integrating into CI/CD
  • Integrating into tests
• Considerations when using row-level security
• Usage with VSCode
• Oso Library Deprecation
• Motivation
• Limitations
• Support and Feature Requests

Installation

sqlauthz is distributed as an npm package, so you can install it via your favorite package manager:

npm install --save-dev sqlauthz
# or, if using pnpm
pnpm add -D sqlauthz

You may not want to install it as a development dependency if you plan on using it as a library within your application.

Compatilibity

sqlauthz has automated testing in place and is compatible with node 18 and 20, and PostgreSQL versions 12-16. It may be compatible with older versions of either, but it has not been tested.

CLI

Most users will probably want to use sqlauthz via the command line. There are three ways to configure sqlauthz via CLI:

• command line arguments
• environment variables
• under the sqlauthz key in package.json

In order to invoke the sqlauthz CLI, just invoke the sqlauthz command:

npm run sqlauthz
# or
npx sqlauthz
# or, if using pnpm
pnpm sqlauthz

CLI Configuration

The configuration options for sqlauthz can be found in the table below. Note that the argument name in the sqlauthz key of package.json is given first, then the CLI argument, then the environment variable:

| Name | Required | Default | Description | | ---- | -------- | ------- | ----------- | | databaseUrl<br/>-d, --database-url<br/>SQLAUTHZ_DATABASE_URL | Yes | | Database URL to connect to for reading the current database object and executing queries (if one of the dryRun arguments is passed, it will only be used for reading the current database objects). Note that you can pass this in the form env:<name>, which will read the value from a specified environment variable, for example env:MY_DATABASE_URL will read the value from the MY_DATABASE_URL environment variable. | | rules<br/>-r, --rules<br/>SQLAUTHZ_RULES | No | ['sqlauthz.polar'] | Path(s) to .polar files containing rules. Globs (e.g. sqlauthz/*.polar) are supported. Note that only a single path is supported when setting this argument via environment variable | | revokeReferenced<br/>--revoke-referenced<br/>SQLAUTHZ_REVOKE_REFERENCED | No | true | Use the referenced user revoke strategy. This is the default strategy. See User revoke strategies for details. Conflicts with revokeAll and revokeUsers. Note that if setting this via environment variable, the value must be true. | | revokeAll<br/>--revoke-all<br/>SQLAUTHZ_REVOKE_ALL | No | false | Use the all user revoke strategy. See User revoke strategies for details. Conflicts with revokeReferenced and revokeUsers. Note that if setting this via environment variable, the value must be true. | | revokeUsers<br/>--revoke-users<br/>SQLAUTHZ_REVOKE_USERS | No | false | Use the users revoke strategy, revoking permissions from a list of users explicitly. See User revoke strategies for details. Conflicts with revokeReferenced and revokeAll. Note that if setting this via environment variable, only a single value can be passed. | | allowAnyActor<br/>--allow-any-actor<br/>SQLAUTHZ_ALLOW_ANY_ACTOR | No | false | Allow rules that do not put any limitations on the actor, so they apply to all users. This is potentially dangerous, particularly when used with revokeReferenced (the default), so it is disabled by default. This argument allows these rules (but make sure that you know what you're doing!). | | var<br/>--var<br/>SQLAUTHZ_VAR | No | <none> | Inject variables into scope that can be utilized by your rules files. The syntax for variables injected via command line is <name>=<value>. The CLI will attempt to parse <value> a JSON string, and if that fails it will just be interpreted as a string. Within your rules files, variables can be access with var.<name>. This can be used to parametrize your rules files, and separate your configuration from your permissions logic. Also see --var-file for more flexibility. | | varFile<br/>--var-file<br/>SQLAUTHZ_VAR_FILE | No | <none> | Specify script(s) or JSON file(s) that will be loaded, and their exports will be used to inject variables into your rules files. Glob paths are supported e.g. *.js. The file(s) must have .js or .json extensions. Within your rules files, variables can be access with var.<name>. --var will take priority over variables loaded from file(s) loaded with this argument. This can be used to separate your permissions logic from your configuration. For an example, see the complete example below. | | dryRun<br/>--dry-run<br/>SQLAUTHZ_DRY_RUN | No | false | Print the full SQL query that would be executed instead of executing it. Note that if setting this via environment variable, the value must be true. This conflicts with dryRunShort | | dryRunShort<br/>--dry-run-short<br/>SQLAUTHZ_DRY_RUN_SHORT | No | false | Print an abbreviated SQL query, only containing the GRANT queries that will be run, instead of executing anything. Note that if setting this via environment variable, the value must be true. This conflicts with dryRun | | debug<br/>--debug<br/>SQLAUTHZ_DEBUG | No | false | Print more detailed error information for debugging compilation failures. Note that if setting this via environment variable, the value must be true. |

NOTE: Environment variables will be loaded from your .env file and used as arguments where applicable. The order of precedence for configuration arguments is:

• Command line args
• Environment variables
• The sqlauthz key in package.json You can disable the loading of environment variables from you .env file by setting the NO_DOTENV environment variable to any truthy value.

User revoke strategies

The intent of sqlauthz is the after you apply your permission rules, they will define the entire set of permissions for a user. Before sqlauthz applies new permissions, it revokes all permissions from a set of users first. It both revokes and grants the permissions as part of the same transaction, however, so in practice this does not lead to any "downtime" where a user has no permissions.

It's possible that you may not want to control the permissions of all of your users. This is particularly true if you're just trying sqlauthz out or adopting it incrementally. To allow you to use sqlauthz in a way that works for your use-case, there are three different "user revoke strategies" in sqlauthz. A "user revoke strategy" determines what users to revoke permissions from before granting permissions. The three strategies are as follows:

• referenced (default) - Any user who would be granted a permission by your rules will have al