How to configure postgresql for the first time

Question

I have just installed postgresql and I specified password x during installation   When I try to do createdb and specify any password I get the message      createdb  could not connect to database postgres  FATAL   password authentication failed for user   Same for createuser   How should I start   Can I add myself as a user to the database

User · Answer

You probably need to update your pg hba conf file  This file controls what users can log in from what IP addresses  I think that the postgres user is pretty locked-down by default

User · Answer

Note  textdb is the database which you are going to explore with  alex  user   root kalilinux    sudo su - postgres  postgres    psql    postgres    create database testdb  postgres    create user alex with password  alex   postgres   GRANT ALL PRIVILEGES ON DATABASE testdb TO alex  enter code here

User · Answer

EDIT  Warning  Please  read the answer posted by Evan Carroll  It seems that this solution is not safe and not recommended   This worked for me in the standard Ubuntu 14 04 64 bits installation   I followed the instructions  with small modifications  that I found in http   suite opengeo org 4 1 dataadmin pgGettingStarted firstconnect html    Install postgreSQL  if not already in your machine     sudo apt-get install postgresql   Run psql using the postgres user   sudo    u postgres psql postgres   Set a new password for the postgres user     password postgres   Exit psql    q   Edit  etc postgresql 9 3 main pg hba conf and change     Database administrative login by Unix domain socket  local    all        postgres                               peer  To     Database administrative login by Unix domain socket  local    all        postgres                               md5   Restart postgreSQL    sudo service postgresql restart   Create a new database   sudo    u postgres createdb mytestdb   Run psql with the postgres user again    psql    U postgres    W   List the existing databases  your new database should be there now      l

User · Answer

If you re running macOS like I am  you may not have the postgres user    When trying to run sudo -u postgres psql I was getting the error sudo  unknown user  postgres  Luckily there are executables that postgres provides    createuser -D  var postgres var-10-local --superuser --username nick createdb --owner nick   Then I was able to access psql without issues    psql psql  10 2  Type  help  for help   nick       If you re creating a new postgres instance from scratch  here are the steps I took  I used a non-default port so I could run two instances   mkdir  var postgres var-10-local pg ctl init -D  var postgres var-10-local   Then I edited  var postgres var-10-local postgresql conf with my preferred port  5433    Applications Postgres app Contents Versions 10 bin postgres -D  Users nick Library Application  Support Postgres var-10-local -p 5433  createuser -D  var postgres var-10-local --superuser --username nick --port 5433 createdb --owner nick --port 5433   Done

User · Answer

Just browse up to your installation s directory and execute this file  pg env bat   so after go at bin folder and execute pgAdmin exe  This must work no doubt

User · Answer

Under Linux PostgresQL is usually configured to allow the root user to login as the postgres superuser postgres from the shell  console or ssh      psql -U postgres   Then you would just create a new database as usual   CREATE ROLE myuser LOGIN password  secret   CREATE DATABASE mydatabase ENCODING  UTF8  OWNER myuser    This should work without touching pg hba conf  If you want to be able to do this using some GUI tool over the network - then you would need to mess with pg hba conf

User · Answer

In MacOS  I followed the below steps to make it work   For the first time  after installation  get the username of the system     cd     pwd  Users someuser   psql -d postgres -U someuser   Now that you have logged into the system  and you can create the DB   postgres   create database mydb  CREATE DATABASE postgres   create user myuser with encrypted password  pass123   CREATE ROLE postgres   grant all privileges on database mydb to myuser  GRANT

User · Answer

This is my solution   su root su postgres psql

User · Answer

The other answers were not completely satisfying to me  Here s what worked for postgresql-9 1 on Xubuntu 12 04 1 LTS    Connect to the default database with user postgres      sudo -u postgres psql template1  Set the password for user postgres  then exit psql  Ctrl-D       ALTER USER postgres with encrypted password  xxxxxxx    Edit the pg hba conf file      sudo vim  etc postgresql 9 1 main pg hba conf   and change  peer  to  md5  on the line concerning postgres      local nbsp  nbsp  nbsp  nbsp  nbsp  nbsp all nbsp  nbsp  nbsp  nbsp  nbsp postgres nbsp  nbsp  nbsp  nbsp  nbsp peer md5   To know what version of postgresql you are running  look for the version folder under  etc postgresql  Also  you can use Nano or other editor instead of VIM  Restart the database       sudo  etc init d postgresql restart    Here you can check if it worked with psql -U postgres   Create a user having the same name as you  to find it  you can type whoami       sudo createuser -U postgres -d -e -E -l -P -r -s  lt my name gt    The options tell postgresql to create a user that can login  create databases  create new roles  is a superuser  and will have an encrypted password  The really important ones are -P -E  so that you re asked to type the password that will be encrypted  and -d so that you can do a createdb   Beware of passwords  it will first ask you twice the new password  for the new user   repeated  and then once the postgres password  the one specified on step 2   Again  edit the pg hba conf file  see step 3 above   and change  peer  to  md5  on the line concerning  all  other users      local nbsp  nbsp  nbsp  nbsp  nbsp  nbsp all nbsp  nbsp  nbsp  nbsp  nbsp all nbsp  nbsp  nbsp  nbsp  nbsp peer md5  Restart  like in step 4   and check that you can login without -U postgres      psql template1   Note that if you do a mere psql  it will fail since it will try to connect you to a default database having the same name as you  i e  whoami   template1 is the admin database that is here from the start  Now createdb  lt dbname gt  should work

User · Answer

There are two methods you can use  Both require creating a user and a database    Using createuser and createdb     sudo -u postgres createuser --superuser  USER   createdb mydatabase   psql -d mydatabase  Using the SQL administration commands  and connecting with a password over TCP    sudo -u postgres psql postgres   And  then in the psql shell  CREATE ROLE myuser LOGIN PASSWORD  mypass   CREATE DATABASE mydatabase WITH OWNER   myuser    Then you can login     psql -h localhost -d mydatabase -U myuser -p  lt port gt    If you don t know the port  you can always get it by running the following  as the postgres user   SHOW port    Or     grep  port     etc postgresql   main postgresql conf    Sidenote  the postgres user  I suggest NOT modifying the postgres user    It s normally locked from the OS  No one is supposed to  log in  to the operating system as postgres  You re supposed to have root to get to authenticate as postgres  It s normally not password protected and delegates to the host operating system  This is a good thing  This normally means in order to log in as postgres which is the PostgreSQL equivalent of SQL Server s SA  you have to have write-access to the underlying data files  And  that means that you could normally wreck havoc anyway  By keeping this disabled  you remove the risk of a brute force attack through a named super-user  Concealing and obscuring the name of the superuser has advantages

[linux] How to configure postgresql for the first time?

Examples related to linux

Examples related to database

Examples related to postgresql

Examples related to configuration