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 files in 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 to do this. I may have
misunderstood how your new feature works, just doublechecking.
Regards,
Ryan
