Error creating security label using Postgres 14/15 and pgsodium 3.1.5

nik-ewf commented 1 year ago

Steps to reproduce:

SELECT format('ENCRYPT WITH KEY ID %s', (pgsodium.create_key('aead-det')).id) AS seclabel \gset
SECURITY LABEL FOR pgsodium ON COLUMN public.owner.did IS :'seclabel';

Internal Postgres error logs:

2023-02-06 05:10:52.790 UTC [104] ERROR:  syntax error at or near "FROM" at character 217
2023-02-06 05:10:52.790 UTC [104] QUERY:  
           DROP VIEW IF EXISTS public.decrypted_owner;
           CREATE VIEW public.decrypted_owner AS SELECT 
                       id,       
               created,       
               updated,       
               deleted,       
               did,

           FROM public.owner;
           ALTER VIEW public.decrypted_owner OWNER TO postgres;

2023-02-06 05:10:52.790 UTC [104] CONTEXT:  PL/pgSQL function pgsodium.create_mask_view(oid,integer,boolean) line 39 at EXECUTE
       SQL statement "SELECT pgsodium.create_mask_view(objoid, objsubid, debug)
           FROM pg_catalog.pg_seclabel sl
           WHERE sl.objoid = target
             AND sl.label ILIKE 'ENCRYPT%'
             AND sl.provider = 'pgsodium'"
       PL/pgSQL function pgsodium.update_mask(oid,boolean) line 4 at PERFORM
       SQL statement "SELECT pgsodium.update_mask(objoid, debug)
           FROM pg_catalog.pg_seclabel sl
           JOIN pg_catalog.pg_class cl ON (cl.oid = sl.objoid)
           WHERE label ilike 'ENCRYPT%'
              AND cl.relowner = session_user::regrole::oid
              AND provider = 'pgsodium'
                  AND objoid::regclass != 'pgsodium.key'::regclass"
       PL/pgSQL function pgsodium.update_masks(boolean) line 3 at PERFORM
       SQL statement "SELECT pgsodium.update_masks()"
       PL/pgSQL function pgsodium.trg_mask_update() line 9 at PERFORM
2023-02-06 05:10:52.790 UTC [104] STATEMENT:  SECURITY LABEL FOR pgsodium ON COLUMN public.owner.did IS 'ENCRYPT WITH KEY ID d78a3c04-d8aa-4c23-a8b6-85b60867cffb'

For some reason an additional comma , is being added to the last column in the CREATE VIEW statement, not sure why this is?

I've tested this pull request using branch refactor_tce and it works fine.

andrewwasielewski commented 1 year ago

What's the data type of the column public.owner? I've seen a similar error when attempting to add a security label to a column that isn't of type text, ie. character varying

michelp commented 1 year ago

Can you share your table definition?

michelp / pgsodium

Error creating security label using Postgres 14/15 and pgsodium 3.1.5 #71