Обсуждение: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file?

Hi,
Tested it now on built today Pg 16devel, straight from repo.

In docs (https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-GENFILE), I found:
> The functions shown in Table 9.99 provide native access to files on
> the machine hosting the server. Only files within the database cluster
> directory and the log_directory can be accessed, unless the user is
> a superuser or is granted the role pg_read_server_files. Use
> a relative path for files in the cluster directory, and a path
> matching the log_directory configuration setting for log files.

which I understand that if I'll grant pg_read_server_files to some user,
then this user should be able to use the generic file access functions.

So I tried:

#v+
$ select current_user;
 current_user
──────────────
 test
(1 row)

$ \du+ test
                                            List of roles
 Role name │ Attributes │                           Member of                           │ Description
───────────┼────────────┼───────────────────────────────────────────────────────────────┼─────────────
 test      │            │ {pg_read_all_settings,pg_read_all_stats,pg_read_server_files} │

$ select * from pg_ls_dir('.');
ERROR:  permission denied for function pg_ls_dir

$ select * from pg_ls_dir('log');
ERROR:  permission denied for function pg_ls_dir

$ select * from pg_ls_dir('/etc');
ERROR:  permission denied for function pg_ls_dir

$ select * from pg_read_file('PG_VERSION');
ERROR:  permission denied for function pg_read_file

$ select * from pg_read_file('/etc/passwd');
ERROR:  permission denied for function pg_read_file
#v-

After I additionally granted EXECUTE on the functions, I was able to run them, even when I revoked
pg_read_server_files.

Am I missing something in there, or is this a bug?

Best regards,

depesz

At Tue, 14 Mar 2023 20:33:10 +0100, hubert depesz lubaczewski <depesz@depesz.com> wrote in 
> Hi,
> Tested it now on built today Pg 16devel, straight from repo.
> 
> In docs (https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-GENFILE), I found:
> > The functions shown in Table 9.99 provide native access to files on
> > the machine hosting the server. Only files within the database cluster
> > directory and the log_directory can be accessed, unless the user is
> > a superuser or is granted the role pg_read_server_files. Use
> > a relative path for files in the cluster directory, and a path
> > matching the log_directory configuration setting for log files.
> 
> which I understand that if I'll grant pg_read_server_files to some user,
> then this user should be able to use the generic file access functions.

> $ select * from pg_ls_dir('.');
> ERROR:  permission denied for function pg_ls_dir

"GRANT EXECUTE ON FUNCTION pg_ls_dir(text) TO test" might work for you.

The doc section you are referring to also describes that on a
per-function basis like this.

> This function is restricted to superusers by default, but other
> users can be granted EXECUTE to run the function.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

On Wed, Mar 15, 2023 at 11:10:11AM +0900, Kyotaro Horiguchi wrote:
> At Tue, 14 Mar 2023 20:33:10 +0100, hubert depesz lubaczewski <depesz@depesz.com> wrote in 
> > Hi,
> > Tested it now on built today Pg 16devel, straight from repo.
> > 
> > In docs (https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-GENFILE), I found:
> > > The functions shown in Table 9.99 provide native access to files on
> > > the machine hosting the server. Only files within the database cluster
> > > directory and the log_directory can be accessed, unless the user is
> > > a superuser or is granted the role pg_read_server_files. Use
> > > a relative path for files in the cluster directory, and a path
> > > matching the log_directory configuration setting for log files.
> > 
> > which I understand that if I'll grant pg_read_server_files to some user,
> > then this user should be able to use the generic file access functions.
> 
> > $ select * from pg_ls_dir('.');
> > ERROR:  permission denied for function pg_ls_dir
> 
> "GRANT EXECUTE ON FUNCTION pg_ls_dir(text) TO test" might work for you.
> 
> The doc section you are referring to also describes that on a
> per-function basis like this.

OK, I get it, but then, what is the point of mentioning
pg_read_server_files in the doc? I can just as well grant execute on the
functions to someone. And, I tested, they don't need to be in
pg_read_server_files.

Best regards,

depesz

At Wed, 15 Mar 2023 11:46:17 +0100, hubert depesz lubaczewski <depesz@depesz.com> wrote in 
> On Wed, Mar 15, 2023 at 11:10:11AM +0900, Kyotaro Horiguchi wrote:
> > At Tue, 14 Mar 2023 20:33:10 +0100, hubert depesz lubaczewski <depesz@depesz.com> wrote
> 
> OK, I get it, but then, what is the point of mentioning
> pg_read_server_files in the doc? I can just as well grant execute on the
> functions to someone. And, I tested, they don't need to be in
> pg_read_server_files.

I believe you can confirm that the non-superuser test cannot access
/etc without the pg_read_server_files privilege.

> postgres=> select * from pg_ls_dir('/etc');
> ERROR:  absolute path not allowed
> postgres=> select * from pg_ls_dir('../../');
> ERROR:  path must be in or below the current directory
("current" directory...)

Although the order of the descriptions may seem reversed, the doc
clearly states that pg_read_server_files is responsible for
controlling access outside the database cluster directory and the
log_directory for non-superusers, while GRANT EXECUTE regulates
executability for non-superusers.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

On Thu, Mar 16, 2023 at 10:11:04AM +0900, Kyotaro Horiguchi wrote:
> At Wed, 15 Mar 2023 11:46:17 +0100, hubert depesz lubaczewski <depesz@depesz.com> wrote in 
> > On Wed, Mar 15, 2023 at 11:10:11AM +0900, Kyotaro Horiguchi wrote:
> > > At Tue, 14 Mar 2023 20:33:10 +0100, hubert depesz lubaczewski <depesz@depesz.com> wrote
> > 
> > OK, I get it, but then, what is the point of mentioning
> > pg_read_server_files in the doc? I can just as well grant execute on the
> > functions to someone. And, I tested, they don't need to be in
> > pg_read_server_files.
> 
> I believe you can confirm that the non-superuser test cannot access
> /etc without the pg_read_server_files privilege.
> 
> > postgres=> select * from pg_ls_dir('/etc');
> > ERROR:  absolute path not allowed
> > postgres=> select * from pg_ls_dir('../../');
> > ERROR:  path must be in or below the current directory
> ("current" directory...)
> 
> Although the order of the descriptions may seem reversed, the doc
> clearly states that pg_read_server_files is responsible for
> controlling access outside the database cluster directory and the
> log_directory for non-superusers, while GRANT EXECUTE regulates
> executability for non-superusers.

Ok, tested. You're right. So it was my misunderstanding. Could be that
the docs could use some clarification, but otoh, it could be just as
well that I suck at understanding :)

Thanks a lot for the info.

Best regards,

depesz