Can Activerecord Connect to Postgresql Remotely and Protect the Db Password

Can ActiveRecord connect to PostgreSQL remotely and protect the DB password?

Regardless of whether Postgres allows this functionality, you can enable a secure connection to a remote database by using SSH tunneling. Here's the gratuitous Stack Overflow paste-in from the Web docs:

First make sure that an SSH server is
running properly on the same machine
as the PostgreSQL server and that you
can log in using ssh as some user.
Then you can establish a secure tunnel
with a command like this from the
client machine:

ssh -L 3333:foo.com:5432 joe@foo.com
The first number in the -L argument,
3333, is the port number of your end
of the tunnel; it can be chosen
freely. The second number, 5432, is
the remote end of the tunnel: the port
number your server is using. The name
or IP address between the port numbers
is the host with the database server
you are going to connect to. In order
to connect to the database server
using this tunnel, you connect to port
3333 on the local machine:

psql -h localhost -p 3333 postgres To
the database server it will then look
as though you are really user
joe@foo.com and it will use whatever
authentication procedure was
configured for connections from this
user and host. Note that the server
will not think the connection is
SSL-encrypted, since in fact it is not
encrypted between the SSH server and
the PostgreSQL server. This should not
pose any extra security risk as long
as they are on the same machine.

In case you want more, you can find it online by searching for "SSL tunnel" or "postgres SSL tunnel". Here's the Postgres site where I got the above:

http://www.postgresql.org/docs/current/static/ssh-tunnels.html

To summarize for Rails, you would then do the following:

1) In a terminal window, run the first ssh command above to establish the tunnel.

2) Set your database props like so:

development:
  adapter: postgresql
  database: journalapp_development
  username: xxx
  password: yyy
  host: localhost
  port: 3333

How to connect to postgresql using url

Try heroku db:push postgres://username:password@localhost/myrailsdb.

Working on a rails app locally with a remote Postgres connection?

Below are the steps to access Heroku db from local development:

1. Login to your heroku account.

2. Navigate to this URL https://postgres.heroku.com/databases/ OR you can get heroku db url by running this command in the terminal: heroku pg:credentials:url

3. Select your application database.

4. You will able to see your heroku pg credentials:

   Host         xxxxxxxxx.77777.amazonaws.com
   Database     42feddfddeee 
   Username         44444444444
   Port         xxxx
   Password     777sfsadferwefsdferwefsdf
   

5. collect above details and put in your databse.yml file development evn:

   adapter: postgresql  
   host: < host >                            # HOST 
   port: < port >                            # Port  
   database: < database name >               # Database Name  
   username: < user_name >                   # User Name  
   password: '< password >'                  # Password 
   

Restart you application,

Best of luck..!!

How do I make Rails use SSL to connect to PostgreSQL?

As you wrote, normally the Ubuntu 12.x packages are set up so that SSL is activated, works out of the box, and in addition is the first method tried by rails, or any client that lets libpq deal with this stuff, which means almost all clients.

This automatic enabling is not necessarily true with other PostgreSQL packages or with a self-compiled server, so the answers or advice applying to these other contexts don't help with yours.

As your setup should work directly, this answer is a list of things to check to find out what goes wrong. Preferably, use psql first to test a connection setup rather than rails, so that generic postgresql issues can be ruled out first.

Client-side

The client-side sslmode parameter controls the sequence of connect attempts.

To voluntarily avoid SSL, a client would need to put sslmode=disable somewhere in the connection string, or PGSSLMODE=disable in the environment, or mess up with one of the other PGSSL* variables. In the unlikely case your rails process had this in its environment, that would explain the error you're getting, given that pg_hba.conf does not allow non-SSL connections.

Another reason to not try SSL is obviously when libpq is not compiled with SSL support but that's not the case with the Ubuntu packages.

The default for sslmode is prefer, described as:

prefer (default)

first try an SSL connection; if that fails, try a non-SSL connection

The SSL=off at the end of your error message relates to the last connect attempt that fails. It may be that SSL was tried and failed, or not tried at all, we can't know from this message alone. The connect attempt with SSL=off is rejected normally by the server per the policy set in pg_hba.conf (hostssl in the first column).

It's more plausible that the problem is server-side, because there are more things than can go wrong.

Server-side

Here are various things to check server-side:

• There should be ssl=on in postgresql.conf (default location: /etc/postgresql/9.1/main/)

• when connecting to localhost with psql, you should be greeted with a message like this:

psql (9.1.13)

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

Type "help" for help.

• The ca-certificates package should be installed and up-to-date.

• The ssl-cert package should be installed and up-to-date.

• Inside the postgres data directory (/var/lib/postgresql/9.1/main by default), there should be soft links:
  
  server.crt -> /etc/ssl/certs/ssl-cert-snakeoil.pem or another valid certificate, and
  
  server.key -> /etc/ssl/private/ssl-cert-snakeoil.key or another valid key.

• /etc/ssl/certs and parent directories should be readable and cd'able by postgres.

• The postgres unix user should be in the ssl-cert unix group (check with id -a postgres) otherwise it can't read the private key.

• If changing postgresql.conf, be sure that postgresql gets restarted before doing any other test.

• There shouldn't be any suspicious message about SSL in /var/log/postgresql/postgresql-9.1-main.log at startup time or at the time of the failed connection attempt.

Unable to start Rails server to connect to postgres because of ACTIVE RECORD : CONNECTION NOT ESTABLISHED

If u are facing this type of errors u have to check three files..

1.database.yml
2. pg_hba.conf in postgresql lib.
3. if u are working on the remote machine we have to include our host ip to the database.yml file also...

while creating the project use his command to know to server that u are going to use postgresql..

'rails new demo --database=postgresql'

then only the pg gem file will get load to bundle..

Coming to pg_hba.conf file edit as trust where it is md5. it will be near your ip address.

md5- requires a password to connect to db
trust- dont asks a password

Be sure that your database.yml file is correct

---

Related Topics