Postgraphile: Add note to the documentation to explain Heroku doesn't allow `CREATE ROLE`

[colophonemes]

It looks like Heroku Postgres doesn't allow you to create roles. Is there a workaround for this given that's the basis of PostGraphQL's security model?

If not, it might be a good thing to note in the docs that Heroku isn't a hosting option, given there are a number of links to Heroku's Postgres documentation.

[benjie]

You are correct. I use Heroku for my Node.js hosting, but I use Amazon RDS for my database; you can still feed the URL in the same way (via DATABASE_URL) and everything works just fine.

[colophonemes]

Yeah, I figured that was the case, but thanks for making it explicit 👍

[ppiechota]

@benjie Hi, I tried to create similar setup as yours, but I get below error. This is tested from local repo, but I'm sure I can connect from localhost as I establish connection via DataGrip to the remote database on Amazon RDS. Could you suggest me how to deal with this? You say that setting DATABASE_URL can be used, but I thought it is a parameter used only for heroku postgres db.

$ heroku local web
[OKAY] Loaded ENV .env File as KEY=VALUE Format
21:29:58 web.1   |  Node app is running on port 5000
21:30:03 web.1   |  Error: getaddrinfo ENOTFOUND przemek przemek:5432
21:30:03 web.1   |      at errnoException (dns.js:28:10)
21:30:03 web.1   |      at GetAddrInfoReqWrap.onlookup [as oncomplete] (dns.js:76:26)

server.js

var express = require('express');
var app = express();
var postgraphql = require('postgraphql').postgraphql

app.set('port', (process.env.PORT || 5000));

app.use(postgraphql('postgres://Przemek/*******@przemek.cj2omtd7wryw.us-west-2.rds.amazonaws.com:5432**', 'hairapp', {graphiql: true}));


app.listen(app.get('port'), function() {
  console.log('Node app is running on port', app.get('port'));
});

[colophonemes]

I think the issue is that AWS doesn't let you use the connection string directly, instead you need to destructure it:

This is my connection.js file:

require('dotenv').load({ silent: true })

// parse DATABASE_URL into pg connection object
// see https://github.com/brianc/node-pg-pool#create
const url = require('url')
const params = url.parse(process.env.DATABASE_URL)
const auth = params.auth ? params.auth.split(':') : [null, null]

module.exports = {
  user: auth[0],
  password: auth[1],
  host: params.hostname,
  port: params.port,
  database: params.pathname.split('/')[1],
  ssl: process.env.NODE_ENV !== 'development'
}

[ppiechota]

Thanks @colophonemes . It works now from heroku local, I could finally connect to graphiql and see my schema BUT... it doesn't work from heroku server. I set DATABASE_URL properly. I can connect to graphiql but I receive error when checking docs on graphiql website:

SyntaxError: Unexpected token < in JSON at position 0

As the app works fine when run locally and fails with above error on graphiql, could the heroku server configuration be the cause?

process.env.NODE_ENV is production

Code below. Could you guide me please with it?

var express = require('express');
var app = express();
const url = require('url');
var postgraphql = require('postgraphql').postgraphql;


const params = url.parse(process.env.DATABASE_URL);

//const params = url.parse('postgres://avvo:****@avvo.cj2omtd7wryw.us-west-2.rds.amazonaws.com:5432/avvo');

const auth = params.auth ? params.auth.split(':') : [null, null];

app.set('port', (process.env.PORT || 5000));


console.log('user: ' + auth[0]);
console.log('password: ' + auth[1]);
console.log('host: ' + params.hostname);
console.log('port: ' + params.port);
console.log('database: ' + params.pathname.split('/')[1]);

app.use(postgraphql({
  user: auth[0],
  password: auth[1],
  host: params.hostname,
  port: params.port,
  database: params.pathname.split('/')[1],
  ssl: process.env.NODE_ENV !== 'development'
}, auth[0], {graphiql: true}));


app.listen(app.get('port'), function() {
  console.log('Node app is running on port', app.get('port'));
});

[ppiechota]

remote: -----> Node.js app detected
remote:
remote: -----> Creating runtime environment
remote:
remote: NPM_CONFIG_LOGLEVEL=error
remote: NPM_CONFIG_PRODUCTION=true
remote: NODE_VERBOSE=false
remote: NODE_ENV=production
remote: NODE_MODULES_CACHE=true
remote:
remote: -----> Installing binaries
remote: engines.node (package.json): 6.10.2
remote: engines.npm (package.json): unspecified (use default)
remote:
remote: Downloading and installing node 6.10.2...
remote: Using default npm version: 3.10.10
remote:
remote: -----> Restoring cache
remote: Loading 2 from cacheDirectories (default):
remote: - node_modules
remote: - bower_components (not cached - skipping)
remote:
remote: -----> Building dependencies
remote: Installing node modules (package.json)
remote:
remote: -----> Caching build
remote: Clearing previous node cache
remote: Saving 2 cacheDirectories (default):
remote: - node_modules
remote: - bower_components (nothing to cache)
remote:
remote: -----> Build succeeded!
remote: -----> Discovering process types
remote: Procfile declares types -> web
remote:
remote: -----> Compressing...
remote: Done: 16.8M
remote: -----> Launching...
remote: Released v16
remote: https://cryptic-oasis-71105.herokuapp.com/ deployed to Heroku
remote:
remote: Verifying deploy... done.
To https://git.heroku.com/cryptic-oasis-71105.git
e27ac9a..478c070 master -> master

[ppiechota]

Network:

Request URL:https://cryptic-oasis-71105.herokuapp.com/graphql
Request Method:POST
Status Code:503 Service Unavailable
Remote Address:50.16.227.194:443
Referrer Policy:no-referrer-when-downgrade

**Response Headers**
view source
Cache-Control:no-cache, no-store
Connection:keep-alive
Content-Length:506
Content-Type:text/html; charset=utf-8
Date:Thu, 25 May 2017 00:01:08 GMT
Server:Cowboy

[ppiechota]

so the question is why I get 503 from https://cryptic-oasis-71105.herokuapp.com/graphql
while localhost:5000/graphql is all right?

[benjie]

Moved to https://github.com/postgraphql/postgraphql.github.io/issues/4

[pencilcheck]

Looks like the ticket was for documentation.

Let me copy and paste my comment here so everyone knows:

---

Even though heroku doesn't allow create role, but it doesn't limit anything else (e.g. grants).

I have a workaround. Heroku actually allows you to create new credentials (roles) but only through their heroku API
https://devcenter.heroku.com/articles/heroku-postgresql-credentials#the-default-credential

Therefore you need to simply replace create role with heroku commands, the rest should all fall in place very quickly then you are all set.

[benhjames]

I added some additional info to https://github.com/graphile/postgraphile/wiki/Using-with-Heroku to help people get set up with Heroku Postgres too. :)

[benjie]

Thanks!

Ref: https://github.com/graphile/graphile.github.io/issues/21

[davbeck]

What would the consequence be of just relying on row level security?

[benjie]

You mean via ALTER TABLE ... FORCE ROW LEVEL SECURITY so that RLS policies applied to the DB owner as well as the unprivileged roles? I have no experience of doing that; but I would guess:

• SECURITY DEFINER functions would no longer bypass RLS (?), which may make things harder that require elevated privileges
• you'd have permission granted to INSERT/UPDATE on all columns (?), so --no-ignore-rbac would be less useful and users could potentially manipulate the primary keys of the documents - you'd have to be careful to @omit create,update on each of those columns.

I would not recommend this approach.