Import data only works for superusers?

[Gregor Stephen (Aridhia Informatics)]

Hi everyone

I've been looking at copying data from one workspace to another by associating the dataset in the 'source' workspace  with the 'destination' workspace and using the Import Now button to copy the data across. Both workspaces have separate data sources using separate database accounts, and separate sandboxes linking to separate GreenPlum databases. This is all (currently) on one GreenPlum DB server.

I've found that this works fine if the database accounts specified in the workspaces' data sources both have superuser privileges. However, if I use database accounts that are not superusers, I get this error trying to import the data (this is taken from the worker process log):

timestamp="2013-03-21 13:12:14 UTC" lib=queue_classic level=error action=failed_work job="ImportExecutor.run(8) job_id=42" error="#<TableCopier::ImportFailed: Java::OrgPostgresqlUtil::PSQLException: ERROR: permission denied: no privilege to create a readable gpfdist(s) external table>"

timestamp="2013-03-21 13:12:14 UTC" lib=queue_classic level=error job="ImportExecutor.run(8) job_id=42" exception="Java::OrgPostgresqlUtil::PSQLException: ERROR: permission denied: no privilege to create a readable gpfdist(s) external table" backtrace="

/lib/greenplum_connection.rb:101:in `with_connection'

/lib/greenplum_connection.rb:95:in `with_connection'

/lib/data_source_connection.rb:38:in `execute'

/app/services/cross_database_table_copier.rb:39:in `run'

/app/services/table_copier.rb:16:in `start'

/app/services/import_executor.rb:24:in `run'

/app/services/import_executor.rb:8:in `run'

org/jruby/RubyBasicObject.java:1673:in `__send__'

org/jruby/RubyKernel.java:2081:in `send'

Is it possible to copy data in this way using non-superuser database accounts, maybe by temporarily granting a certain privilege to these accounts to allow them to create the gpfdist tables (I'd like to avoid granting superuser access)? Or is there an alternative method for copying data from workspace to another (via OpenChorus)?

Thanks

Gregor

[Dieu Cao]

Hi Gregor,

Dug up a thread on this in our gpdb forums.

http://www.greenplum.com/communities/forums/archive/index.php/t-270.html

So you could grant create privileges for external tables to normal users but it's not necessarily recommended to do so.

Since different clients have different import security requirements, we had considered in the past adding the ability to specify a special privileged account for each data source that could handle the creation of the external tables against that data source.

I think this could be a very useful feature but it hasn't yet bubbled up to the top of our list.

Let me know if you would like to discuss this further.

-Dieu Cao

Chorus Product Manager