From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chema <chema@interneta.org>
cc: pgsql-general@lists.postgresql.org
Subject: Re: "permission denied to COPY to or from an external program" even with GRANT pg_execute_server_program
In-reply-to: <CALdEsqOM2q7bXt1Bo2S_41nHgSjR2_epzm=aHHFoHsQdzB3fqQ@mail.gmail.com>
References: <CALdEsqOM2q7bXt1Bo2S_41nHgSjR2_epzm=aHHFoHsQdzB3fqQ@mail.gmail.com>
Comments: In-reply-to Chema <chema@interneta.org>
	message dated "Wed, 12 Jun 2024 19:31:32 -0600"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1709906.1718243286.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 12 Jun 2024 21:48:06 -0400
Message-ID: <1709907.1718243286@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1709907.1718243286%40sss.pgh.pa.us>
Precedence: bulk

Chema <chema@interneta.org> writes:
> been banging my head against this one for a couple days.  Googling and
> StackExchange were just as useful, so you're my last hope.  I've been
> unable to get a non-admin user to run Copy From Program even after grant=
ing
> pg_execute_server_program, and everything else I could think of.  It alw=
ays
> fails with ERROR: permission denied to COPY to or from an external progr=
am.

Works for me:

regression=3D# create user joe;
CREATE ROLE
regression=3D# \c - joe
You are now connected to database "regression" as user "joe".
regression=3D> create table jt (t1 text);
CREATE TABLE
regression=3D> copy jt From Program 'echo "Buffa Testata"' CSV;
ERROR:  permission denied to COPY to or from an external program
DETAIL:  Only roles with privileges of the "pg_execute_server_program" rol=
e may COPY to or from an external program.
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also =
works for anyone.
regression=3D> \c - postgres
You are now connected to database "regression" as user "postgres".
regression=3D# GRANT pg_execute_server_program TO joe;
GRANT ROLE
regression=3D# \c - joe
You are now connected to database "regression" as user "joe".
regression=3D> copy jt From Program 'echo "Buffa Testata"' CSV;
COPY 1

What PG version are you working with?

			regards, tom lane