.editorconfig
@@ -18,7 +18,3 @@ [*.md] trim_trailing_whitespace = false [package.json] indent_style = space indent_size = 2 .huskyrc
File was deleted .jhipster/Asset.json
File was deleted .jhipster/Customer.json
File was deleted .jhipster/Membership.json
File was deleted .jhipster/SepaMandate.json
File was deleted .jhipster/Share.json
File was deleted .jhipster/UserRoleAssignment.json
File was deleted .prettierignore
File was deleted .prettierrc
File was deleted .yo-rc.json
File was deleted Glossary.md
New file @@ -0,0 +1,18 @@ # hsadminNg Glossary ### Business Object Represents an object from the ### Tenant The RBAC ### RBAC abbreviation for *Role Based Access Control* ### Role Based Access Control (RBAC) A system to control access to business objects by defining users, roles, and permissions. For more information see JHIPSTER.md
File was deleted Jenkinsfile
File was deleted README.md
@@ -1,350 +1,77 @@ # hsadminNg Development <!-- START doctoc generated TOC please keep comment here to allow auto update --> <!-- DON'T EDIT THIS SECTION, INSTEAD RE-RUN doctoc TO UPDATE --> - [Setting up the Development Environment](#setting-up-the-development-environment) - [Frequent Tasks](#frequent-tasks) - [Building the Application with Test Execution](#building-the-application-with-test-execution) - [Starting the Application](#starting-the-application) - [Running JUnit tests with branch coverage](#running-junit-tests-with-branch-coverage) - [HOWTO Commits](#howto-commits) - [Creating HOWTO Commits](#creating-howto-commits) - [Special Build Tasks](#special-build-tasks) - [Spotless Formatting](#spotless-formatting) - [Mutation Testing PiTest](#mutation-testing-pitest) - [Git Workflow for JHipster Generator](#git-workflow-for-jhipster-generator) - [Generating the Table of Contents for Markdown](#generating-the-table-of-contents-for-markdown) <!-- END doctoc generated TOC please keep comment here to allow auto update --> ## Setting up the Development Environment You'll often need to execute `./gradlew`, therefore we suggest to define this alias: ### PostgreSQL Server alias gw='./gradlew' So far the spike contains almost only PostgreSQL Code. All you need so far, is a PostgreSQL database, for now with full admin rights. The easiest way to set it up is using docker: TODO: Instructions for setting up the dev environment from scratch. Initially, pull an image compatible to current PostgreSQL version of Hostsharing: ## Frequent Tasks docker pull postgres:13.7-bullseye ### Building the Application with Test Execution Create and run a container with the given PostgreSQL version: gw build docker run --name hsadmin-ng-postgres -e POSTGRES_PASSWORD=password -p 5432:5432 -d postgres:13.7-bullseye ### Starting the Application To check if the PostgreSQL container is running, the following command should list a container with the name "hsadmin-ng-postgres": To use an **H2 in-memory database** populated with sample-data. docker container ls gw bootRun Stop the PostgreSQL container: docker stop hsadmin-ng-postgres To use an **H2 file-based database**, start the application with the h2file profile: Start the PostgreSQL container again: gw bootRun -Ph2file gw bootRun -Ph2file -Psample-data # populated with sample data docker container start hsadmin-ng-postgres To use a **local Postgres database**, first prepare your environment: Remove the PostgreSQL container: export HSADMINNG_DB_URL='jdbc:postgresql://localhost:5432/DBNAME' export HSADMINNG_DB_USER='DBUSER' export HSADMINNG_DB_PASS='DBPASS' docker rm hsadmin-ng-postgres Where `DBNAME`, `DBUSER` and `DBPASS` are replaced by your credentials. After the PostgreSQL container is removed, you need to create it again as shown in "Create and run ..." above. Then start the application with the pgsql profile: ### Markdown with PlantUML plugin gw bootRun -Ppgsql gw bootRun -Ppgsql -Psample-data # populated with sample data Can you see the following diagram? To use a **remote Postgres database** on a Hostsharing server, ```plantuml @startuml me -> you: Can you see this diagram? you -> me: Sorry, I don't :-( me -> you: Install some tooling! @enduml ``` autossh -M 0 -o "ServerAliveInterval 60" -o "ServerAliveCountMax 3" \ -f -N -L 55432:127.0.0.1:5432 "xyz00@xyz.hostsharing.net" If not, you need to install some tooling. Then prepare your environment, e.g. like this: #### for IntelliJ IDEA (or derived products) export HSADMINNG_DB_URL='jdbc:postgresql://localhost:55432/xyz00_hsadminng' export HSADMINNG_DB_USER='xyz00_hsadminng' export HSADMINNG_DB_PASS='whatever' You just need the bundled Markdown plugin enabled and install and activate the PlantUML plugin in its settings: In all cases, you can also **specify the port** to used for the application via environment: jetbrains://idea/settings?name=Languages+%26+Frameworks--Markdown SERVER_PORT=8081 gw bootRun ... You might also need to install Graphviz on your operating system. For Debian-based Linux systems this might work: For starting the JVM of the application in **debug-mode**, add `--debug-jvm` to any of the options above, e.g. ```sh sudo apt install graphviz ``` gw bootRun -Ppgsql -Psample-data --debug-jvm ### Running JUnit tests with branch coverage ### Ubuntu Linux command line #### for IntelliJ IDEA ```sh sudo apt-get install pandoc texlive-latex-base texlive-fonts-recommended texlive-extra-utils texlive-latex-extra pandoc-plantuml-filter ``` see: https://confluence.jetbrains.com/display/IDEADEV/IDEA+Coverage+Runner ```sh pandoc --filter pandoc-plantuml rbac.md -o rbac.pdf ``` Either apply it to specific test configurations or, better, delete the previous test configurations and amend the JUnit template. ### for other IDEs / operating systems ## HOWTO Commits There are git tags on some commits which show how to add certain features. Find all of such tags with: git tag | grep HOWTO ### Creating HOWTO Commits If you want to add such a commit, make sure that it contains no clutter (no changes which are not necessary for whatever the commit is about to explain), and is complete with all unit tests, code coverage, pitest and other checks. Otherwise the next developer would run into the same problems again. One way to keep the commit clean, is to develop it on a local branch. If any other changes (e.g. bugfixes, API extensions etc.) are necessary, apply these only to the master or cherry-pick just these to the master, then rebase your local branch. Do not forget to run all checks locally: gw clean check pitest # might need over an hour (Check the PiTest section for speeding up mutation testing.) To create and push a new tag use: git tag HOWTO-... master git push origin HOWTO-... To moved an existing the tag to another commit (here current master again), do this: git tag --force HOWTO-... master git push --force origin HOWTO-... ## Special Build Tasks Besides common build tasks like `build`, `test` or `bootRun` this projects has some not so common tasks which are explained in this section. ### Spotless Formatting To make sure that no IDE auto-formatter destroys the git history of any file and especially to avoid merge conflicts from JHipster generated files after these had been changed, we are using a standard formatter enforced by _spotless_, which is based on the standard Eclipse formatter. The rules can be checked and applied with these commands: gw spotlessCheck gw spotlessApply The spotlessCheck task is included as an early step in our Jenkins build pipeline. Therefore wrong formatting is automatically detected. Our configuration can be found under the directory `cfg/spotless`. Currently we only have specific rules for _\*.java_-files and their import-order. #### Our Changes to the Standard Eclipse Formatter We amended the Standard Eclipse Formatter in these respects: - Lines of code are never joined, thus the developer has control about linebreaks, which is important for readability in some implementations like toString(). - Lines in comments are never joined either, because that often destroys readable stucture. - Parts of files can be excluded from getting formatted, by using `@formatter:off` and `@formatter:on` in a comment. See for example in class `SecurityConfiguration`. #### Pre-Commit Hook If you like, you could add this code to the _pre-commit or \_pre_push_ hook\_ in your `.git/hooks` directory: if ! ./gradlew spotlessCheck; then exit 1 fi #### The Tagged Spotless Commit The commit which introduces the spotless configuration is tagged. Through this tag it can easily be cherry-picked in the JHipster workflow. If you need to amend the commit tagged 'spotless', e.g. to change the spotless configuration, it can be done with these steps: git tag REAL-HEAD git reset --hard spotless^ git cherry-pick -n spotless ... git add . # do NOT run: gw spotlessApply yet! # for the case you have a commit hook which runs spotlessCheck: git commit --no-verify git tag --force spotless git push --no-verify origin spotless git reset --hard REAL-HEAD git tag -d REAL-HEAD ### Mutation Testing PiTest ./gradlew pitest Runs (almost) all JUnit tests under mutation testing. Mutation testing is a means to determine the quality of the tests. On Jenkins, the results can be found in the build artifacts under: - https://ci.hostsharing.net/job/hsadmin-ng-pitest/XX/artifact/build/reports/pitest/index.html Where XX is the build number. Or for the latest build under: - https://ci.hostsharing.net/job/hsadmin-ng-pitest/lastCompletedBuild/artifact/build/reports/pitest/index.html #### Some Background Information on Mutation Testing PiTest does it with these steps: - initially PiTest checks which production code is executed by which tests - if the tests don't pass, it stops - otherwise the production code is 'mutated' and PiTest checks whether this makes a test fail ('mutant killed') - Finally it checks thresholds for coverage and mutant killing. More information about can be found here: - PiTest: http://pitest.org/ - gradle-plugin: https://gradle-pitest-plugin.solidsoft.info/ #### How to Configure PiTest These thresholds can be configured in `build.gradle`, but we should generally not lower these. There is also a list of excluded files, all generated by JHipster or MapStruct, not containing any changes by us. As you might figure, mutation testing is CPU-hungry. To limit load in our Jenkins build server, it only uses 2 CPU threads, thus it needs over an hour. If you want to spend more CPU threads on your local system, you can change that via command line: gw pitest -Doverride.pitest.threads=7 I suggest to leave one CPU thread for other tasks or your might lag extremely. ### Git Workflow for JHipster Generator The following workflow steps make sure that - JHipster re-imports work properly, - the git history of changes to the JDL-files, the generated code and the master is comprehensible, - and merging newly generated code to the master branch is smooth. It uses a git branch `jhipster-generated` to track the history of the JDL model file and the generated source code. Applying commits which contain non-generated changes to that branch breaks the normal git history for generated files. Therefore, this documentation is also not available in that branch. Thus: **MANUAL STEP before starting:** Copy this workflow documentation, because this file will be gone once you switched the branch. | WARNING: The following steps are just a guideline. You should understand what you are doing! | | -------------------------------------------------------------------------------------------- | #### 1. Preparing the `jhipster-generated` git Branch This step assumes that the latest `*.jdl` files are on the `HEAD` of the `jhipster-generated` git branch. On a re-import of a JDL-file, JHipster does not remove any generated classes which belong to entities deleted from the JDL-file. Therefore, the project has to be reset to a clean state before changes to the JDL file can be re-imported. We have not yet finally tested a simplified workflow for just adding new entities or properties. A git tag `jdl-base` is assumed to sit on the base commit after the application was generated, but before any entities were imported. git checkout jhipster-generated git pull git tag REAL-HEAD git reset --hard jdl-base git clean -f -d git cherry-pick -n spotless git reset --soft REAL-HEAD git checkout REAL-HEAD src/main/jdl/customer.jdl git checkout REAL-HEAD src/main/jdl/accessrights.jdl git checkout REAL-HEAD src/main/jdl/... # once there are more git tag -d REAL-HEAD #### 2. Amending and Re-Importing the JDL **MANUAL STEP:** First apply all necessary changes to the JDL files. Then re-import like this: # (Re-) Importing jhipster import-jdl src/main/jdl/customer.jdl jhipster import-jdl src/main/jdl/accessrights.jdl jhipster import-jdl src/main/jdl/... # once there are more For smoothly being able to merge, we need the same formatting in the generated code as on the master: gw spotlessApply #### 3. Committing our Changes git add . git commit -m"..." #### 4. Merging our Changes to the `master` Branch git checkout master git pull **MANUAL STEP:** If you've renamed any identifiers, use the refactoring feature of your IDE to rename in master as well. To avoid oodles of merge-conflicts, you need to do that **BEFORE MERGING!** Commit any of such changes, if any. Now we can finally merge our changes to master. git merge jhipster-generated It's a good idea doing this step in an IDE because it makes conflict resolving much easier. Typical merge conflicts stem from: - Random numbers in test data of `*IntTest.java` files. - Timestamps in Liquibase-xml-Files. Now, I suggest to run all tests locally: gw clean test Once everything works again, we can push our new version: git push #### 5. General Aftermath Think about which additional code could be effected by your JDL-changes! Files which are not at all in the `jhipster-generated` branch, don't show conflicts even though they might need changes. Here some examples for amendments to be done: - in `historicization_*.xml`: the columns or their constraints - `sampledata/*.xml/csv` If you find more of such general cases, please add them here! #### 6. Special Aftermath for new Entities Because we have added quite some functionality, after introducing new entities, there is a lot more to amend. Here some issues to consider: - add sample-data for the new entity - internal (Angular) frontend: add table filters - internal (Angular) frontend: amend input fields for multiline, if applicable - internal (Angular) frontend: check if dates are properly formatted - \*Mapper: add displayLabel for entity itself and parents - \*DTO: add access-right annotations with customized JSON serializer/deserializer - Validator: implement entity-based validator and call it in the generated service - external API: add new type to client library WARNING: This list is most likely incomplete. Pleas add any new found issue! For many of these issues look for HOWTO-commits in git or HOWTO comments in the source code. ### Generating the Table of Contents for Markdown This README file contains a table of contents generated by _doctoc_. It's quite simple to use: npm install -g doctoc doctoc --maxlevel 3 README.md Further information can be found [https://github.com/thlorenz/doctoc/blob/master/README.md](on the _doctoc_ github page). If you have figured out how it works, please add instructions above this section. adr/2022-07-18.row-level-security-mechanism.md
New file @@ -0,0 +1,160 @@ # Use VIEWs with JOIN into Permission-Assignments for Row-Level-Security **Status:** - [x] proposed by Michael Hönnig - [ ] accepted by (Participants) - [ ] rejected by (Participants) - [ ] superseded by (superseding ADR) ## Context and Problem Statement We need to decide how to apply the access rules defined in our RBAC system to the visibility of table rows for the accessing user. The core problem here is, that in our RBAC system, determining the permissions of the accessing user has to consider a hierarchy of roles. ### Technical Background The session variable `hsadminng.currentUser` contains the accessing (domain-level) user, which is unrelated to the PostgreSQL user). Given is a stored function `isPermissionGrantedToSubject` which detects if the accessing user has a given permission (e.g. 'view'). Given is also a stored function `queryAllPermissionsOfSubjectId` which returns the flattened view to all permissions assigned to the given accessing user. In the following code snippets `customer` is just an example domain table. ## Considered Options * Perform Visibility-Checks programmatically in the Backend * Add Visibility-Checks in the Backend * POLICY with ENABLE ROW LEVEL SECURITY * VIEW-RULE with ON SELECT DO INSTEAD * VIEW with JOIN into Flattened Permissions ### Perform Visibility-Checks programmatically in the Backend In this solution, the database ignores row level visibility and returns all rows which match a given query. Afterwards, the result is filtered programmatically with Java-code in the backend. #### Advantages Very flexible access, programmatic, rules could be implemented. The role-hierarchy and permissions for currently logged-in users user could be cached in the backend. The access logic can be tested in pure Java unit tests. At least regarding this aspect, an in-memory database could be used for integration testing; though the recursive Role-evaluation uses PostgreSQL features anyway. #### Disadvantages It's inefficient when initial query is not very restrictive, e.g. as on overview pages in a frontend, which often show all accessible objects, large parts or even whole database tables need to be transferred from the database to the backend. It's error-prone and security leaks can happen too easily, because after every query the access rights for all participating joins have to be considered. ### Add Visibility-Checks in the Backend In this solution again, the database ignores row level visibility and returns all rows which match a given query. And the backend adds filter conditions to each query sent to the database. #### Advantages At least regarding this aspect, an in-memory database could be used for integration testing. #### Disadvantages It's error-prone and security leaks can happen too easily, because for every query the access rights for all participating joins have to be considered. ### POLICY with ENABLE ROW LEVEL SECURITY For restricted DB-users, which are used by the backend, access to rows is filtered using a policy: SET SESSION AUTHORIZATION DEFAULT; CREATE ROLE restricted; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO restricted; ALTER TABLE customer ENABLE ROW LEVEL SECURITY; CREATE POLICY customer_policy ON customer FOR SELECT TO restricted USING ( isPermissionGrantedToSubject(findPermissionId('customer', id, 'view'), currentUserId()) ); SET SESSION AUTHORIZATION restricted; SET hsadminng.currentUser TO 'alex@example.com'; SELECT * from customer; -- will only return visible rows #### Advantages Using POLICY together with ENABLE ROW LEVEL SECURITY is the PostgreSQL native mechanism to control access to data on the role level. Therefore, it looked like an obvious and elegant solution. Every access at from the backend is under access control at the database level. ### Disadvantages Unfortunately security mechanisms in PostgreSQL prevent the query optimizer to work well beyond ownership barriers (session user vs. table owner) and a SELECT from a table with 1 million objects needed over 30 seconds with our hierarchical RBAC policy. We are bound to PostgreSQL, including integration tests and testing the RBAC system itself. ### VIEW-RULE with ON SELECT DO INSTEAD SET SESSION SESSION AUTHORIZATION DEFAULT; CREATE VIEW cust_view AS SELECT * FROM customer; CREATE OR REPLACE RULE "_RETURN" AS ON SELECT TO cust_view DO INSTEAD SELECT * FROM customer WHERE isPermissionGrantedToSubject(findPermissionId('customer', id, 'view'), currentUserId()); SET SESSION AUTHORIZATION restricted; SET hsadminng.currentUser TO 'alex@example.com'; SELECT * from customer; -- will only return visible rows #### Advantages Every access at from the backend is under access control at the database level. Also using ON UPDATE etc., original tables could be completely hidden from the backend, and thus improved security. ### Disadvantages Unfortunately security mechanisms in PostgreSQL prevent the query optimizer to work well beyond ownership barriers (session user vs. table owner) and a SELECT from a table with 1 million objects needed over 30 seconds with our hierarchical RBAC policy. We are bound to PostgreSQL, including integration tests and testing the RBAC system itself. An extra view needed for every table. ### VIEW with JOIN into flattened permissions We do not access the tables directly from the backend, but via views which join the flattened permissions SET SESSION SESSION AUTHORIZATION DEFAULT; CREATE OR REPLACE VIEW cust_view AS SELECT c.id, c.reference, c.prefix FROM customer AS c JOIN queryAllPermissionsOfSubjectId(currentUserId()) AS p ON p.tableName='customer' AND p.rowId=c.id AND p.op='view'; GRANT ALL PRIVILEGES ON cust_view TO restricted; SET SESSION SESSION AUTHORIZATION restricted; SET hsadminng.currentUser TO 'alex@example.com'; SELECT * from cust_view; -- will only return visible rows Alternatively the JOIN could also be applied in a "ON SELECT DO INSTEAD"-RULE, if there is any advantage for later features. #### Advantages Every access at from the backend is under access control at the database level. No special PostgreSQL features needed; though the recursive Role-evaluation uses PostgreSQL features anyway. Very fast, on my laptop a SELECT * FROM a table with 1 million rows just took about 50ms. Also using ON UPDATE etc., original tables could be completely hidden from the backend, and thus improved security. ### Disadvantages An extra view needed for every table. ## Decision Outcome We chose the option **"VIEW with JOIN into flattened permissions"** because it supports the best combination of performance and security with almost no disadvantge. angular.json
File was deleted build-cucumber.gradle
File was deleted build-jacoco.gradle
File was deleted build-pitest.gradle
File was deleted build-spotless.gradle
File was deleted build.gradle
File was deleted cfg/spotless/eclipse_formatter.xml
File was deleted cfg/spotless/hsadminng.importorder
File was deleted cfg/spotless/javascript_formatters.xml
File was deleted gradle.properties
File was deleted gradle/docker.gradle
File was deleted gradle/profile_dev.gradle
File was deleted gradle/profile_prod.gradle
File was deleted gradle/sonar.gradle
File was deleted gradle/swagger.gradle
File was deleted gradle/wrapper/gradle-wrapper.jarBinary files differ
gradle/wrapper/gradle-wrapper.properties
File was deleted gradle/zipkin.gradle
File was deleted gradlew
File was deleted gradlew.bat
File was deleted package-lock.json
File was deleted package.json
File was deleted postcss.config.js
File was deleted proxy.conf.json
File was deleted settings.gradle
File was deleted sql/00-util.sql
New file @@ -0,0 +1,38 @@ abort; set local session authorization default; CREATE OR REPLACE FUNCTION array_distinct(anyarray) RETURNS anyarray AS $f$ SELECT array_agg(DISTINCT x) FROM unnest($1) t(x); $f$ LANGUAGE SQL IMMUTABLE; CREATE OR REPLACE FUNCTION lastRowCount() RETURNS bigint LANGUAGE plpgsql AS $$ DECLARE lastRowCount bigint; BEGIN GET DIAGNOSTICS lastRowCount = ROW_COUNT; RETURN lastRowCount; END; $$; -- ======================================================== -- Test Data helpers -- -------------------------------------------------------- CREATE OR REPLACE FUNCTION intToVarChar(i integer, len integer) RETURNS varchar LANGUAGE plpgsql AS $$ DECLARE partial varchar; BEGIN SELECT chr(ascii('a') + i%26) INTO partial; IF len > 1 THEN RETURN intToVarChar(i/26, len-1) || partial; ELSE RETURN partial; END IF; END; $$; SELECT * FROM intToVarChar(211, 4); sql/10-rbac-base.sql
New file @@ -0,0 +1,657 @@ -- ======================================================== -- RBAC -- -------------------------------------------------------- SET SESSION SESSION AUTHORIZATION DEFAULT; -- https://arctype.com/blog/postgres-uuid/#creating-a-uuid-primary-key-using-uuid-osp-postgresql-example CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; DROP TABLE IF EXISTS "RbacPermission"; DROP TABLE IF EXISTS "RbacGrants"; DROP TABLE IF EXISTS "RbacUser"; DROP TABLE IF EXISTS RbacReference CASCADE; DROP TYPE IF EXISTS RbacOp CASCADE; DROP TYPE IF EXISTS ReferenceType CASCADE; CREATE TYPE ReferenceType AS ENUM ('RbacUser', 'RbacRole', 'RbacPermission'); CREATE TABLE RbacReference ( uuid uuid UNIQUE DEFAULT uuid_generate_v4(), type ReferenceType not null ); CREATE TABLE RbacUser ( uuid uuid primary key references RbacReference (uuid) ON DELETE CASCADE, name varchar(63) not null unique ); CREATE TABLE RbacRole ( uuid uuid primary key references RbacReference (uuid) ON DELETE CASCADE, name varchar(63) not null unique ); CREATE TABLE RbacGrants ( ascendantUuid uuid references RbacReference (uuid) ON DELETE CASCADE, descendantUuid uuid references RbacReference (uuid) ON DELETE CASCADE, -- apply bool not null, -- alternative 1 to implement assumable roles primary key (ascendantUuid, descendantUuid) ); CREATE INDEX ON RbacGrants (ascendantUuid); CREATE INDEX ON RbacGrants (descendantUuid); DROP DOMAIN IF EXISTS RbacOp CASCADE; CREATE DOMAIN RbacOp AS VARCHAR(67) CHECK( VALUE = '*' OR VALUE = 'delete' OR VALUE = 'edit' OR VALUE = 'view' OR VALUE = 'assume' OR VALUE ~ '^add-[a-z]+$' ); DROP TABLE IF EXISTS RbacObject; CREATE TABLE RbacObject ( uuid uuid UNIQUE DEFAULT uuid_generate_v4(), objectTable varchar(64) not null, unique (objectTable, uuid) ); CREATE OR REPLACE FUNCTION createRbacObject() RETURNS trigger LANGUAGE plpgsql STRICT AS $$ DECLARE objectUuid uuid; BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO RbacObject (objectTable) VALUES (TG_TABLE_NAME) RETURNING uuid INTO objectUuid; NEW.uuid = objectUuid; RETURN NEW; ELSE RAISE EXCEPTION 'invalid usage of TRIGGER AFTER INSERT'; END IF; END; $$; DROP TABLE IF EXISTS RbacPermission; CREATE TABLE RbacPermission ( uuid uuid primary key references RbacReference (uuid) ON DELETE CASCADE, objectUuid uuid not null, op RbacOp not null, unique (objectUuid, op) ); CREATE OR REPLACE FUNCTION hasPermission(forObjectUuid uuid, forOp RbacOp) RETURNS bool LANGUAGE sql AS $$ SELECT EXISTS ( SELECT op FROM RbacPermission p WHERE p.objectUuid=forObjectUuid AND p.op in ('*', forOp) ); $$; CREATE OR REPLACE FUNCTION createRbacUser(userName varchar) RETURNS uuid RETURNS NULL ON NULL INPUT LANGUAGE plpgsql AS $$ declare objectId uuid; BEGIN INSERT INTO RbacReference (type) VALUES ('RbacUser') RETURNING uuid INTO objectId; INSERT INTO RbacUser (uuid, name) VALUES (objectid, userName); return objectId; END; $$; CREATE OR REPLACE FUNCTION findRbacUser(userName varchar) -- TODO: rename to ...Id RETURNS uuid RETURNS NULL ON NULL INPUT LANGUAGE sql AS $$ SELECT uuid FROM RbacUser WHERE name = userName $$; CREATE OR REPLACE FUNCTION getRbacUserId(userName varchar, whenNotExists RbacWhenNotExists) RETURNS uuid RETURNS NULL ON NULL INPUT LANGUAGE plpgsql AS $$ DECLARE userUuid uuid; BEGIN userUuid = findRbacUser(userName); IF ( userUuid IS NULL ) THEN IF ( whenNotExists = 'fail') THEN RAISE EXCEPTION 'RbacUser with name="%" not found', userName; END IF; IF ( whenNotExists = 'create') THEN userUuid = createRbacUser(userName); END IF; END IF; return userUuid; END; $$; CREATE OR REPLACE FUNCTION createRole(roleName varchar) RETURNS uuid RETURNS NULL ON NULL INPUT LANGUAGE plpgsql AS $$ declare referenceId uuid; BEGIN INSERT INTO RbacReference (type) VALUES ('RbacRole') RETURNING uuid INTO referenceId; INSERT INTO RbacRole (uuid, name) VALUES (referenceId, roleName); IF (referenceId IS NULL) THEN RAISE EXCEPTION 'referenceId for roleName "%" is unexpectedly null', roleName; end if; return referenceId; END; $$; CREATE OR REPLACE PROCEDURE deleteRole(roleUUid uuid) LANGUAGE plpgsql AS $$ BEGIN DELETE FROM RbacRole WHERE uuid=roleUUid; END; $$; CREATE OR REPLACE FUNCTION findRoleId(roleName varchar) RETURNS uuid RETURNS NULL ON NULL INPUT LANGUAGE sql AS $$ SELECT uuid FROM RbacRole WHERE name = roleName $$; CREATE TYPE RbacWhenNotExists AS ENUM ('fail', 'create'); CREATE OR REPLACE FUNCTION getRoleId(roleName varchar, whenNotExists RbacWhenNotExists) RETURNS uuid RETURNS NULL ON NULL INPUT LANGUAGE plpgsql AS $$ DECLARE roleUuid uuid; BEGIN roleUuid = findRoleId(roleName); IF ( roleUuid IS NULL ) THEN IF ( whenNotExists = 'fail') THEN RAISE EXCEPTION 'RbacRole with name="%" not found', roleName; END IF; IF ( whenNotExists = 'create') THEN roleUuid = createRole(roleName); END IF; END IF; return roleUuid; END; $$; -- select getRoleId('hostmaster', 'create'); CREATE OR REPLACE FUNCTION createPermissions(forObjectUuid uuid, permitOps RbacOp[]) RETURNS uuid[] LANGUAGE plpgsql AS $$ DECLARE refId uuid; permissionIds uuid[] = ARRAY[]::uuid[]; BEGIN IF ( forObjectUuid IS NULL ) THEN RAISE EXCEPTION 'forObjectUuid must not be null'; END IF; IF ( array_length(permitOps, 1) > 1 AND '*' = any(permitOps) ) THEN RAISE EXCEPTION '"*" operation must not be assigned along with other operations: %', permitOps; END IF; FOR i IN array_lower(permitOps, 1)..array_upper(permitOps, 1) LOOP refId = (SELECT uuid FROM RbacPermission WHERE objectUuid=forObjectUuid AND op=permitOps[i]); IF (refId IS NULL) THEN INSERT INTO RbacReference ("type") VALUES ('RbacPermission') RETURNING uuid INTO refId; INSERT INTO RbacPermission (uuid, objectUuid, op) VALUES (refId, forObjectUuid, permitOps[i]); END IF; permissionIds = permissionIds || refId; END LOOP; return permissionIds; END; $$; CREATE OR REPLACE FUNCTION findPermissionId(forObjectTable varchar, forObjectUuid uuid, forOp RbacOp) RETURNS uuid RETURNS NULL ON NULL INPUT STABLE LEAKPROOF LANGUAGE sql AS $$ SELECT uuid FROM RbacPermission p WHERE p.objectUuid=forObjectUuid AND p.op in ('*', forOp) $$; CREATE OR REPLACE FUNCTION assertReferenceType(argument varchar, referenceId uuid, expectedType ReferenceType) RETURNS ReferenceType LANGUAGE plpgsql AS $$ DECLARE actualType ReferenceType; BEGIN actualType = (SELECT type FROM RbacReference WHERE uuid=referenceId); IF ( actualType <> expectedType ) THEN RAISE EXCEPTION '% must reference a %, but got a %', argument, expectedType, actualType; end if; RETURN expectedType; END; $$; CREATE OR REPLACE PROCEDURE grantPermissionsToRole(roleUuid uuid, permissionIds uuid[]) LANGUAGE plpgsql AS $$ BEGIN FOR i IN array_lower(permissionIds, 1)..array_upper(permissionIds, 1) LOOP perform assertReferenceType('roleId (ascendant)', roleUuid, 'RbacRole'); perform assertReferenceType('permissionId (descendant)', permissionIds[i], 'RbacPermission'); -- INSERT INTO RbacGrants (ascendantUuid, descendantUuid, apply) VALUES (roleId, permissionIds[i], true); -- assumeV1 INSERT INTO RbacGrants (ascendantUuid, descendantUuid) VALUES (roleUuid, permissionIds[i]); END LOOP; END; $$; CREATE OR REPLACE PROCEDURE grantRoleToRole(subRoleId uuid, superRoleId uuid -- , doapply bool = true -- assumeV1 ) LANGUAGE plpgsql AS $$ BEGIN perform assertReferenceType('superRoleId (ascendant)', superRoleId, 'RbacRole'); perform assertReferenceType('subRoleId (descendant)', subRoleId, 'RbacRole'); RAISE NOTICE 'granting subRole % to superRole %', subRoleId, superRoleId; -- TODO: remove IF ( isGranted(subRoleId, superRoleId) ) THEN RAISE EXCEPTION 'Cyclic role grant detected between % and %', subRoleId, superRoleId; END IF; -- INSERT INTO RbacGrants (ascendantUuid, descendantUuid, apply) VALUES (superRoleId, subRoleId, doapply); -- assumeV1 INSERT INTO RbacGrants (ascendantUuid, descendantUuid) VALUES (superRoleId, subRoleId) ON CONFLICT DO NOTHING ; -- TODO: remove END; $$; CREATE OR REPLACE PROCEDURE revokeRoleFromRole(subRoleId uuid, superRoleId uuid) LANGUAGE plpgsql AS $$ BEGIN perform assertReferenceType('superRoleId (ascendant)', superRoleId, 'RbacRole'); perform assertReferenceType('subRoleId (descendant)', subRoleId, 'RbacRole'); IF ( isGranted(subRoleId, superRoleId) ) THEN DELETE FROM RbacGrants WHERE ascendantUuid=superRoleId AND descendantUuid=subRoleId; END IF; END; $$; CREATE OR REPLACE PROCEDURE grantRoleToUser(roleId uuid, userId uuid) LANGUAGE plpgsql AS $$ BEGIN perform assertReferenceType('roleId (ascendant)', roleId, 'RbacRole'); perform assertReferenceType('userId (descendant)', userId, 'RbacUser'); -- INSERT INTO RbacGrants (ascendantUuid, descendantUuid, apply) VALUES (userId, roleId, true); -- assumeV1 INSERT INTO RbacGrants (ascendantUuid, descendantUuid) VALUES (userId, roleId) ON CONFLICT DO NOTHING ; -- TODO: remove END; $$; abort; set local session authorization default; CREATE OR REPLACE FUNCTION queryAccessibleObjectUuidsOfSubjectIds( requiredOp RbacOp, -- objectTable varchar, -- TODO: maybe another optimization? but test perforamance for joins! subjectIds uuid[], maxDepth integer = 8, maxObjects integer = 16000) RETURNS SETOF uuid RETURNS NULL ON NULL INPUT LANGUAGE plpgsql AS $$ DECLARE foundRows bigint; BEGIN RETURN QUERY SELECT DISTINCT perm.objectUuid FROM ( WITH RECURSIVE grants AS ( SELECT descendantUuid, ascendantUuid, 1 AS level FROM RbacGrants WHERE ascendantUuid = ANY(subjectIds) UNION ALL SELECT "grant".descendantUuid, "grant".ascendantUuid, level + 1 AS level FROM RbacGrants "grant" INNER JOIN grants recur ON recur.descendantUuid = "grant".ascendantUuid WHERE level <= maxDepth ) SELECT descendantUuid FROM grants -- LIMIT maxObjects+1 ) as granted JOIN RbacPermission perm ON granted.descendantUuid=perm.uuid AND perm.op IN ('*', requiredOp); foundRows = lastRowCount(); IF foundRows > maxObjects THEN RAISE EXCEPTION 'Too many accessible objects, limit is %, found %.', maxObjects, foundRows USING ERRCODE = 'P0003', -- 'HS-ADMIN-NG:ACC-OBJ-EXC', HINT = 'Please assume a sub-role and try again.'; END IF; END; $$; abort; set local session authorization restricted; begin transaction; set local statement_timeout TO '60s'; select count(*) from queryAccessibleObjectUuidsOfSubjectIds('view', ARRAY[findRbacUser('mike@hostsharing.net')], 4, 10000); end transaction; --- abort; set local session authorization default; CREATE OR REPLACE FUNCTION queryRequiredPermissionsOfSubjectIds(requiredOp RbacOp, subjectIds uuid[]) RETURNS SETOF RbacPermission STRICT LANGUAGE sql AS $$ SELECT DISTINCT * FROM RbacPermission WHERE op = '*' OR op = requiredOp AND uuid IN ( WITH RECURSIVE grants AS ( SELECT DISTINCT descendantUuid, ascendantUuid FROM RbacGrants WHERE ascendantUuid = ANY(subjectIds) UNION ALL SELECT "grant".descendantUuid, "grant".ascendantUuid FROM RbacGrants "grant" INNER JOIN grants recur ON recur.descendantUuid = "grant".ascendantUuid ) SELECT descendantUuid FROM grants ); $$; abort; set local session authorization restricted; begin transaction; -- set local statement_timeout TO '5s'; set local statement_timeout TO '5min'; select count(*) from queryRequiredPermissionsOfSubjectIds('view', ARRAY[findRbacUser('mike@hostsharing.net')]); end transaction; --- abort; set local session authorization default; CREATE OR REPLACE FUNCTION queryAllPermissionsOfSubjectIds(subjectIds uuid[]) RETURNS SETOF RbacPermission STRICT LANGUAGE sql AS $$ SELECT DISTINCT * FROM RbacPermission WHERE uuid IN ( WITH RECURSIVE grants AS ( SELECT DISTINCT descendantUuid, ascendantUuid FROM RbacGrants WHERE ascendantUuid = ANY(subjectIds) UNION ALL SELECT "grant".descendantUuid, "grant".ascendantUuid FROM RbacGrants "grant" INNER JOIN grants recur ON recur.descendantUuid = "grant".ascendantUuid ) SELECT descendantUuid FROM grants ); $$; abort; set local session authorization restricted; begin transaction; set local statement_timeout TO '5s'; select count(*) from queryAllPermissionsOfSubjectIds(ARRAY[findRbacUser('mike@hostsharing.net')]); end transaction; --- CREATE OR REPLACE FUNCTION queryAllPermissionsOfSubjectId(subjectId uuid) -- TODO: remove? RETURNS SETOF RbacPermission RETURNS NULL ON NULL INPUT LANGUAGE sql AS $$ SELECT * FROM RbacPermission WHERE uuid IN ( WITH RECURSIVE grants AS ( SELECT descendantUuid, ascendantUuid FROM RbacGrants WHERE ascendantUuid = subjectId UNION ALL SELECT "grant".descendantUuid, "grant".ascendantUuid FROM RbacGrants "grant" INNER JOIN grants recur ON recur.descendantUuid = "grant".ascendantUuid ) SELECT descendantUuid FROM grants ); $$; --- CREATE OR REPLACE FUNCTION queryAllRbacUsersWithPermissionsFor(objectId uuid) RETURNS SETOF RbacUser RETURNS NULL ON NULL INPUT LANGUAGE sql AS $$ SELECT * FROM RbacUser WHERE uuid IN ( WITH RECURSIVE grants AS ( SELECT descendantUuid, ascendantUuid FROM RbacGrants WHERE descendantUuid = objectId UNION ALL SELECT "grant".descendantUuid, "grant".ascendantUuid FROM RbacGrants "grant" INNER JOIN grants recur ON recur.ascendantUuid = "grant".descendantUuid ) SELECT ascendantUuid FROM grants ); $$; CREATE OR REPLACE FUNCTION findGrantees(grantedId uuid) RETURNS SETOF RbacReference RETURNS NULL ON NULL INPUT LANGUAGE sql AS $$ SELECT reference.* FROM ( WITH RECURSIVE grants AS ( SELECT descendantUuid, ascendantUuid FROM RbacGrants WHERE descendantUuid = grantedId UNION ALL SELECT "grant".descendantUuid, "grant".ascendantUuid FROM RbacGrants "grant" INNER JOIN grants recur ON recur.ascendantUuid = "grant".descendantUuid ) SELECT ascendantUuid FROM grants ) as grantee JOIN RbacReference reference ON reference.uuid=grantee.ascendantUuid; $$; CREATE OR REPLACE FUNCTION isGranted(granteeId uuid, grantedId uuid) RETURNS bool RETURNS NULL ON NULL INPUT LANGUAGE sql AS $$ SELECT granteeId=grantedId OR granteeId IN ( WITH RECURSIVE grants AS ( SELECT descendantUuid, ascendantUuid FROM RbacGrants WHERE descendantUuid = grantedId UNION ALL SELECT "grant".descendantUuid, "grant".ascendantUuid FROM RbacGrants "grant" INNER JOIN grants recur ON recur.ascendantUuid = "grant".descendantUuid ) SELECT ascendantUuid FROM
