Re: Add default role 'pg_access_server_files'

Greetings Ryan!

* Ryan Murphy (ryanfmurphy@gmail.com) wrote:
> Stephen, so far I've read thru your patch and familiarized myself with some of the auth functionality in pg_authid.h
andsrc/backend/utils/adt/acl.c 
>
> The only question I have so far about your patch is the last several hunks of the diff, which remove superuser checks
withoutadding anything immediately obvious in their place: 

Ah, I realize it's not immediately obvious, but they *are* replaced by
something else- REVOKE statements in the "system_views.sql" file in
src/backend/catalog:

REVOKE EXECUTE ON FUNCTION pg_read_file(text) FROM public;
REVOKE EXECUTE ON FUNCTION pg_read_file(text,bigint,bigint) FROM public;
REVOKE EXECUTE ON FUNCTION pg_read_file(text,bigint,bigint,boolean) FROM public;

REVOKE EXECUTE ON FUNCTION pg_read_binary_file(text) FROM public;
REVOKE EXECUTE ON FUNCTION pg_read_binary_file(text,bigint,bigint) FROM public;
REVOKE EXECUTE ON FUNCTION pg_read_binary_file(text,bigint,bigint,boolean) FROM public;

REVOKE EXECUTE ON FUNCTION pg_stat_file(text) FROM public;
REVOKE EXECUTE ON FUNCTION pg_stat_file(text,boolean) FROM public;

That script is run as part of 'initdb' to set things up in the system.
By issueing those REVOKE statements, no one but the cluster owner (a
superuser) is able to run those functions- unless a superuser decides
that it's ok for others to run them, in which case they would run:

GRANT EXECUTE ON FUNCTION pg_read_file(text) TO myuser;

> I wanted to ask if you have reason to believe that these checks were not necessary (and therefore can be deleted
insteadof replaced by is_member_of_role() checks like you did elsewhere).  I still have limited understanding of the
overallcode, so really just asking because it's the first thing that jumped out. 

The places where is_member_of_role() is being used are cases where it's
not possible to use the GRANT system.  For example, we don't have a way
to say "GRANT read-files-outside-the-data-directory TO role1;" in the
normal GRANT system, and so a default role is added to allow that
specific right to be GRANT'd to non-superuser.

One would need to have both the default role AND EXECUTE rights on the
function to be able to, say, run:

SELECT pg_read_file('/data/load_area/load_file');

With just EXECUTE on the function, they could use pg_read_file() to read
files under the data directory but not elsewhere on the system, which
may be overly limiting for some use-cases.

Of course, all of these functions allow a great deal of access to the
system and that's why they started out being superuser-only.
Administrators will need to carefully consider who, if anyone, should
have the level of access which these functions and default roles
provide.

Thanks!

Stephen