What accounts for the differing instructions on setting password for postgres?

Question

I'm following this blog post http://hendrelouw73.wordpress.com/2012/11/14/how-to-install-postgresql-9-1-on-ubuntu-12-10-linux/ to install postgres on ubuntu. These are steps two and three.

STEP 2: INSTALL POSTGRESQL ON YOUR SYSTEM


sudo apt-get update
sudo apt-get install postgresql-9.1

STEP 3: CONFIGURE YOUR POSTGRESQL PASSWORD

sudo -u postgres psql template1
ALTER USER postgres with encrypted password 'your_password';

However, I'm also looking at a RailsCast which installs postgres on ubuntu. First, Ryan Bates does this

root@li349-144:~# apt-get install postgresql libpq-dev

and then he sets a password like this root@li349-144:~# sudo -u postgres psql

terminal
postgres=# \password
Enter new password: 
Enter it again:

So one big difference is in how the password is configured, in that the Railscast does not touch something called template described in Step 3 of the blogpost

Looking at the instructions for installing postgres on ubuntu https://help.ubuntu.com/community/PostgreSQL, it is similar to Railscasts in that it doesn't have this reference to template to create the user

Can you explain the difference and whether or not I should take this step? STEP 3: CONFIGURE YOUR POSTGRESQL PASSWORD

sudo -u postgres psql template1
ALTER USER postgres with encrypted password 'your_password';

Answer 1

There is no real difference between the two methods. User/login roles and passwords are stored server-wide and not database-wide. In other words, the password is for connecting to the server, so it doesn't matter which database you connect to. The command

sudo -u postgres psql template1 (Thanks to Daniel Vérité for the comment)

in effect tells the system to switch to user postgres and run the CLI client (psql) to connect to the server and use the "template1" database. If no user and/or database name is specified, then psql tries to connect as the current user and "to use" a database with the same name as the current user (postgres in this case - which makes the "template" parameter superfluous)

This is what the above command is doing step by step:

sudo -u postgres This switches to the postgres user. Notice the absence of the -p parameter from sudo. This is essential, because the "os user postgres" (who has no password and his sole purpose is to own the running server processes) is different from "database user postgres" (who is the database root user or rather the database administrator and absolutely needs a password.)

psql --username=postgres --database=template1 This connects to localhost using database username postgres and database template1.

For your purpose, this line can be abbreviated to

sudo -u postgres psql

ALTER USER postgres with encrypted password 'your_password';

This will change to user postgres and connect to localhost as user postgres to the database named postgres and then set the password.

If you need further clarification please ask. I'll be happy to help.

A brief note on template databases. When creating a new database, postgres uses a template named template1. This is something like the skeleton file used to set user default when creating user accounts in linux. It is usually modified by the dba to make sure certain tools and behaviors are enforced server-wide. But why the 1 you may ask. Well, there is another template database named template0 and it is a protected "clean slate" copy of template1 just in case something goes south with it. Here is a wiki page describing such a scenario.

Answer 2

Do not do this..

terminal
postgres=# \password
Enter new password: 
Enter it again:

You're turning on a user you should not turn on. Instead, (a copy of my answer)

Configuring PostgreSQL

This is a copy of my original post here

There are two methods you can to configure. Both require creating a user and a database.

1. Using createuser and createdb,

   $ sudo -u postgres createuser -s $USER
   $ createdb mydatabase
   $ psql -d mydatabase
   

2. Using the SQL administration commands, and connecting with a password over TCP

   $ sudo -u postgres psql postgres
   

   And, then in the psql shell

   CREATE ROLE myuser LOGIN PASSWORD 'mypass';
   CREATE DATABASE mydatabase WITH OWNER = myuser;
   

   Then you can login,

   $ psql -h localhost -d mydatabase -U myuser -p <port>
   

   If you don't know the port, you can always get it by running the following, as the postgres user,

   SHOW port;
   

   Or,

---

    $ grep "port =" /etc/postgresql/*/main/postgresql.conf

Sidenote: the postgres user

I suggest NOT modifying the postgres user.

1. It's normally locked from the OS. No one is supposed to "log in" to the operating system as postgres. You're supposed to have root to get to authenticate as postgres.
2. It's normally not password protected and delegates to the host operating system. This is a good thing. This normally means in order to log in as postgres which is the PostgreSQL equivalent of SQL Server's SA, you have to have write-access to the underlying data files. And, that means that you could normally wreck havoc anyway.
3. By keeping this disabled, you remove the risk of a brute force attack through a named super-user. Concealing and obscuring the name of the superuser has advantages.