Question

2

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

rated 0 times [ 2] [ 0] / answers: 1 / hits: 1069 / 2 Years ago, tue, october 11, 2022, 8:53:54

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';

Answers

Only authorized users can answer the question. Please sign in first, or register a free account.

hentor

Add To Favorites

Follow

Total Points: 482

Total Questions: 104

Total Answers: 111

Location: South Korea

Member since Sun, Dec 25, 2022

1 Year ago

answered 2 Years ago sertold · Accepted Answer

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.