postgresqlprivilegessql-grantsql-revoke

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

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

Solution

  • 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;