Thread: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file?
pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file?
From
hubert depesz lubaczewski
Date:
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
Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file?
From
Kyotaro Horiguchi
Date:
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
Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file?
From
hubert depesz lubaczewski
Date:
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
Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file?
From
Kyotaro Horiguchi
Date:
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
Re: pg_read_server_files doesn't let me use pg_ls_dir() or pg_read_file?
From
hubert depesz lubaczewski
Date:
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