On 15/09/2010 12:50 AM, Carlos Mennens wrote:
On Mon, Sep 13, 2010 at 9:58 PM, Craig Ringer
<craig@xxxxxxxxxxxxxxxxxxxxx> 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/
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
