Search This Blog

Sunday, July 1, 2012

Postgresql 9.1 set default password on Ubuntu 12.04

Set default password for Postgresql 9.1 on Ubuntu 12.04:

Open a terminal

sudo -u postgres psql postgres
(run psql client as a linux user postgres and connect to postgres database)

ALTER USER postgres WITH PASSWORD '<newpassword>';
(change password for user postgres to <newpassword>)

16 comments:

  1. Just what I was looking for! Thanks!

    ReplyDelete
  2. On OPEN SUSE remember to change pg_hba.conf settings. Otherwise you won't be able to log in to postgresql.

    In file /var/lib/pgsql/data/pg_hba.conf you should replace peer and ident authentication method with trust method, so this lines:

    # "local" is for Unix domain socket connections only
    local all all peer
    # IPv4 local connections:
    host all all 127.0.0.1/32 ident
    … should be replaced on:

    # "local" is for Unix domain socket connections only
    local all all trust
    # IPv4 local connections:
    host all all 127.0.0.1/32 trust

    ReplyDelete
  3. please i need a help, i'm new in postgresql SGBDR, i installed it on ubuntu 12, and i can't open these files pg_hba.conf and postgresql.conf. when i perform this command:
    sudo gedit /ect/postgresql/9.1/main/postgresql.conf
    or this one :
    sudo gedit /ect/postgresql/9.1/main/pg_hba.conf
    i'm invited to enter postgres password
    [sudo] password for postgres:
    when i enter the password i receive a message saying sorry, try again
    please help ;e to understand what can be the problem.

    ReplyDelete
    Replies
    1. You need to enter your 'root' password or a 'sudo user password'... Which means that only a superuser can edit the files in /etc/ folder. Alternatively you can login as root and just enter "gedit /ect/postgresql/9.1/main/pg_hba.conf"

      Delete
  4. please is there any difference between a database postgres's password and a system postgres's password???? because i can login in pgamin with postgres and his password without a problem but to open config file or to restart the postgresql server, the same password is not going.

    ReplyDelete
    Replies
    1. YES, of course there is a difference. Postgres is a linux user which postgresql runs on. To log in a linux postgres user you can first log in as root an then enter: su postgres.
      On the other hand you can log in as root, and enter:
      "sudo -u postgres psql postgres" which will run the psql command as postgres user. Hope that helpful.

      Delete
    2. thanks a lot for your explanations, i will try what you said and let you know the result.

      Delete
  5. GOOOOOODDDD i logged as root user and i configured the two system files. now i need to restart the postgresql server. can i do it as root? or i must be a postgres user for that?

    ReplyDelete
    Replies
    1. i came back, please hier is my ip adress 198.168.178.139, i'm on windows 7, i installed postgresql on a virtual ubuntu machine wo has 192.168.255.139 like ip.
      now i want to connect on the postgres server trough pgadmin on windows, here is what i place in the postgresql.conf : listen_addresses = '*' and
      in the pg_hba.conf:
      #Database administrative login by unix domain socket
      local all postgres trust
      # "local" is for unix domain socket connection only
      local all all trust
      # ipv4 local connection
      host all all 192.168.178.139/32 md5
      host all all 127.0.0.1/32 md5
      host all all 192.168.0.0/24 md5

      mit that configuration i still not having acces to the server on my windows pgadmin. but on ubunto in local there's no problem of connexion.

      thanks for any help please.

      Delete
    2. You can restart postgres as root by entering: "service postgresql restart"

      Delete
    3. There are only 2 things you need to do:
      a) set postgres password
      b)allow remote access in pg_hba.conf

      If that's a Virtual machine you need to have traffic enabled to and from the host and VM. Try sending pings to check whether the traffic is working properly.

      Delete
    4. yes the ping on the ubuntu OS ( 192.168.255.129) is going wihtout a problem. i've restarted the postgresql server. even like that i'm stell not have acces on postgres serveur from my host (pgadmin on windows 7)

      Delete
  6. great tutorial!!! Tks a lot!

    ReplyDelete

If you like this post, please leave a comment :)