Thread: "permission denied to COPY to or from an external program" even with GRANT pg_execute_server_program

"permission denied to COPY to or from an external program" even with GRANT pg_execute_server_program

From

Chema

Date:

13 June 2024, 01:31:32

Dear Postgreezers,

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 granting pg_execute_server_program, and everything else I could think of. It always fails with ERROR: permission denied to COPY to or from an external program.

I'll let the code speak by itself. Here's a minimal example that I've tried in the last official Docker image:

-- Spin a temporal Pg and connect to psql
--docker run --name pg16 -e POSTGRES_PASSWORD=qwer -d postgres:16
--docker exec -ti pg16 psql -U postgres
CREATE TABLE testtable (
id int NOT NULL GENERATED ALWAYS AS IDENTITY,
name text NOT NULL
);
Create Role justintestin noinherit login password 'qwer';
-- Necessary privileges
GRANT CONNECT ON DATABASE postgres TO justintestin;
GRANT USAGE ON SCHEMA public TO justintestin;
GRANT ALL ON ALL TABLES IN SCHEMA public TO justintestin;
-- Apply them to new tables/views created by admin account
ALTER DEFAULT IN SCHEMA public GRANT ALL ON TABLES TO justintestin;
-- Allow Copy From Program... or try to anyway
GRANT pg_execute_server_program TO justintestin;
-- Tests 
GRANT ALL ON testtable TO justintestin;
GRANT ALL ON SCHEMA public TO justintestin;
GRANT ALL ON DATABASE postgres to justintestin;
GRANT pg_read_all_data TO justintestin;
GRANT pg_write_all_data TO justintestin;
--Copy works with admin account
Copy testtable(name) From Program 'echo "Buffa Testata"' CSV;
-- COPY 1

--But fails with justintestin
SET role justintestin;
Copy testtable(name) From Program 'echo "Errato Denegato"' CSV;
--SQL Error [42501]: ERROR: permission denied to COPY to or from an external program
-- Detail: Only roles with privileges of the "pg_execute_server_program" role may COPY to or from an external program.

--Even tho he's privileged
SELECT rolname FROM pg_roles WHERE
pg_has_role(current_user, oid, 'member');
-- rolname
---------------------------
-- pg_read_all_data
-- pg_write_all_data
-- pg_execute_server_program
-- justintestin

--Insert works
Insert Into testtable ("name") VALUES('Pazzo Intestinato');
--INSERT 0 1
Select * From testtable;
SELECT current_user, session_user;

-- Clean up for new test
SET role postgres;
Drop Table testtable;
Drop Owned By justintestin;
Drop Role justintestin;

What am I missing? (besides a few chunks of hair)

Re: "permission denied to COPY to or from an external program" even with GRANT pg_execute_server_program

From

"David G. Johnston"

Date:

13 June 2024, 01:44:40

On Wednesday, June 12, 2024, Chema <chema@interneta.org> wrote:

Create Role justintestin noinherit login password 'qwer';

GRANT pg_execute_server_program TO justintestin;

Pretty sure since you choose not to allow justintestin to inherit stuff you will need to issue a “set role to pg_execute_server_program” before you attempt the copy command.

David J.

Re: "permission denied to COPY to or from an external program" even with GRANT pg_execute_server_program

From

Tom Lane

Date:

13 June 2024, 01:48:06

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 granting
> pg_execute_server_program, and everything else I could think of.  It always
> fails with ERROR: permission denied to COPY to or from an external program.

Works for me:

regression=# create user joe;
CREATE ROLE
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=> create table jt (t1 text);
CREATE TABLE
regression=> 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" role 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=> \c - postgres
You are now connected to database "regression" as user "postgres".
regression=# GRANT pg_execute_server_program TO joe;
GRANT ROLE
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=> copy jt From Program 'echo "Buffa Testata"' CSV;
COPY 1

What PG version are you working with?

            regards, tom lane

Re: "permission denied to COPY to or from an external program" even with GRANT pg_execute_server_program

From

Chema

Date:

13 June 2024, 01:53:43

Pretty sure since you choose not to allow justintestin to inherit stuff you will need to issue a “set role to pg_execute_server_program” before you attempt the copy command.

David J.

That was it! Blind paranoia bites my rear again. Thanks!

Trying to dynamically create a procedure

From

"Dirschel, Steve"

Date:

26 March, 23:27:29

Hi,

I have the need to dynamically create a procedure. Here is a simple procedure:

create or replace procedure junk.test_proc()

LANGUAGE plpgsql

AS $$

declare

v_cnt integer := 0;

begin

raise notice 'v_cnt is %', v_cnt;

end $$;

That creates and runs fine.

Here I’m trying to create it inside PL/pgSQL block (yes there is nothing dynamic below but the real code will have parts of the procedure that needs to have code dynamically generated):

DO $$

BEGIN

EXECUTE 'create or replace procedure junk.test_proc() ' ||

'LANGUAGE plpgsql ' ||

'AS $$ ' ||

'declare ' ||

' v_cnt integer := 0; ' ||

'begin ' ||

' raise notice 'v_cnt is %', v_cnt; ' ||

'end $$';

END;

It throws this error:

ERROR: syntax error at or near "$$

DO $$"

LINE 1: $$

dbtest=>

dbtest=> END;

WARNING: there is no transaction in progress

COMMIT

dbtest=>

dbtest=> $$

I think the problem has to do with having AS $$ and END $$ with the 2 $’s. I’m not sure if there is different syntax I can use outside the $$ or if there is something I should use in the PL/pgSQL to escape those $$ to get this to work.

Any help would be appreciated.

Thanks

This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html

Re: Trying to dynamically create a procedure

From

Christophe Pettus

Date:

26 March, 23:30:02

> On Mar 26, 2025, at 13:27, Dirschel, Steve <steve.dirschel@thomsonreuters.com> wrote:
>
>   I think the problem has to do with having AS $$ and END $$ with the 2 $’s.

PostgreSQL's multiline-string syntax is quite flexible.  You can do things like:

DO $doblock$
 ...
$doblock$
LANGUAGE plpgsql;

I tend to put the name of the function between the $s to avoid nesting problems.

Re: Trying to dynamically create a procedure

From

Laurenz Albe

Date:

28 March, 10:12:32

On Wed, 2025-03-26 at 20:27 +0000, Dirschel, Steve wrote:
> DO $$
>  
> BEGIN
>  
> EXECUTE 'create or replace procedure junk.test_proc() ' ||
>   'LANGUAGE plpgsql  '                          ||
>   'AS $$ '                            ||
>   'declare  '                              ||
>   '  v_cnt         integer := 0; '               ||
>   'begin '                            ||
>   '  raise notice 'v_cnt is %', v_cnt; '         ||        
>   'end $$';
>  
> END;
>  
> $$
>  
> It throws this error:
>  
> ERROR:  syntax error at or near "$$
> DO $$"
> LINE 1: $$
>         ^
> dbtest=>
> dbtest=> END;
> WARNING:  there is no transaction in progress
> COMMIT

If you nest dollar quotes, you need to use different strings between the dollars:

DO $do$
BEGIN
   EXECUTE 'CREATE PROCEDURE ... AS $fun$ ... $fun$';
END;
$do$;

Yours,
Laurenz Albe