SQL API not working for external IP

[Dinesh Dass Subramanian]

I could successfully access cartoDb interface using an external IP Address. But the SQL API is not working for the external IP even though I opened the port 8080 in my AWS.

cartodb app_config.yml

defaults: &defaults

  # If uncommented, most images like static map previews and twitter card image urls will use this CDN urls

  #cdn_url:

  #  http:             "http.cdn.host"

  #  https:            "https.cdn.host"

  http_client_logs: true

  ogr2ogr:

    binary:           'which ogr2ogr'

    csv_guessing:     true

  debug_assets: true

  mandatory_keys:     [layer_opts, sql_api, varnish_management, redis, session_domain]

  session_domain:     'Public IP'

  # If activated, urls will use usernames in format //SESSION_DOMAIN/user/USERNAME and ignore subdomains if present

  subdomainless_urls: true

  http_port:           3000 # nil|integer. HTTP port to use when building urls. Leave empty to use default (80)

  https_port:          # nil|integer. HTTPS port to use when building urls. Leave empty to use default (443)

  secret_token:       '71c2b25921b84a1cb21c71503ab8fb23'

  account_host:       'Public IP:3000'

  # Here you can define other hosts different to account_host that also will be CORS enabled

  # cors_enabled_hosts:

  #   - example.com

  #   - foo.bar

  #   - carto.dev

  account_path:       '/account'

  vizjson_cache_domains: ['Public IP']

  data_library:

    username:         'common-data'

    path:             '/data-library'

  disable_file:       '~/disable'

  watcher:

    ttl:              60

  tiler:

    filter: 'mapnik'

    internal:

      protocol:      'http'

      domain:        'Public IP'

      port:          '8181'

      host:          ''

      verifycert:     false

    private:

      protocol:      'http'

      domain:        'Public IP'

      port:          '8181'

      verifycert:     false

    public:

      protocol:      'http'

      domain:        'Public IP'

      port:          '8181'

      verifycert:     false

  sql_api:

    private:

      protocol:   'http'

      domain:     'Public IP'

      endpoint:   '/api/v1/sql'

      port:       8080

    public:

      protocol:   'http'

      domain:     'Public IP'

      endpoint:   '/api/v2/sql'

      port:       8080

  api_requests_service_url: ''

  developers_host:    'http://developers.Public IP:3000'

SQL-API config/environments/development.js

// Time in milliseconds to force GC cycle.

// Disable by using <=0 value.

module.exports.gc_interval = 10000;

// In case the base_url has a :user param the username will be the one specified in the URL,

// otherwise it will fallback to extract the username from the host header.

module.exports.base_url     = '(?:/api/:version|/user/:user/api/:version)';

// If useProfiler is true every response will be served with an

// X-SQLAPI-Profile header containing elapsed timing for various

// steps taken for producing the response.

module.exports.useProfiler = true;

module.exports.log_format   = '[:date] :remote-addr :method :req[Host]:url :status :response-time ms -> :res[Content-Type] (:res[X-SQLAPI-Profiler]) (:res[X-SQLAPI-Errors])';

// If log_filename is given logs will be written there, in append mode. Otherwise stdout is used (default).

// Log file will be re-opened on receiving the HUP signal

module.exports.log_filename = 'logs/cartodb-sql-api.log';

// Regular expression pattern to extract username

// from hostname. Must have a single grabbing block.

module.exports.user_from_host = '^Public IP:8080\/user\/(.*)';

module.exports.node_port    = 8080;

module.exports.node_host    = '';

// idle socket timeout, in miliseconds

module.exports.node_socket_timeout    = 600000;

module.exports.environment  = 'development';

module.exports.db_base_name = 'cartodb_dev_user_<%= user_id %>_db';

// Supported labels: 'user_id' (read from redis)

module.exports.db_user      = 'development_cartodb_user_<%= user_id %>';

// Supported labels: 'user_id', 'user_password' (both read from redis)

module.exports.db_user_pass = '<%= user_password %>'

// Name of the anonymous PostgreSQL user

module.exports.db_pubuser   = 'publicuser';

// Password for the anonymous PostgreSQL user

module.exports.db_pubuser_pass   = 'public';

module.exports.db_host      = 'localhost';

module.exports.db_port      = '5432';

module.exports.db_batch_port      = '5432';

module.exports.finished_jobs_ttl_in_seconds = 2 * 3600; // 2 hours

module.exports.batch_query_timeout = 12 * 3600 * 1000; // 12 hours in milliseconds

module.exports.batch_log_filename = 'logs/batch-queries.log';

// Max number of queued jobs a user can have at a given time

module.exports.batch_max_queued_jobs = 64;

// Capacity strategy to use.

// It allows to tune how many queries run at a db host at the same time.

// Options: 'fixed', 'http-simple', 'http-load'

module.exports.batch_capacity_strategy = 'fixed';

// Applies when strategy='fixed'.

// Number of simultaneous users running queries in the same host.

// It will use 1 as min.

// Default 4.

module.exports.batch_capacity_fixed_amount = 4;

// Applies when strategy='http-simple' or strategy='http-load'.

// HTTP endpoint to check db host load.

// Helps to decide the number of simultaneous users running queries in that host.

// 'http-simple' will use 'available_cores' to decide the number.

// 'http-load' will use 'cores' and 'relative_load' to decide the number.

// It will use 1 as min.

// If no template is provided it will default to 'fixed' strategy.

