keep user privs on regenerated views. (#58)

michelp · Dec 15, 2022 · a114f51 · a114f51
1 parent 060e27f
commit a114f51
Show file tree

Hide file tree

Showing 5 changed files with 148 additions and 6 deletions.
diff --git a/META.json b/META.json
@@ -2,7 +2,7 @@
     "name": "pgsodium",
     "abstract": "Postgres extension for libsodium functions",
     "description": "pgsodium is a PostgreSQL extension that exposes modern libsodium based cryptographic functions to SQL.",
-    "version": "3.1.4",
+    "version": "3.1.5",
     "maintainer": [
         "Michel Pelletier <pelletier.michel@gmail.com>"
     ],
@@ -13,7 +13,7 @@
             "abstract": "Postgres extension for libsodium functions",
             "file": "src/pgsodium.h",
             "docfile": "README.md",
-            "version": "3.1.4"
+            "version": "3.1.5"
         }
     },
     "prereqs": {

diff --git a/pgsodium.control b/pgsodium.control
@@ -1,5 +1,5 @@
 # pgsodium extension
 comment = 'Postgres extension for libsodium functions'
-default_version = '3.1.4'
+default_version = '3.1.5'
 relocatable = false
 schema = pgsodium
diff --git a/sql/pgsodium--3.1.4--3.1.5.sql b/sql/pgsodium--3.1.4--3.1.5.sql
@@ -0,0 +1,125 @@
+
+
+CREATE OR REPLACE FUNCTION pgsodium.create_mask_view(relid oid, subid integer, debug boolean = false)
+    RETURNS void AS
+  $$
+DECLARE
+  m record;
+  body text;
+  source_name text;
+  view_owner regrole = session_user;
+  rule pgsodium.masking_rule;
+  privs aclitem[];
+  priv record;
+BEGIN
+  SELECT DISTINCT * INTO STRICT rule FROM pgsodium.masking_rule WHERE attrelid = relid AND attnum = subid;
+
+  source_name := relid::regclass::text;
+
+  BEGIN
+    SELECT relacl INTO STRICT privs FROM pg_catalog.pg_class WHERE oid = rule.view_name::regclass::oid;
+  EXCEPTION
+	WHEN undefined_table THEN
+      SELECT relacl INTO STRICT privs FROM pg_catalog.pg_class WHERE oid = relid;
+  END;
+
+  body = format(
+    $c$
+    DROP VIEW IF EXISTS %s;
+    CREATE VIEW %s AS SELECT %s
+    FROM %s;
+    ALTER VIEW %s OWNER TO %s;
+    $c$,
+    rule.view_name,
+    rule.view_name,
+    pgsodium.decrypted_columns(relid),
+    source_name,
+    rule.view_name,
+    view_owner
+  );
+  IF debug THEN
+    RAISE NOTICE '%', body;
+  END IF;
+  EXECUTE body;
+
+  FOR priv IN SELECT * FROM pg_catalog.aclexplode(privs) LOOP
+	body = format(
+	  $c$
+	  GRANT %s ON %s TO %s;
+	  $c$,
+	  priv.privilege_type,
+	  rule.view_name,
+	  priv.grantee::regrole::text
+	);
+	IF debug THEN
+	  RAISE NOTICE '%', body;
+	END IF;
+	EXECUTE body;
+  END LOOP;
+
+  FOR m IN SELECT * FROM pgsodium.mask_columns where attrelid = relid LOOP
+	IF m.key_id IS NULL AND m.key_id_column is NULL THEN
+	  CONTINUE;
+	ELSE
+	  body = format(
+		$c$
+		DROP FUNCTION IF EXISTS %s."%s_encrypt_secret_%s"() CASCADE;
+
+		CREATE OR REPLACE FUNCTION %s."%s_encrypt_secret_%s"()
+		  RETURNS TRIGGER
+		  LANGUAGE plpgsql
+		  AS $t$
+		BEGIN
+		%s;
+		RETURN new;
+		END;
+		$t$;
+
+		ALTER FUNCTION  %s."%s_encrypt_secret_%s"() OWNER TO %s;
+
+		DROP TRIGGER IF EXISTS "%s_encrypt_secret_trigger_%s" ON %s;
+
+		CREATE TRIGGER "%s_encrypt_secret_trigger_%s"
+		  BEFORE INSERT OR UPDATE OF "%s" ON %s
+		  FOR EACH ROW
+		  EXECUTE FUNCTION %s."%s_encrypt_secret_%s" ();
+		  $c$,
+		rule.relnamespace,
+		rule.relname,
+		m.attname,
+		rule.relnamespace,
+		rule.relname,
+		m.attname,
+		pgsodium.encrypted_column(relid, m),
+		rule.relnamespace,
+		rule.relname,
+		m.attname,
+		view_owner,
+		rule.relname,
+		m.attname,
+		source_name,
+		rule.relname,
+		m.attname,
+		m.attname,
+		source_name,
+		rule.relnamespace,
+		rule.relname,
+		m.attname
+	  );
+	  if debug THEN
+		RAISE NOTICE '%', body;
+	  END IF;
+	  EXECUTE body;
+	END IF;
+  END LOOP;
+
+  PERFORM pgsodium.mask_role(oid::regrole, source_name, rule.view_name)
+  FROM pg_roles WHERE pgsodium.has_mask(oid::regrole, source_name);
+
+  RETURN;
+END
+  $$
+  LANGUAGE plpgsql
+  VOLATILE
+  SET search_path='pg_catalog'
+;
diff --git a/test/tce.sql b/test/tce.sql
@@ -1,6 +1,6 @@
 \if :serverkeys
 BEGIN;
-SELECT plan(10);
+SELECT plan(15);
 
 CREATE SCHEMA private;
 CREATE SCHEMA "private-test";
@@ -63,7 +63,7 @@ SELECT lives_ok(
 -- Create a key id to use in the tests below
 SELECT id AS secret_key_id FROM pgsodium.create_key('aead-det', 'OPTIONAL_NAME') \gset
 
--- Create a key id to use in the tests below
+-- Create another key id to use in the tests below
 SELECT id AS secret2_key_id
   FROM pgsodium.create_key('aead-det', 'Optional Name 2') \gset
 
@@ -122,6 +122,24 @@ GRANT USAGE ON ALL SEQUENCES IN SCHEMA "private-test" TO bobo;
 SELECT * FROM finish();
 COMMIT;
 
+select pgsodium.update_masks();
+
+select ok(has_table_privilege('bobo', 'private.bar', 'SELECT'),
+	'user keeps privs after regeneration');
+
+select ok(has_table_privilege('bobo', 'private.other_bar', 'SELECT'),
+	'user keeps view select privs after regeneration');
+
+select ok(has_table_privilege('bobo', 'private.other_bar', 'INSERT'),
+	'user keeps view insert privs after regeneration');
+
+select ok(has_table_privilege('bobo', 'private.other_bar', 'UPDATE'),
+	'user keeps view update privs after regeneration');
+
+select ok(has_table_privilege('bobo', 'private.other_bar', 'DELETE'),
+	'user keeps view delete privs after regeneration');
+
+SELECT * FROM finish();
 \c - bobo
 
 BEGIN;

diff --git a/test/test.sql b/test/test.sql
@@ -7,7 +7,6 @@
 
 \set ON_ERROR_ROLLBACK 1
 \set ON_ERROR_STOP on
--- \set QUIET 1
 
 CREATE EXTENSION IF NOT EXISTS pgtap;