How to restrict a user from executing a COPY query in PostgreSQL?

Question

I need to restrict a user in pgsql from executing copy statement. I can disallow execution of any of the CRUD operations.

to restrict `copy table to` revoke select, to restrict `copy table from` revoke insert — Vao Tsun, Mar 08 '18 at 10:00
I don't understand your second sentence. What has that to do with the first sentence? — Laurenz Albe, Mar 08 '18 at 10:36

score 1 · Accepted Answer · answered Mar 08 '18 at 11:33

COPY command can read from a table to somewhere or it can write to a table from somewhere (from file, another table or from a query).

I assume you want to restrict writing to any table.

In this case, just restrict UPDATE/INSERT/DELETE operations for that user:

revoke insert, delete, update on all tables in schema public from xxx;

Double-check that your user is not a superuser (in this case, all permissions checks are just ignored; so if it is, just alter role xxx nosuperuser;).

It's also worth to define default strategy for all new tables, revoking writing access from this role:

alter default privileges in schema public
  revoke insert,update,delete on tables from xxx;

If you want to restrict reading with COPY from any table (like copy (select * from table1) to stdout;), you need also revoke reading access (i.e. SELECT permission) from all objects.

Note, that there is a variant of COPY which will work nevertheless – if it reads from "nowhere", i.e.:

copy (select 'blabla') to stdout;

or

copy (values('blabla')) to stdout;

or, even more extreme version, reading "nothing":

copy (select) to stdout;

How to restrict a user from executing a COPY query in PostgreSQL?

1 Answers1