What is the difference between user postgres and a superuser?

Question

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;

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