michelp / pgsodium Goto Github PK

View Code? Open in Web Editor NEW

511.0 11.0 31.0 462 KB

Modern cryptography for PostgreSQL using libsodium.

License: Other

Makefile 0.19% C 41.56% PLpgSQL 35.37% Dockerfile 1.19% Shell 2.41% Jupyter Notebook 10.96% Perl 8.27% Batchfile 0.05%

postgresql cryptography library encryption decryption libsodium sql

pgsodium's People

Contributors

Stargazers

Watchers

pgsodium's Issues

Add error message when the security label isn't syntactically correct

For example, we don't get any error message with such a label:

'ENCRYPT WITH KEY 793cd018-6c7d-473d-94a4-04f68c93badb'

although the keywork ID is missing.

Avoid secret logging

Hello

Re https://github.com/michelp/pgsodium#avoid-secret-logging

Setting log_statement to 'none' isn't enough to avoid secret logging.

If, say, log_min_duration_statement is set to 0, the secret will be logged anyway.

I don't know if there's a simple way to avoid completely secret logging (besides log_min_duration_statement, logging could be handled by an ad hoc extension).

Running into `fatal error: 'sodium.h' file not found` error during installation

Here is the complete error

Which version supports on debian 10

Which version supports on debian 10
I see libsodium and libsodium-dev supports only 1.0.17-1

How to set security_invoker automatically?

Currently every time we use:

SECURITY LABEL FOR pgsodium ON COLUMN ...

it generates/replaces a decrypted view. This view does not have security_invoker = on by default.

We can use a separate command to enable it:

ALTER VIEW public.decrypted_foo SET (security_invoker = on);

However if we were to enable encryption on another column from the same table, this would remove security_invoker since the view is replaced.

I'm not really sure what the right solution is here, but i did find it a bit surprising and clearly since security_invoker is such a critical configuration property of a view to enable RLS, having it removed is a big security issue.

Should security_invoker be enabled by default on supported Postgres versions? Should it remain on the newly generated view if it existed on the previous version? Should we be able to specify it when calling SECURITY LABEL?

Thanks for your consideration!

only pgsodium 1.0.0 is available for installation on PostgreSQL 9.6

Error installing the extension on PostgreSQL 9.6

available version for installation

postgres=# select name, default_version as version, comment from pg_available_extensions where name like '%pgsodium%' order by 1
postgres-# ;
   name   | version |                  comment                   
----------+---------+--------------------------------------------
 pgsodium | 2.0.2   | Postgres extension for libsodium functions
(1 row)

create extension

postgres=# CREATE EXTENSION pgsodium;
ERROR:  could not stat file "/usr/share/postgresql/9.6/extension/pgsodium--2.0.2.sql": No such file or directory

lists the specific extension versions that are available for installation

postgres=# select * from pg_available_extension_versions where name = 'pgsodium' order by version desc;
   name   | version | installed | superuser | relocatable | schema | requires |                  comment                   
----------+---------+-----------+-----------+-------------+--------+----------+--------------------------------------------
 pgsodium | 1.0.0   | f         | t         | t           |        |          | Postgres extension for libsodium functions
(1 row)

we can only install version 1.0.0

postgres=# CREATE EXTENSION pgsodium WITH VERSION '1.0.0';
CREATE EXTENSION
postgres=# \dx
                         List of installed extensions
   Name   | Version |   Schema   |                Description                 
----------+---------+------------+--------------------------------------------
 pgsodium | 1.0.0   | public     | Postgres extension for libsodium functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

without the possibility of updating

error:

postgres=# ALTER EXTENSION pgsodium UPDATE;
ERROR:  syntax error at or near "FROM"
LINE 2:    REVOKE ALL ON FUNCTION derive_key FROM PUBLIC;
                                             ^
QUERY:  
			REVOKE ALL ON FUNCTION derive_key FROM PUBLIC;
			GRANT EXECUTE ON FUNCTION derive_key TO pgsodium_keymaker;
		
CONTEXT:  PL/pgSQL function inline_code_block line 29 at EXECUTE

list of extension files

root@6d7ebcea4a6f:/# ls -l /usr/share/postgresql/9.6/extension/ | grep pgsodium
-rw-r--r-- 1 root root    6300 Oct 17 19:18 pgsodium--1.0.0--1.1.0.sql
-rw-r--r-- 1 root root    4606 Oct 17 19:18 pgsodium--1.0.0.sql
-rw-r--r-- 1 root root      59 Oct 17 19:18 pgsodium--1.1.0--1.1.1.sql
-rw-r--r-- 1 root root    6731 Oct 17 19:18 pgsodium--1.1.1--1.2.0.sql
-rw-r--r-- 1 root root    7076 Oct 17 19:18 pgsodium--1.2.0--2.0.0.sql
-rw-r--r-- 1 root root       0 Oct 17 19:18 pgsodium--2.0.0--2.0.1.sql
-rw-r--r-- 1 root root       0 Oct 17 19:18 pgsodium--2.0.1--2.0.2.sql
-rw-r--r-- 1 root root     135 Oct 17 19:18 pgsodium.control

