2

I created a new superuser just so that this user can run COPY command. Note that a non-superuser cannot run a copy command. I need this user due to a backup application, and that application requires to run COPY command

But all the restrictions that I specified does not take effect (see below). What is the difference between user postgres and a superuser?

And is there a better way to achieve what I want? I looked into a function with security definer as postgres ... that seems a lot of work for multiple tables.

DROP ROLE IF EXISTS mynewuser;
CREATE ROLE mynewuser PASSWORD 'somepassword' SUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN;
-- ISSUE: the user can still CREATEDB, CREATEROLE

REVOKE UPDATE,DELETE,TRUNCATE ON ALL TABLES IN SCHEMA public, schema1, schema2, schema3 FROM mynewuser;
-- ISSUE: the user can still UPDATE, DELETE, TRUNCATE

REVOKE CREATE ON DATABASE ip2_sync_master FROM mynewuser;
-- ISSUE: the user can still create table;
Improve this question
1
  • What do you want? A non-superuser role that can COPY? Commented Aug 11, 2016 at 20:49

2 Answers 2

Reset to default
1

You are describing a situation where a user can write files to the server where the database runs but is not a superuser. While not impossible, it's definitely abnormal. I would be very selective about who I allow to access my DB server.

That said, if this is the situation, I'd create a function to load the table (using copy), owned by the postgres user and grant the user rights to execute the function. You can pass the filename as a parameter.

If you want to get fancy, you can create a table of users and tables to define what users can upload to what tables and have the table name as a parameter also.

It's pretty outside of the norm, but it's an idea.

Here's a basic example:

CREATE OR REPLACE FUNCTION load_table(TABLENAME text, FILENAME text)
  RETURNS character varying AS
$BODY$
DECLARE
  can_upload integer;
BEGIN

  select count (*)
  into can_upload
  from upload_permissions p
  where p.user_name = current_user and p.table_name = TABLENAME;

  if can_upload = 0 then
    return 'Permission denied';
  end if;

  execute 'copy ' || TABLENAME ||
    ' from ''' || FILENAME || '''' ||
    ' csv';

  return '';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

you've got an sql injection vulnerability there, (in the execute add quote_identifier() on tablename and use quote_literal() on filename)
the idea is very good to be owner of the function by postgres, and execute by ordinary user, but this was not working for me. still received error saying the COPY requires to have superuser.
@pilesoft -- is the owner of the function a superuser?
0

COPY with option other than writing to STDOUT and reading from STDIN is only allowed for database superusers role since it allows reading or writing any file that the server has privileges to access.

\copy is a psql client command which serves the same functionality as COPY but is not server-sided, so only local files can be processed - meaning it invokes COPY but ... FROM STDIN / ... TO STDOUT, so that files on a server are not "touched".

You can not revoke specific rights from a superuser. I'm quoting docs on this one:

Docs: Access DB

Being a superuser means that you are not subject to access controls.

Docs: CREATE ROLE

"superuser", who can override all access restrictions within the database. Superuser status is dangerous and should be used only when really needed.

Improve this answer

2 Comments

Strictly, COPY is allowed for non-superusers, but only for COPY ...TO STDOUT / COPY ... FROM STDIN. Which is what \copy uses. This matters because other clients like psycopg2, PgJDBC etc can also use COPY even without superuser, just only to stream rows to/from the client, not to/from files on the server's disk.
@CraigRinger I've enunciated the COPY part. Thanks for the contribution.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.