npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

pg-rls-util

v0.0.41

Published

A tool to generate postgres row-level-security policies.

Downloads

43

Readme

pg-rls-util

a tool to manage clean generation of postgres row level security scripts basic usage and to view help:

npx pg-rls-util

should give you

pg-rls-util <command>

Commands:
  pg-rls-util init      initialize the current draft or an entire project
                                                                    [aliases: i]
  pg-rls-util generate  generate all policy scripts                 [aliases: g]
  pg-rls-util release   copy current-draft dir to a new release dir [aliases: r]
  pg-rls-util diff      examine differences between current draft assignments
                        and db introspection                        [aliases: d]
  pg-rls-util merge     merge current-diff into table and function assignments,
                        removing and adding entries as appropriate. [aliases: m]

Options:
  --version  Show version number                                       [boolean]
  --help     Show help                                                 [boolean]

graphile starter

create db

with the graphile-starter schema located here: https://github.com/graphile/starter/blob/main/data/schema.sql

init

perform intial db introspection and table assignment. the default configuration for graphile-starter should do this automatically

npx pg-rls-util init -x -c postgres://[USER]:[PWD]@[HOST]:[PORT]/[DB_NAME] -s app_hidden,app_private,app_public -p graphile-starter

review files in pg-rls-util-gen/current-draft directory

  • roles.json: not really meant to be edited, but it could be
    • graphile_starter
    • graphile_starter_authenticator
    • graphile_starter_visitor
  • script-templates.json: currently poorly formatted - json5? - later could be edited, but may remove??
  • table-security-profiles.json: meant to be edited
  • table-profile-assignments.json: meant to be edited
  • function-security-profiles.json: meant to be edited
  • function-profile-assignments.json: meant to be edited

generate

creates the current-draft/artifacts directory

npx pg-rls-util generate

scripts created include:

  • one-script-to-rule-them-all.sql
    • all table and function policies across all schemata
    • does NOT affect any existing rls policies
  • all-table-policies---all-schemata.sql
  • all-function-policies---all-schemata.sql
  • create-roles.sql
    • will create any missing roles that are needed
  • ownership.sql
    • ensure that all schemas/tables/functions are owned by the proper user
  • remove-all-rls.sql
    • optional script that will remove any existing rls policies across the entire database
    • maybe useful if you are trying to apply a new security procedure to an old database

each schema will have rollup scripts as well as function and table scripts to quickly view the impact of a portion of the overall security policy on just one table.

for instance, pg-rls-util-gen/current-draft/artifacts/app_public/tableScripts/users.sql

-- to run this sql:
--
-- psql -h 0.0.0.0 -U postgres -d graphile_starter -f /Users/buckfactor/tmp/pg-rls-util-gen/current-draft/artifacts/app_public/tableScripts/users.sql

-- this script is meant to used during development                    ----------------------
-- to give a quick view of the before and after state for table:      app_public.users
begin;
\echo
\echo ........
\echo ....DETAILED TABLE INFORMATION
\echo ........
\d+ app_public.users
\echo
\echo ........
\echo ....SECURITY BEFORE SCRIPT EXECUTES
\echo ........
\dp+ app_public.users

\echo
\echo ........
\echo ....LEAVING ANY EXISTING RLS INTACT
\echo ....this setting can be controlled by table-security-profiles.includeTableRlsRemoval settinc
\echo ........

\echo
\echo ........
\echo ....now executing actual table script
\echo ........

----******
----******  BEGIN TABLE POLICY: app_public.users
----******  TABLE SECURITY PROFILE:  graphile-starter:: app_public.users
----******
----------  REMOVE EXISTING TABLE GRANTS
  revoke all privileges on table app_public.users
  from public,
       graphile_starter_visitor
  ;

----------  ENABLE ROW LEVEL SECURITY: app_public.users
  alter table app_public.users enable row level security;

  drop policy if exists select_all on app_public.users;
  create policy select_all on app_public.users as PERMISSIVE for SELECT to graphile_starter_visitor using (true);
  drop policy if exists update_self on app_public.users;
  create policy update_self on app_public.users as PERMISSIVE for UPDATE to graphile_starter_visitor with check (id = app_public.current_user_id());

----------  CREATE NEW TABLE GRANTS: app_public.users

----------  graphile_starter_visitor
  grant
    SELECT ,
    UPDATE (username, name, avatar_url)
       --  excluded columns for UPDATE: id, created_at, is_admin, is_verified, updated_at
  on table app_public.users to graphile_starter_visitor;


----*******  END TABLE POLICY: app_public.users
--**


\echo
\echo ........
\echo ....SECURITY AFTER SCRIPT EXECUTES
\echo ........
\dp+ app_public.users;
rollback;

release

copy the current draft over to a numbered release. really, you will also want to also copy one or more of the generated scripts over to your own db change management tool. but this is a way to snapshot your work as you go along and could be more tightly coupled via automation

npx pg-rls-util release

diff

later, when you have added new tables and functions, diff freshly introspects the database to help identify and review differences.

npx pg-rls-util diff

this will create current-draft/current-diff.json

merge

fold current-diff.json into table-profile-assignments.json and function-profile-assignments.json

npx pg-rls-util merge