jsonb type support

Hello! Thank you for the great extension.

Are there any plans to enable TCE on jsonb data? It would be even better if it could be supported!

Currently, we got this error:

ECURITY LABEL FOR pgsodium ON COLUMN temp_table.content IS 'ENCRYPT WITH KEY ID c9c52d1c-372d-4187-bcd6-dad2a06c3992';
ERROR:  syntax error at or near ","
LINE 12: ,
         ^
QUERY:
    DROP VIEW IF EXISTS pg_temp_13.decrypted_temp_table;
    CREATE VIEW pg_temp_13.decrypted_temp_table AS SELECT
                id,
        content,
,
        created_at
    FROM temp_table;
    ALTER VIEW pg_temp_13.decrypted_temp_table OWNER TO postgres;

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

CREATE TABLE temp_table (
    id varchar(128) NOT NULL,
    content jsonb NOT NULL,
    created_at timestamp with time zone NOT NULL,
    PRIMARY KEY (id)
);

We use this on supabase (hosted):

> SELECT VERSION();
PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg20.04+1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit

Transparent Column Encryption with Empty string

Hello,

I created a simple table for TCE testing.
I got everything created automatically (function, trigger, decrypted view).
However, when I insert empty string in the column to be encrypted, the decryption fails.

CREATE TABLE users (
id bigserial primary key,
secret text,
key_id uuid not null default 'e3496f2a-787f-45a0-9717-f648496179d1',
nonce bytea default pgsodium.crypto_aead_det_noncegen()
);

SECURITY LABEL FOR pgsodium
ON COLUMN users.secret
IS 'ENCRYPT WITH KEY COLUMN key_id NONCE nonce';

insert into users( secret ) values ( '12345' );
select * from decrypted_users;
insert into users( secret ) values ( '' );
select * from decrypted_users;

SQL Error [22000]: ERROR: pgsodium_crypto_aead_det_decrypt_by_id: invalid message
Where: PL/pgSQL function pgsodium.crypto_aead_det_decrypt(bytea,bytea,uuid,bytea) line 12 at RETURN

Regards,

Transparent Column Decryption

Hello,

It's not clear to me how to decrypt a column using a view that was encrypted with a security label. Is there any documentation around this? Thanks!

Affect on performance

I'm curious about the affect on performance. Have you done any benchmarking?

How to manage pgsodium_root.key in primary-secondary cluster

I have a primary-secondary cluster with automatic failover and load balancing. All nodes should have the same root key in pgsodium_root.key otherwise it will create conflicts on the secondary nodes, right?

If yes, should I replace/mount pgsodium_root.key after installation on the secondary nodes and everything will work correctly? There won't be any problems with the generated keys from the root key as those will be replicated on the secondary nodes?

Wrong memory space allocated

Hi,

I have a memory warning when using PostgreSQL 15.1 compiled with --enabled-cassert (enables MEMORY_CONTEXT_CHECKING) and current HEAD of pgsodium. See:

nacl=# SELECT convert_from(
    pgsodium.crypto_aead_det_decrypt(
        decode('TA3aB8kpo4tZFbonlD6UPS3WeOMD6QxiAMDfWZ0bu+nkMtZQ', 'base64'), 
        '', 
        'acf73c9c-a1f3-473b-ae3a-9daff68f05fa'::uuid, 
        NULL::bytea), 
    'utf8'::name
);
WARNING:  problem in alloc set ExprContext: detected write past chunk end in block 0x20b29c0, chunk 0x20b2a10
WARNING:  problem in alloc set ExprContext: detected write past chunk end in block 0x20b29c0, chunk 0x20b2a10
 convert_from 
--------------
 blah
(1 row)

I might be wrong, but I suspect some missing header size or something related when allocating space in pgsodium_crypto_aead_det_encrypt_by_id and other equivalent funcs. Which means more data are written to in the allocated space than asked when the data AND its header are written there.

By the way, note that if I give NULL as additional data, it just crash:

nacl=# SELECT convert_from(
    pgsodium.crypto_aead_det_decrypt(
        decode('TA3aB8kpo4tZFbonlD6UPS3WeOMD6QxiAMDfWZ0bu+nkMtZQ', 'base64'), 
        NULL, 
        'acf73c9c-a1f3-473b-ae3a-9daff68f05fa'::uuid, 
        NULL::bytea), 
    'utf8'::name
);
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
The connection to the server was lost. Attempting reset: Failed.

The backtrace:

