"permission denied to COPY to or from an external program" even with GRANT pg_execute_server_program
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)
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.
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
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!
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
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.
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