default postgres user and password

Question

I'm just new to postgresql db management. I've been playing around with a db that I didn't create. Trying to understand the different roles that have been created.

Does the fact that I can log in doing the following mean that the postgres user has no password set up?

psql -U postgres 

I did a

`select * from pg_roles`

and I can see that there is a password set. I found this article: http://www.postgresql.org/message-id/[email protected] which seeme to confirm that the postgres user is there by default... and you have to explicitly set a password. It's the second part about the password that I'm not sure about. Is it blank by default or set to something?

I know that if pg_hba.conf is set to trust everything from 127.0.0.1, then you can log in from the local server without specifying a password. That might be what's happening in my case... i will test by changing the pg_hba.conf file... But it'd be nice to know what the default password is for postgres user.

Thanks.

Answer 1

pg_roles is not the view that can tell whether a user has a password or not, because the password field is always set to ******** no matter what.

This comes from the definition of this view (taken from version 9.3):

select definition from pg_views  where viewname='pg_roles';

Result:

  SELECT pg_authid.rolname,
     pg_authid.rolsuper,
     pg_authid.rolinherit,
     pg_authid.rolcreaterole,
     pg_authid.rolcreatedb,
     pg_authid.rolcatupdate,
     pg_authid.rolcanlogin,
     pg_authid.rolreplication,
     pg_authid.rolconnlimit,
     '********'::text AS rolpassword
     pg_authid.rolvaliduntil,
     s.setconfig AS rolconfig,
     pg_authid.oid 
    FROM (pg_authid
    LEFT JOIN pg_db_role_setting s
      ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))));

Note how the rolpassword column is hardcoded to reveal nothing (We may wonder why it's there at all. Maybe for backward compatibility?)

On the other hand , there is a pg_shadow view that displays passwords as they're stored, in a column named passwd. This view is only readable by a superuser (typically: the postgres user).

Example:

create user foo unencrypted password 'foopassword';
create user bar encrypted password 'foopassword';
select usename,passwd from pg_shadow where usename in ('postgres','foo','bar');

Result on a vanilla Debian install:

 usename  |               passwd                
----------+-------------------------------------
 postgres | 
 foo      | foopassword
 bar      | md50390570d30cb9a2f9cb7476f0763cf51

Initially the postgres password is often empty, except on Windows for which the installer tends to ask for it. On Unix, pg_hba.conf is often set up such that only the OS user postgres may log in as the database user postgres through Unix socket domains without a password. This is reasonable as a default security policy. Windows doesn't have Unix domain sockets, and the most recent versions of the installer don't even use a postgres OS user, so it makes sense that it implements a different default security policy.

If a password is blank and the pg_hba.conf requires a password for the particular database/user/origin of an incoming connection, then the connection is rejected. There's no difference between a blank password and a lack of password.