change PSQL default user

Question

K- so hopefully explaining why I'm trying to do this will help. I do most of my dev work on a company-provided laptop; but when I get home, I would like to use my desktop. I have a number of rails projects wherein the username & password are already defined as 'postgres', so I don't want to change the username & password in the .yml files for these projects just to get it to work on my desktop. Too much of a headache. So, all I want to do is change my postgres environment to look for the username 'postgres' as default. I also don't want to have to pass -U or -W every single time I want to log in. So...

How can you change the default role for psql/postgres? From command line, if I type in:

$ psql

I get back

psql: FATAL:  role "crash" does not exist

It's looking for "crash" because that's my user account for my PC login- however, I want my default username to be "postgres" not "crash". How can I change which role PSQL is looking for on init?

As it turns out, Google has a number of resources regarding creating a new role for postgres, or changing the password, or any number of other useful information; however, I've been searching for hours and I can't find anything specific to changing which role postgres should use for default.

If somebody could point me in the right direction, I'd appreciate it.

Sys- ubuntu 12.04 lts 64-bit

Solution

There's introductory information on using PostgreSQL on Ubuntu on the Ubuntu Community site.

You may also find the PostgreSQL manual on client authentication useful.

You'll want to set the PGUSER environment variable to override the default user.

It can also be useful to define a service for libpq in ~/.pg_service.conf, per the documentation on libpq service definitions; you can then refer to that in psql with service=myservice, avoiding the need to repeat details. This is the "right" way to handle this problem - define a service, refer to the service in the database.yml and have a .pg_service.conf file outside revision control specify what that actually means, with a template copy in revision control for convenience.

(The ~/.psqlrc file may also be useful See the documentation for psql.)

Shell aliases can be handy too. I often do things like add

alias psomedb 'psql "service=somedb"'

to my ~/.bashrc for convenient access to frequently used DBs and hosts.

The ~/.pgpass file may be used to store passwords for various host/port/db/user combinations.

All that being said, it's a terrible idea to work routinely as the postgres user. It should not be your default. Create an ordinary user, possibly one with CREATEDB rights, but not a superuser. Use that user for most tasks and only switch to a superuser (postgres or some other user you created for the purpose) when you need superuser rights. It's the same principle as "don't work as root, sudo only when required instead".

Comments

[hbn]

I don't see how you can use .psqlrc to set default connection info - please could you clarify?

[Craig Ringer]

@hbn You're right, and I have no idea what I was thinking when I wrote that. Amended.