module.exports.batch_capacity_http_url_template = 'http://<%= dbhost %>:9999/load';

// Max database connections in the pool

// Subsequent connections will wait for a free slot.

// NOTE: not used by OGR-mediated accesses

module.exports.db_pool_size = 500;

// Milliseconds before a connection is removed from pool

module.exports.db_pool_idleTimeout = 30000;

// Milliseconds between idle client checking

module.exports.db_pool_reapInterval = 1000;

// max number of bytes for a row, when exceeded the query will throw an error

//module.exports.db_max_row_size = 10 * 1024 * 1024;

// allows to use an object to connect with node-postgres instead of a connection string

//module.exports.db_use_config_object = true;

// requires enabling db_use_config_object=true

// allows to enable/disable keep alive for database connections

// by default is not enabled

//module.exports.db_keep_alive = {

//    enabled: true,

//    initialDelay: 5000

//};

module.exports.redis_host   = '127.0.0.1';

module.exports.redis_port   = 6379;

module.exports.redisPool    = 50;

module.exports.redisIdleTimeoutMillis   = 100;

module.exports.redisReapIntervalMillis  = 10;

module.exports.redisLog     = false;

// tableCache settings

module.exports.tableCacheEnabled = false; // false by default

// Max number of entries in the query tables cache

module.exports.tableCacheMax = 8192;

// Max age of query table cache items, in milliseconds

module.exports.tableCacheMaxAge = 1000*60*10;

// Temporary directory, make sure it is writable by server user

module.exports.tmpDir = '/tmp';

// change ogr2ogr command or path

module.exports.ogr2ogrCommand = 'ogr2ogr';

// change zip command or path

module.exports.zipCommand = 'zip';

// Optional statsd support

module.exports.statsd = {

  host: 'localhost',

  port: 8125,

  prefix: 'dev.:host.',

  cacheDns: true

  // support all allowed node-statsd options

};

module.exports.health = {

    enabled: true,

    username: 'development',

    query: 'select 1'

};

module.exports.disabled_file = 'pids/disabled';

module.exports.ratelimits = {

  // whether it should rate limit endpoints (global configuration)

  rateLimitsEnabled: false,

  // whether it should rate limit one or more endpoints (only if rateLimitsEnabled = true)

  endpoints: {

    query: false,

    query_format: false,

    job_create: false,

    job_get: false,

    job_delete: false

  }

}

Error:

[2018-04-03 06:37:35.887] [INFO] [default] - [Tue, 03 Apr 2018 06:37:35 GMT] ::ffff:202.83.25.160 GET Public IP:8080/user/development/api/v2/sql?sort_order=asc&ro$

[2018-04-03 06:40:57.941] [INFO] console - Using Node.js v6.9.2

[2018-04-03 06:40:57.942] [INFO] console - Using configuration file "/home/ubuntu/cartodb-server/CartoDB-SQL-API/config/environments/development.js"

[2018-04-03 06:40:57.943] [INFO] console - CartoDB SQL API 2.0.1 listening on :8080 PID=17251 (development)

[2018-04-03 06:41:03.965] [ERROR] console - EXCEPTION REPORT: Error: Sorry, we can't find CARTO user 'development'. Please check that you have entered the correct doma$

    at /home/ubuntu/cartodb-server/CartoDB-SQL-API/node_modules/cartodb-redis/lib/carto_metadata.js:268:30

    at Command.callback (/home/ubuntu/cartodb-server/CartoDB-SQL-API/node_modules/cartodb-redis/lib/carto_metadata.js:707:21)

    at normal_reply (/home/ubuntu/cartodb-server/CartoDB-SQL-API/node_modules/redis/index.js:726:21)

    at RedisClient.return_reply (/home/ubuntu/cartodb-server/CartoDB-SQL-API/node_modules/redis/index.js:824:9)

    at JavascriptRedisParser.returnReply (/home/ubuntu/cartodb-server/CartoDB-SQL-API/node_modules/redis/index.js:192:18)

    at JavascriptRedisParser.execute (/home/ubuntu/cartodb-server/CartoDB-SQL-API/node_modules/redis-parser/lib/parser.js:574:12)

    at Socket.<anonymous> (/home/ubuntu/cartodb-server/CartoDB-SQL-API/node_modules/redis/index.js:274:27)

    at emitOne (events.js:96:13)

    at Socket.emit (events.js:188:7)

    at readableAddChunk (_stream_readable.js:176:18)

    at Socket.Readable.push (_stream_readable.js:134:10)

    at TCP.onread (net.js:548:20)

[Dinesh Dass Subramanian]

Got it worked. Did following steps to make that work. 

• Removed and reinstalled redis server
  

  sudo apt-get purge redis-server
  sudo add-apt-repository ppa:cartodb/redis && sudo apt-get update
  sudo spt-get install redis-server
  

• Ran the 'restore-redis' in cartodb
  https://groups.google.com/forum/#!topic/cartodb/2zzYXKLbip8
  

  cd ~/Wherever/cartodb
  sh script/restore_redis
  

• Check the redis dir and dbfilename
  

  $ redis-cli
  127.0.0.1:6379> CONFIG GET dir
  "dir" 
  "/var/lib/redis" 
  127.0.0.1:6379> CONFIG GET dbfilename
  "dbfilename" 
  "dump.rdb" 
  

  
  https://stackoverflow.com/questions/22160753/redis-failed-opening-rdb-for-saving-permission-denied