Revoking access to specific columns not working in postgresql on table pg_proc

Question

My goal is to only enable a specific user to execute functions in a specific schema, list the functions available by name but not see the source code of the function or list other schema.

It is possible to achieve the above without the ability to list the available function names via carrying out the following:

First create a test user role:

CREATE ROLE test_user WITH LOGIN PASSWORD 'secret';

Now revoke all permissions from the public on all schemas:

REVOKE ALL PRIVILEGES ON DATABASE test_db FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA function_schema FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA function_schema FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA function_schema FROM PUBLIC;
REVOKE ALL ON SCHEMA function_schema FROM PUBLIC;

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA table_schema FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA table_schema FROM PUBLIC;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA table_schema FROM PUBLIC;
REVOKE ALL ON SCHEMA table_schema FROM PUBLIC;

Setup restricted access for test user:

GRANT CONNECT ON DATABASE test_db TO test_user;
GRANT USAGE ON SCHEMA function_schema TO test_user;
REVOKE ALL ON SCHEMA public FROM test_user;
REVOKE ALL ON SCHEMA table_schema FROM test_user;
GRANT EXECUTE ON FUNCTION function_schema.function1() TO test_user;
GRANT EXECUTE ON FUNCTION function_schema.function2(integer) TO test_user;

Now to hid the schema structure and code form the test users and public:

REVOKE SELECT ON TABLE pg_proc FROM public;
REVOKE SELECT ON TABLE pg_proc FROM test_user;

This all works well, the test user can execute the functions but they can't see the code inside the functions, nor can they see the schema and table structure.

--

I'd like to allow the test user to now see the functions by name in the test_functions schema.I've tried the following according to GRANT Postgresql 9.3 (this is granting select on every column in pg_proc):

GRANT SELECT (proname,pronamespace,proowner,prolang,procost,prorows,
provariadic,protransform,proisagg,proiswindow,prosecdef,proleakproof,
proisstrict,proretset,provolatile,pronargs,pronargdefaults,prorettype,
proargtypes,proallargtypes,proargmodes,proargnames,proargdefaults,prosrc,
probin,proconfig,proacl) ON TABLE pg_proc TO test_user;

The result here is that the test user does not get all the same select permissions as if they had access to the whole table. They still can't see the function names.

Another test was to do the reverse, grant select to the table then revoke select on all columns accouding to REVOKE postgresql 9.3:

GRANT SELECT ON TABLE pg_proc TO test_user;

REVOKE SELECT (proname,pronamespace,proowner,prolang,procost,prorows,
provariadic,protransform,proisagg,proiswindow,prosecdef,proleakproof,
proisstrict,proretset,provolatile,pronargs,pronargdefaults,prorettype,
proargtypes,proallargtypes,proargmodes,proargnames,proargdefaults,prosrc,
probin,proconfig,proacl) ON TABLE pg_proc FROM test_user;

Again this doesn't work, they now can see all schemas, code and tables (on allowed schemas).

It appears that the grant/revoke on specific columns doesn't work the way the documentation suggests.

Searching extensively yielded How to restrict access to code in a function wich suggests revoking access to only the pg_proc.prosrc column which clearly doesn't work from the tests above.

I'm using postgresql 9.3

Please feel free to suggest any other solution that comes to mind.

score 3 · Answer 1 · answered Mar 16 '14 at 02:15

Working with column permissions should work but is probably not the best approach here. Maintaining privileges in this way is rather burdensome in PostgreSQL. Using this approach you have to set and track your security policy for individual users on all affected system catalog relations. Just imagine having to update your security policy when you have more than a few users.

Rather than applying detailed security roles on relations, I suggest that you lock everything in the system catalogs down (as you did) and then create views to selectively expose parts of the system catalogs (this is already the case in the standard setup, but apparently not strict enough for your case). GRANT SELECT on those views to a group role and then grant that group role to login roles (which should have the INHERIT property). This way it is much easier to track what you have done and to update your security policy as all policy is contained in a set of views (content) and group roles (accessibility). If you are using PostgreSQL-9.2+ check out the with security-barrier option on the views as this will prevent optimizer-spoofing by malicious users.

Revoking access to specific columns not working in postgresql on table pg_proc

1 Answers1