5

Doing freelance, I keep on getting on new projets.

I find postgresql config highly complicated when being used in development mode only (I totally understand that production requirements are much different).

What I want to achieve is to config my postgres so that whatever username/password/port/connexion mode is used, it has all the rights on the DB (as security is no matter here). Working with Rails, all the config is in config/database.yml and I don't want to change anything from the file itself.

I achieved having any password_less authentication for every connexion (local and TCP), but doing this:

# /etc/postgresql/9.3/main/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
host    all             all             0.0.0.0/0               trust

And:

# /etc/postgresql/9.3/main/postgresql.conf
listen_addresses = '*'

As from this Post.

But if the user (eg. appname_dev) doesn't exists, I get:

FATAL:  role "appname_dev" does not exist

This Post allows me to create the user in 1 line, which is fair enough (sudo -u postgres createuser -d -R -P appname_dev), but I would really like this to be plug and play.

  • How can I achieve that?
  • Ain't there any development installation mode on postgres where by default, credentials would be much lighter configured that the current one?
  • Am I missing some best practice that make this not being a problem?

I understand the port thing can be tricky, but IMHO, the rest should not!

Thanks for the help!

Community
  • 1
  • 1
Augustin Riedinger
  • 20,909
  • 29
  • 133
  • 206
  • It seems you want [sqlite](http://www.sqlite.org) except it's not fully compatible with postgres at the SQL level. – Daniel Vérité Feb 03 '15 at 19:43
  • SQLite does fit most of my requirements. But what I'd like is a working db out of the box, without having to edit any single config file meaning the choice of Postgres + Username + Password is already made, not by me. – Augustin Riedinger Feb 04 '15 at 11:02
  • 2
    You can only connect with users that do exist in the database. There is no way around that. Is there some "bootstrap" schema setup code in your dev-environment? You could include the necessary `create user` script there and as everybody can connect as the superuser, everybody can run that bootstrap code. –  Mar 12 '15 at 11:09
  • Sounds like an interesting solution, I shall have a look at it! – Augustin Riedinger Mar 12 '15 at 13:43
  • What existing role would you like it to assume instead of a non-existing one, Augustin? – soulseekah Mar 17 '15 at 12:05
  • 1
    I've skimmed through the source, the only way to change the behavior would be to patch `InitializeSessionUserId` and either add a new Id there if it doesn't exist, or replace the one coming in for a completely different one. This begs the question, why don't you just use an existing user to begin with? – soulseekah Mar 17 '15 at 12:29
  • Well, I get on projects with existing codebase. Some of them include DB connexion configuration (username and password) for local environment. And I don't really see a good reason to change their value when my local environment DB should be in *development mode*, meaning accepting any config. – Augustin Riedinger Mar 17 '15 at 16:33
  • Otherwise, it is the man bending to the machine's desires! – Augustin Riedinger Mar 17 '15 at 16:33
  • I have a similar application requirement. I wrote a script ( bash/python ) doesn't matter, that will create and set up the database, users, etc on install. That script is run as part of my 'install' step ( it's not really install, but similar .. ) – StAlphonzo Mar 18 '15 at 12:11

1 Answers1

0

You may achieve this by using pgbouncer (connection pooler) with following settings:

* = host=127.0.0.1 user=postgres
auth_type = any

so when you connect to pgbouncer with any user and any password it will connect to postgres as postgres user which has all permissions.

You may also want to change pgbouncer port to 5432 (by default it works on 6432 port) and change postgres port to something different so your application will connect to pgbouncer without any modifications in configs.

alexius
  • 2,501
  • 20
  • 21