Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

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';
share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
When this answer refers to template, it should be replaced by template1. The point of template1 in the tutorial is that it will accept your connection when you need to issue SQL commands but haven't created any db yet. –  Daniel Vérité Jun 14 '13 at 13:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.