Re: Add default role 'pg_access_server_files' - Mailing list pgsql-hackers

Hi Stephen,

I have another question then based on what you said earlier today, and some testing I did using your patch.

TLDR: I created a role "tester" and was (as expected) not able to perform pg_read_file() on files outside the data
directory.
But then I granted EXECUTE on that function for that role, and then I was able to (which is not what I expected).

Here's what I did (I apologize if this is too verbose):

* I successfully applied your patch to HEAD, and built Postgres from source:

    make clean
    configure (with options including a specific --prefix)
    make
    make install

* Then I went into the "install_dir/bin" and did the following to setup a data directory:

    $ ./initdb ~/sql-data/2018-01-06
    The files belonging to this database system will be owned by user "postgres".
    This user must also own the server process.

    The database cluster will be initialized with locale "en_US.UTF-8".
    The default database encoding has accordingly been set to "UTF8".
    The default text search configuration will be set to "english".

    Data page checksums are disabled.

    creating directory /Users/postgres/sql-data/2018-01-06 ... ok
    creating subdirectories ... ok
    selecting default max_connections ... 100
    selecting default shared_buffers ... 128MB
    selecting dynamic shared memory implementation ... posix
    creating configuration files ... ok
    running bootstrap script ... ok
    performing post-bootstrap initialization ... ok
    syncing data to disk ... ok

    WARNING: enabling "trust" authentication for local connections
    You can change this by editing pg_hba.conf or using the option -A, or
    --auth-local and --auth-host, the next time you run initdb.

    Success. You can now start the database server using:

        ./pg_ctl -D /Users/postgres/sql-data/2018-01-06 -l logfile start

* Then I started the database:

    $ ./pg_ctl -D /Users/postgres/sql-data/2018-01-06 -l logfile start
    waiting for server to start.... done
    server started

* I went into the database and tried a pg_read_file:

    $ psql postgres
    psql (9.4.5, server 11devel)
    Type "help" for help.

    postgres=# select pg_read_file('/Users/postgres/temp');
                           pg_read_file                        
    -----------------------------------------------------------
     here is the file content
     
    (1 row)

* Of course that worked as superuser, so created a new role:

    postgres=# create role tester;
    CREATE ROLE
    postgres=# \q
    postgres=# alter role tester with login;
    ALTER ROLE
    postgres=# \q

    $ psql postgres tester
    psql (9.4.5, server 11devel)
    Type "help" for help.

    postgres=> select pg_read_file('/Users/postgres/temp');
    ERROR:  permission denied for function pg_read_file
    postgres=> \q

* My current understanding at this point is that EXECUTE permissions would only allow "tester" to pg_read_file() on
filesin the data directory.  I try GRANTing EXECUTE:
 

    $ psql postgres
    psql (9.4.5, server 11devel)
    Type "help" for help.

    postgres=# grant execute on function pg_read_file(text) to tester;
    GRANT
    postgres=# select pg_read_file('/Users/postgres/temp');
                           pg_read_file                        
    -----------------------------------------------------------
     here is the file content
     
    (1 row)



Is this expected behavior?  I thought I would need to GRANT that new "pg_access_server_files" role to "tester" in order
todo this.  I may have misunderstood how your new feature works, just doublechecking.
 

Regards,
Ryan