Re: Post Install / Secure PostgreSQL

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/