On 15/09/2010 12:50 AM, Carlos Mennens wrote:
> On Mon, Sep 13, 2010 at 9:58 PM, Craig Ringer
> <craig@postnewspapers.com.au> wrote:
>> craig$ sudo -u postgres psql
>> postgres=> CREATE USER craig WITH PASSWORD 'somepassword'
>> CREATEDB CREATEROLE;
>> postgres=> CREATE DATABASE craig WITH OWNER craig;
>> postgres=> \q
>
> So I set a Linux shell password on my newly auto created 'postgres'
> system user
You can do that, though you don't need to. I usually just sudo to it.
> which is what I use to login to the database as
> 'superuser'. Now I know my password for 'postgres' in the Linux shell
> but I still don't understand what the database password is for
> 'postgres'.
You need to read the manual. It explains how authentication and login
roles work. In particular, it explains pg_hba.conf and the "ident",
"trust" and "md5" authentication modes.
http://www.postgresql.org/docs/current/interactive/client-authentication.html
> So maybe I am still lost but it appears that the database user
> 'postgres' has a password unique to PostgreSQL, right?
Correct. However, it doesn't need to have any password at all; if you're
using ident authentication, postgresql will accept a connection as
"postgres" only from the local unix user "postgres". No need for a
password, you've already convinced the OS you have the access rights.
If you're using "md5" (password) authentication, then you need to set a
password for the postgres database user.
See the manual.
> postgres=# SELECT * from pg_user;
> usename | usesysid | usecreatedb | usesuper | usecatupd | passwd |
> valuntil | useconfig
> ----------+----------+-------------+----------+-----------+----------+----------+-----------
> postgres | 10 | t | t | t | ******** |
> |
> carlos | 16384 | t | t | t | ******** |
> |
You'd usually use the psql command:
\du
for a better view. See:
\?
in psql
> Obviously there appears to be a specific password for both accounts
> which I think are completely seperate from the Linux shell passwords,
> right?
Correct.
> Secondly I am unable to find any information in the docs that show me
> how to set just the user password for 'carlos'. In MySQL I would use:
ALTER USER username SET PASSWORD 'somepassword';
See the manual, and the psql \h command
\h -- statement listing
\h ALTER USER -- syntax of alter user
http://www.postgresql.org/docs/current/interactive/sql-alteruser.html
--
Craig Ringer
Tech-related writing at http://soapyfrogs.blogspot.com/