(gdb) bt
#0  0x0000000000b35976 in pg_detoast_datum (datum=0x0) at fmgr.c:1710
#1  0x00007fd48dd8d0df in pgsodium_crypto_aead_det_decrypt_by_id (fcinfo=0x2090440) at src/aead.c:304
[...]

The source code being:

pgsodium_crypto_aead_det_decrypt_by_id (PG_FUNCTION_ARGS)
{
	bytea      *ciphertext = PG_GETARG_BYTEA_P (0);
	bytea      *additional = PG_GETARG_BYTEA_P (1); // <- 304

Regards,

Better NULL input checking?

Reported by @ioguix in #43

By the way, note that if I give NULL as additional data, it just crash:

pgsodium.crypto_aead_det_decrypt was incorrectly not labeled STRICT so it crashed on NULL input, I can fix that, or I'm thinking it makes more sense for all functions (in most cases) to throw errors on NULL input instead of returning NULL, which means removing STRICT and doing NULL checking as shown in this branch:

https://github.com/michelp/pgsodium/compare/fix/better-null-checking?expand=1

this is just one example of many that would need to be added. Thoughts?

Incoherency between fields `pgsodium.key.status` and `pgsodium.key.expires`

Hi,

It seems the pgsodium.key.status field can be incoherent with the real status of the key when it expires:

=# select * from pgsodium.create_key(expires => 'yesterday');
 id | name | status | key_type | key_id | key_context | created | expires | associated_data 
----+------+--------+----------+--------+-------------+---------+---------+-----------------
    |      |        |          |        |             |         |         | 
(1 row)

=# select * from pgsodium.key \gx
-[ RECORD 1 ]---+-------------------------------------
id              | 77421bb0-2489-4b5e-8a05-9a7e49bcb778
status          | valid                          -- <=== created as valid
created         | 2023-02-08 20:56:00.987969+01
expires         | 2023-02-07 00:00:00+01         -- <=== is expired !
key_type        | aead-det
key_id          | 1
[...]

Moreover, actual result of pgsodium.create_key() might seem confusing as it returns an empty line from view pgsodium.valid_key whereas the key is actually created.

Where my PGXN Release?

Details here. Draft META.json for you (check the license; "ISC" is not known to PGXN):

{
   "name": "pgsodium",
   "abstract": "Postgres extension for libsodium functions",
   "description": "pgsodium is a PostgreSQL extension that exposes modern libsodium based cryptographic functions to SQL.",
   "version": "1.0.0",
   "maintainer": [
      "Michel Pelletier <[email protected]>"
   ],
   "license": "postgresql",
   "provides": {
      "pgsodium": {
         "abstract": "Postgres extension for libsodium functions",
         "file": "src/pgsodium.h",
         "docfile": "README.md",
         "version": "1.0.0"
      }
   },
   "prereqs": {
      "runtime": {
         "requires": {
            "PostgreSQL": "10.0.0"
         }
      }
   },
   "resources": {
      "bugtracker": {
         "web": "https://github.com/michelp/pgsodium/issues/"
      },
      "repository": {
         "url": "git://github.com/michelp/pgsodium.git",
         "web": "https://github.com/michelp/pgsodium/",
         "type": "git"
      }
   },
   "generated_by": "David E. Wheeler",
   "meta-spec": {
      "version": "1.0.0",
      "url": "https://pgxn.org/meta/spec.txt"
   },
   "tags": [
      "sodium",
      "crypto",
      "cryptography",
      "encryption",
      "random",
      "asymmetric encryption",
      "public key"
   ]
}

Error installing the pgsodium version 3.0.4

Error installing the pgsodium version 3.0.4 on PostgreSQL 14

psql (14.5 (Debian 14.5-2.pgdg110+2))
Type "help" for help.

postgres=# CREATE EXTENSION pgsodium;
ERROR:  relation "pgsodium.key" does not exist
LINE 1: CREATE EXTENSION pgsodium;

postgres=# select name, default_version as version, comment from pg_available_extensions where name like '%pgsodium%' order by 1;
   name   | version |                  comment                   
----------+---------+--------------------------------------------
 pgsodium | 3.0.4   | Postgres extension for libsodium functions
(1 row)

no problem installing the extension version 3.0.2

postgres=# select * from pg_available_extension_versions where name = 'pgsodium' order by version desc;
   name   | version | installed | superuser | trusted | relocatable | schema | requires |                  comment                   
----------+---------+-----------+-----------+---------+-------------+--------+----------+--------------------------------------------
 pgsodium | 3.0.4   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
 pgsodium | 3.0.3   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
 pgsodium | 3.0.2   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
 pgsodium | 3.0.0   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
 pgsodium | 2.0.2   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
 pgsodium | 2.0.1   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
 pgsodium | 2.0.0   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
 pgsodium | 1.2.0   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
 pgsodium | 1.1.1   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
 pgsodium | 1.1.0   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
 pgsodium | 1.0.0   | f         | t         | f       | f           |        |          | Postgres extension for libsodium functions
(11 rows)

postgres=# CREATE EXTENSION pgsodium WITH VERSION '3.0.3'; 
ERROR:  relation "pgsodium.key" does not exist
LINE 1: CREATE EXTENSION pgsodium WITH VERSION '3.0.3';
                     ^
postgres=# CREATE EXTENSION pgsodium WITH VERSION '3.0.2'; 
CREATE EXTENSION
postgres=# \dx
                               List of installed extensions
   Name   | Version |   Schema   |                       Description                       
----------+---------+------------+---------------------------------------------------------
 pgsodium | 3.0.2   | public     | Pgsodium is a modern cryptography library for Postgres.
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

Homomorphic encryption

Are the plans to add homomorphic encryption to this extension, so it's possible to sum up data without decrypting data first?

Error .. security label

While attempting to apply a security label to a table column

SECURITY LABEL FOR pgsodium ON COLUMN test.mka_sysadmin_crypt.paswd
 IS 'ENCRYPT WITH KEY ID 751013a0-21ee-4743-a5eb-692ca7f6e26d';

I encountered the following error:

[42601] ERROR: syntax error at or near "," Where: PL/pgSQL function pgsodium.create_mask_view(oid,integer,boolean) line 41 at EXECUTE SQL statement "SELECT pgsodium.create_mask_view(objoid, objsubid, debug) FROM pg_catalog.pg_seclabel sl WHERE sl.objoid = tar ...

Upon investigation, I traced the issue to the function decrypted_columns(relid oid) returns text and specifically to the line SET search_path="". It appears that assigning an empty string to search_path is not permissible.

And I am also unable to recompile this function due to the following error:

 [2BP01] ERROR: cannot drop function pgsodium.decrypted_columns(oid) because extension pgsodium requires it.

Postgresql version: PostgreSQL 16.2 (Debian 16.2-1.pgdg110+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

pgsodium version: 3.1.9

TCE : Update with using Old Value

Hello,

Looks like table gets corrupted when I'm trying to update an encrypted column using this column.
CREATE TABLE users (
id bigserial primary key,
secret text,
key_id uuid not null default 'e3496f2a-787f-45a0-9717-f648496179d1',
nonce bytea default pgsodium.crypto_aead_det_noncegen()
);

SECURITY LABEL FOR pgsodium
ON COLUMN users.secret
IS 'ENCRYPT WITH KEY COLUMN key_id NONCE nonce';

insert into users( secret ) values ( '12345' );

For now, everything is right
Then
update users set secret = secret || 'test' where id = 1;

At this stage, my secret has been corrupted and is not accessible anymore...
Do I miss anything ?

Thanks

Last Version on PGXN is 1.1

Hi there. Would you consider releasing updates of pgsodium on PGXN? The latest release there is currently v1.1.1, and it looks like there have been a few releases since then.

If you'd like to automate releasing it on PGXN and GitHub, here's how to do it with GitHub Actions.

Doesn't work well with on conflict Json

I have this code

 token_value := jsonb_build_object(
    'token', token,
    'workspace_name', workspace_name, 
    'user', user_email
  );
  
  INSERT INTO services_connected (workspace_id, service_name, data)
  VALUES (workspace_id_value, 'google', token_value)
  ON CONFLICT (workspace_id, service_name) DO UPDATE SET data = EXCLUDED.data;

this works fine but ON CONFLICT, the value stored is incorrect.

TCE not using specified key to encrypt on update

When I use pgsodium with a unique key_id for each row, encryption works well when I add a new record, and I can see the decrypted value in the associated view:

create table if not exists "schema"."secret" (
    "id" uuid DEFAULT uuid_generate_v4() NOT NULL PRIMARY KEY,
    "createdAt" timestamp with time zone DEFAULT "now"() NOT NULL,
    "updatedAt" timestamp with time zone DEFAULT "now"() NOT NULL,
    "lastUsed" timestamp with time zone,
    "name" text DEFAULT ''::text not null,
    "value" text DEFAULT ''::text not null,
    "key_id" uuid NOT NULL references pgsodium.key(id) default (pgsodium.create_key()).id,
    "nonce" bytea DEFAULT pgsodium.crypto_aead_det_noncegen(),
    "userId" uuid DEFAULT "auth"."uid"() references "auth"."users"("id")
);

SECURITY LABEL FOR pgsodium 
    ON COLUMN "schema"."secret"."value" 
    IS 'ENCRYPT WITH KEY COLUMN key_id ASSOCIATED (userId) NONCE nonce';

However, once I update a row in the 'secret' table, a NEW key is created in the pgsodium schema to encrypt the value, which is not subsequently updated in the key_id column. So after updating a record, I can no longer access its decrypted value from the associated view.

Is this a bug?

setting security label in two different tables fails

Hi everyone,

I have two similar tables, within both I want to encrypt the content of one column (in this example its the column is called secret_column in both tables).

CREATE TABLE table_name1 (
   id_1 uuid PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
   key_id_1 uuid NOT NULL REFERENCES pgsodium.key(id) DEFAULT (pgsodium.create_key()).id,
   nonce_1 bytea NOT NULL DEFAULT pgsodium.crypto_aead_det_noncegen(),
   secret_column_1 text NOT NULL DEFAULT 'undefined'::text
);

CREATE TABLE table_name2 (
   id_2 uuid PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
   key_id_2 uuid NOT NULL REFERENCES pgsodium.key(id) DEFAULT (pgsodium.create_key()).id,
   nonce_2 bytea NOT NULL DEFAULT pgsodium.crypto_aead_det_noncegen(),
   secret_column_2 text NOT NULL DEFAULT 'undefined'::text
);

SECURITY LABEL FOR pgsodium
  ON COLUMN table_name1.secret_column_1
  IS 'ENCRYPT WITH KEY COLUMN key_id_1 NONCE nonce_1 ASSOCIATED id_1';

SECURITY LABEL FOR pgsodium
  ON COLUMN table_name2.secret_column_2
  IS 'ENCRYPT WITH KEY COLUMN key_id_2 NONCE nonce_2 ASSOCIATED id_2';

I am able to set the security labels for each table separately, but If I want to add both, it fails, giving me the error:

Failed to run sql query: must be owner of relation table_name1

I am new to this topic and looked up every source I can find online, but none could solve my problem.
I really appreciate your help!

Thank you in advance
Tim :-)

Update secret when updating associated data

Any time associated data is updated,the secret must also be updated. Add triggers for associted columns that also re-encrypt secret with new association.

Updated value in secret column does not get encrypted

Hi everyone,

following my setup in #73, I have a secret value stored in secret_column, which is encrypted at insert.
Now, when updating the value, I expect it to be encrypted again, but it won't. Edit: It is stored as clear text.

Is this behavior expected, or is this, again, an issue I have with supabase?

Kind regards
Tim

edit: I added the database setup scripts in my comment below

Dockerfile needs updation. Image isnt getting generated

docker build .

[+] Building 196.0s (13/30)
=> [internal] load build definition from Dockerfile 0.0s
=> => transferring dockerfile: 2.32kB 0.0s
=> [internal] load .dockerignore 0.0s
=> => transferring context: 108B 0.0s
=> [internal] load metadata for docker.io/library/ubuntu:latest 0.7s
=> [ 1/26] FROM docker.io/library/ubuntu:latest@sha256:3f85b7caad41a95462cf5b787d8a04604c8262cdcdf9a472b8c52ef83375fe15 0.0s
=> [internal] load build context 0.0s
=> => transferring context: 4.91kB 0.0s
=> CACHED [ 2/26] RUN apt-get update && apt-get install -y make cmake git curl build-essential m4 sudo gdbserver gdb libreadline-dev bison flex zlib1g- 0.0s
=> CACHED [ 3/26] RUN groupadd -r postgres && useradd --no-log-init -r -m -s /bin/bash -g postgres -G sudo postgres 0.0s
=> CACHED [ 4/26] RUN /bin/rm -Rf "/home/postgres/data" && mkdir "/home/postgres/data" 0.0s
=> CACHED [ 5/26] WORKDIR /home/postgres 0.0s
=> [ 6/26] RUN git clone --branch REL_16_3 https://github.com/postgres/postgres.git --depth=1 && cd postgres && ./configure --prefix=/usr/ --enabl 60.6s
=> [ 7/26] RUN chown postgres:postgres /home/postgres 0.3s
=> [ 8/26] RUN curl -s -L https://github.com/theory/pgtap/archive/v1.2.0.tar.gz | tar zxvf - && cd pgtap-1.2.0 && make && make install 1.6s
=> ERROR [ 9/26] RUN curl -s -L https://download.libsodium.org/libsodium/releases/libsodium-1.0.18.tar.gz | tar zxvf - && cd libsodium-1.0.18 && ./configure 132.7s

[ 9/26] RUN curl -s -L https://download.libsodium.org/libsodium/releases/libsodium-1.0.18.tar.gz | tar zxvf - && cd libsodium-1.0.18 && ./configure && make check && make -j 4 install:
#0 132.7
#0 132.7 gzip: stdin: unexpected end of file
#0 132.7 tar: Child returned status 1
#0 132.7 tar: Error is not recoverable: exiting now

Dockerfile:29

27 |
28 | RUN curl -s -L https://github.com/theory/pgtap/archive/v1.2.0.tar.gz | tar zxvf - && cd pgtap-1.2.0 && make && make install
29 | >>> RUN curl -s -L https://download.libsodium.org/libsodium/releases/libsodium-1.0.18.tar.gz | tar zxvf - && cd libsodium-1.0.18 && ./configure && make check && make -j 4 install
30 | RUN cpan App::cpanminus && cpan TAP::Parser::SourceHandler::pgTAP && cpan App::prove
31 |

ERROR: failed to solve: process "/bin/sh -c curl -s -L https://download.libsodium.org/libsodium/releases/libsodium-1.0.18.tar.gz | tar zxvf - && cd libsodium-1.0.18 && ./configure && make check && make -j 4 install" did not complete successfully: exit code: 2

Help section needed - Backup/Restore of database

Hi,
could someone add a help section explaining how backup and restore of data is supposed to work.
Imagine a situation that we want to transfer a database from one server to another, is there even a way to do that. As I understand it, both servers would have to have the same master key right? But what if those servers must have different master keys.

Or is there some way to define masterkey while restoring data to new server?

Thank you

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

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.

docker pre-built image

i would like to suggest having a docker image that is pre-built with pgsodium. it would be easier to play with.

Failing pgtap Tests

The log output for the github action tests indicate several failures, although this not reflected in the final job status (passing). The failed tests for the latest run on main (https://github.com/michelp/pgsodium/actions/runs/4045434666):

postgresql 13

not ok 9 - There should be the correct schemas
# Failed test 9: "There should be the correct schemas"
#     Missing schemas:
#         pgsodium
#         pgsodium_masks

postgresql 14

not ok 9 - There should be the correct schemas
# Failed test 9: "There should be the correct schemas"
#     Missing schemas:
#         pgsodium
#         pgsodium_masks
not ok 104 - Role pg_read_all_data should be granted no privileges on table pgsodium.key
# Failed test 104: "Role pg_read_all_data should be granted no privileges on table pgsodium.key"
#     Extra privileges:
#         SELECT
not ok 105 - Role pg_write_all_data should be granted no privileges on table pgsodium.key
# Failed test 105: "Role pg_write_all_data should be granted no privileges on table pgsodium.key"
#     Extra privileges:
#         DELETE
#         INSERT
#         UPDATE
not ok 179 - Role pg_read_all_data should be granted no privileges on table pgsodium.decrypted_key
# Failed test 179: "Role pg_read_all_data should be granted no privileges on table pgsodium.decrypted_key"
#     Extra privileges:
#         SELECT
not ok 180 - Role pg_write_all_data should be granted no privileges on table pgsodium.decrypted_key
# Failed test 180: "Role pg_write_all_data should be granted no privileges on table pgsodium.decrypted_key"
#     Extra privileges:
#         DELETE
#         INSERT
#         UPDATE
not ok 224 - Role pg_read_all_data should be granted no privileges on table pgsodium.mask_columns
# Failed test 224: "Role pg_read_all_data should be granted no privileges on table pgsodium.mask_columns"
#     Extra privileges:
#         SELECT
not ok 225 - Role pg_write_all_data should be granted no privileges on table pgsodium.mask_columns
# Failed test 225: "Role pg_write_all_data should be granted no privileges on table pgsodium.mask_columns"
#     Extra privileges:
#         DELETE
#         INSERT
#         UPDATE
not ok 293 - Role pg_read_all_data should be granted no privileges on table pgsodium.masking_rule
# Failed test 293: "Role pg_read_all_data should be granted no privileges on table pgsodium.masking_rule"
#     Extra privileges:
#         SELECT
not ok 294 - Role pg_write_all_data should be granted no privileges on table pgsodium.masking_rule
# Failed test 294: "Role pg_write_all_data should be granted no privileges on table pgsodium.masking_rule"
#     Extra privileges:
#         DELETE
#         INSERT
#         UPDATE
not ok 347 - Role pg_read_all_data should be granted no privileges on table pgsodium.valid_key
# Failed test 347: "Role pg_read_all_data should be granted no privileges on table pgsodium.valid_key"
#     Extra privileges:
#         SELECT
not ok 348 - Role pg_write_all_data should be granted no privileges on table pgsodium.valid_key
# Failed test 348: "Role pg_write_all_data should be granted no privileges on table pgsodium.valid_key"
#     Extra privileges:
#         DELETE
#         INSERT
#         UPDATE
not ok 362 - Role pg_read_all_data should be granted no privileges on sequence pgsodium.key_key_id_seq
# Failed test 362: "Role pg_read_all_data should be granted no privileges on sequence pgsodium.key_key_id_seq"
#     Extra privileges:
#         SELECT
not ok 363 - Role pg_write_all_data should be granted no privileges on sequence pgsodium.key_key_id_seq
# Failed test 363: "Role pg_write_all_data should be granted no privileges on sequence pgsodium.key_key_id_seq"
#     Extra privileges:
#         UPDATE

postgresql 15

not ok 9 - There should be the correct schemas
# Failed test 9: "There should be the correct schemas"
#     Missing schemas:
#         pgsodium
#         pgsodium_masks
not ok 12 - Schema public should be owned by postgres
# Failed test 12: "Schema public should be owned by postgres"
#         have: pg_database_owner
#         want: postgres
not ok 104 - Role pg_read_all_data should be granted no privileges on table pgsodium.key
# Failed test 104: "Role pg_read_all_data should be granted no privileges on table pgsodium.key"
#     Extra privileges:
#         SELECT
not ok 105 - Role pg_write_all_data should be granted no privileges on table pgsodium.key
# Failed test 105: "Role pg_write_all_data should be granted no privileges on table pgsodium.key"
#     Extra privileges:
#         DELETE
#         INSERT
#         UPDATE
not ok 180 - Role pg_read_all_data should be granted no privileges on table pgsodium.decrypted_key
# Failed test 180: "Role pg_read_all_data should be granted no privileges on table pgsodium.decrypted_key"
#     Extra privileges:
#         SELECT
not ok 181 - Role pg_write_all_data should be granted no privileges on table pgsodium.decrypted_key
# Failed test 181: "Role pg_write_all_data should be granted no privileges on table pgsodium.decrypted_key"
#     Extra privileges:
#         DELETE
#         INSERT
#         UPDATE
not ok 226 - Role pg_read_all_data should be granted no privileges on table pgsodium.mask_columns
# Failed test 226: "Role pg_read_all_data should be granted no privileges on table pgsodium.mask_columns"
#     Extra privileges:
#         SELECT
not ok 227 - Role pg_write_all_data should be granted no privileges on table pgsodium.mask_columns
# Failed test 227: "Role pg_write_all_data should be granted no privileges on table pgsodium.mask_columns"
#     Extra privileges:
#         DELETE
#         INSERT
#         UPDATE
not ok 296 - Role pg_read_all_data should be granted no privileges on table pgsodium.masking_rule
# Failed test 296: "Role pg_read_all_data should be granted no privileges on table pgsodium.masking_rule"
#     Extra privileges:
#         SELECT
not ok 297 - Role pg_write_all_data should be granted no privileges on table pgsodium.masking_rule
# Failed test 297: "Role pg_write_all_data should be granted no privileges on table pgsodium.masking_rule"
#     Extra privileges:
#         DELETE
#         INSERT
#         UPDATE
not ok 351 - Role pg_read_all_data should be granted no privileges on table pgsodium.valid_key
# Failed test 351: "Role pg_read_all_data should be granted no privileges on table pgsodium.valid_key"
#     Extra privileges:
#         SELECT
not ok 352 - Role pg_write_all_data should be granted no privileges on table pgsodium.valid_key
# Failed test 352: "Role pg_write_all_data should be granted no privileges on table pgsodium.valid_key"
#     Extra privileges:
#         DELETE
#         INSERT
#         UPDATE
not ok 367 - Role pg_read_all_data should be granted no privileges on sequence pgsodium.key_key_id_seq
# Failed test 367: "Role pg_read_all_data should be granted no privileges on sequence pgsodium.key_key_id_seq"
#     Extra privileges:
#         SELECT
not ok 368 - Role pg_write_all_data should be granted no privileges on sequence pgsodium.key_key_id_seq
# Failed test 368: "Role pg_write_all_data should be granted no privileges on sequence pgsodium.key_key_id_seq"
#     Extra privileges:
#         UPDATE

test

project test

installation rpm pgsodium

Hi ,
I try to install pgsodium with rpm on Redhat 8.X (pgsodium_14-3.1.5-1.rhel8.x86_64) .
=> postgresql.conf,
shared_preload_libraries='pgsodium'
When postgres restart , we have this error : "The getkey script "/usr/pgsql-14/share/extension/pgsodium_getkey" does not exists" .
ls -l /usr/pgsql-14/share/extension/pgso*
-rw-r--r--. 1 root root 6275 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--1.0.0--1.1.0.sql
-rw-r--r--. 1 root root 4606 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--1.0.0.sql
-rw-r--r--. 1 root root 59 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--1.1.0--1.1.1.sql
-rw-r--r--. 1 root root 6731 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--1.1.1--1.2.0.sql
-rw-r--r--. 1 root root 7076 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--1.2.0--2.0.0.sql
-rw-r--r--. 1 root root 0 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--2.0.0--2.0.1.sql
-rw-r--r--. 1 root root 0 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--2.0.1--2.0.2.sql
-rw-r--r--. 1 root root 16256 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--2.0.2--3.0.0.sql
-rw-r--r--. 1 root root 271 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--3.0.0--3.0.2.sql
-rw-r--r--. 1 root root 150 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--3.0.2--3.0.3.sql
-rw-r--r--. 1 root root 2452 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--3.0.3--3.0.4.sql
-rw-r--r--. 1 root root 28466 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--3.0.4--3.0.5.sql
-rw-r--r--. 1 root root 2698 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--3.0.5--3.0.6.sql
-rw-r--r--. 1 root root 5245 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--3.0.6--3.0.7.sql
-rw-r--r--. 1 root root 17568 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--3.0.7--3.1.0.sql
-rw-r--r--. 1 root root 4542 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--3.1.0--3.1.1.sql
-rw-r--r--. 1 root root 223 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--3.1.1--3.1.2.sql
-rw-r--r--. 1 root root 364 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--3.1.2--3.1.3.sql
-rw-r--r--. 1 root root 96 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--3.1.3--3.1.4.sql
-rw-r--r--. 1 root root 2655 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium--3.1.4--3.1.5.sql
-rw-r--r--. 1 root root 140 Jan 4 23:14 /usr/pgsql-14/share/extension/pgsodium.control

ls /usr/pgsql-14/share/extension/pgsodium_getkey
ls: cannot access '/usr/pgsql-14/share/extension/pgsodium_getkey': No such file or directory

Thanks
Sylvie Halat

ERROR: pgsodium_derive_helper: pgsodium_derive: no server secret key defined

Hi
when i call select pgsodium.derive_key(1); getting an error ERROR: pgsodium_derive_helper: pgsodium_derive: no server secret key defined.
I have added shared_preload_libraries = 'pgsodium'
and pgsodium.server_secret_key = '696fe962615267e8d65adad896ad12d996921fbbaf3fe8e56573bec1c941ca90' in postgresql.conf file
How can i solve the issue?
pgsodium.version-3.1.8

bug in pgsodium.valid_key view

Hello,

First of all, thank you for releasing this very nice project!

I'd like to report a small error in the definition of the view pgsodium.valid_key:

ELSE key.expires < now()

should be replaced by

ELSE key.expires > now()

(unless I missed something)

Best regards,

TCE: triggers recreated twice per column

Triggers get recreated twice per column due to loose masking view query.

Does this not support custom domains?

I was getting some errors I didn't understand and finally changed my column type to a native TEXT and it worked. The original setup was something akin to this:

CREATE DOMAIN EMAIL AS TEXT 
  CONSTRAINT "must be a valid email address" 
    CHECK (VALUE ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$');

CREATE TABLE test (
  id XUID PRIMARY KEY NOT NULL
  ,email EMAIL NOT NULL
  ,sodium_key_id UUID NOT NULL REFERENCES pgsodium.key(id) DEFAULT (pgsodium.create_key()).id
  ,sodium_nonce BYTEA NOT NULL DEFAULT pgsodium.crypto_aead_det_noncegen()
);

SECURITY LABEL FOR pgsodium ON COLUMN test.email IS 'ENCRYPT WITH KEY COLUMN sodium_key_id';

adding the security label yields this error:

Query 1 ERROR at Line 1: : ERROR:  syntax error at or near ","
LINE 6: ,       
        ^
QUERY:  
    DROP VIEW IF EXISTS public.decrypted_test;
    CREATE VIEW public.decrypted_test  AS SELECT 
                id,       
        email,
,       
        sodium_key_id,       
        sodium_nonce
    FROM public.test;
    ALTER VIEW public.decrypted_test OWNER TO insurance;
    
CONTEXT:  PL/pgSQL function pgsodium.create_mask_view(oid,integer,boolean) line 38 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(r.objid)"
PL/pgSQL function pgsodium.trg_mask_update() line 27 at PERFORM

When I switch the type from EMAIL to TEXT, it works fine.

Port pgsodium to Windows

I have ported pgsodium to Windows. It need some minor changes in order to build. It needs plenty of clean-up in order to be in such a state that it can be upstreamed.

Do these changes have a chance of being accepted? Or would you prefer to keep Windows-support out?

Grant `REFERENCES` on `pgsodium.key` to pgsodium_keyiduser?

Hi,

While thinking about #73, I wonder if pgsodium_keyiduser should be granted REFERENCES on pgsodium.key by default?

Thoughtq?

Postgres crash running tests on latest master

...
ok 32 - secretbox_open session key
ok 33 - secretbox_open session key
ok 34 - sha256
ok 35 - sha512
ok 36 - hmac512 verified
ok 37 - hmac512 not verified
ok 38 - hmac256 verified
ok 39 - hmac256 not verified
psql:test/test.sql:275: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:test/test.sql:275: fatal: connection to server was lost
marc:pgsodium$

Command: psql -d test -f test/test.sql
(database is newly created for the tests)

Postgres version:
marc:pgsodium$ psql
psql (12.3 (Debian 12.3-1.pgdg90+1))

__
Marc

michelp / pgsodium Goto Github PK

pgsodium's People

Contributors

Stargazers

Watchers

Forkers

pgsodium's Issues

docker build .

Dockerfile:29

postgresql 13

postgresql 14

postgresql 15

Recommend Projects

Recommend Topics

Recommend Org