Using Postgres PGCrypto encryption requires superuser to run view queries

Question

Using: Postgres 9, CentOS 7, Postgres Data directory not in default location but used RSync to make sure permissions were correct. And yes appropriate .config files were changed.

When I try to query a view containing an encrypted item as a NON superuser (Testuser), I get this error:

ERROR: must be superuser to read files CONTEXT: PL/pgSQL function decrypt_data(bytea) line 13 at assignment

If I run that same query using POSTGRES superuser, the query completes fine.

This seems to be a file system read permission error when trying to read the Key files. Everything I see using encryption seem to not mention how to run without being superuser.

I have already run the following grants for Testuser:

GRANT ALL PRIVILEGES ON DATABASE xxx_db to Testuser;
GRANT SELECT ON ALL TABLES IN SCHEMA xxxxx TO Testuser;
GRANT ALL ON ALL TABLES IN SCHEMA xxxxx TO Testuser;

The test user can create tables, views, basically anything within that db.. just not read encryption keys.

The permissions on the keys are 775 right now, I even tried 777 without luck.

Any Ideas?

Answer 1

pgcrypto is a PostgreSQL extension described here: https://www.postgresql.org/docs/current/static/pgcrypto.html

but it doesn't provide a decrypt_data(bytea) function.

This function seems to be custom code that happens to open a server-side file, with pg_read_file() or a similar method.

These methods are restricted to superusers to avoid normal users to read on the server's filesystem, no matter what are the Unix rights of the particular file they want to read.

You can verify this in the source of decrypt_data(bytea), which can be obtained with:

select pg_get_functiondef('decrypt_data(bytea)'::regprocedure);

or \df+ decrypt_data(bytea) from within psql.

Answer 2

I found the issue. I need to grant the user with function permissions.

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA xxxxx TO yyyyyyyyy;