current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 86 down vote favorite
31

I have installed PostgreSQL 8.4 on my CentOS server and connected to root user from shell and accessing the PostgreSQL shell.

I created the database and user in PostgreSQL.

While trying to connect from my PHP script it shows me authentication failed.

How do I create a new user and how to grant permissions to them for a particular DB?

share|improve this question

closed as off topic by tereško, Dejan Marjanovic, Quentin, vascowhite, hakre Jun 2 '12 at 11:36

Questions on Stack Overflow are expected to relate to programming within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.
    
what exactly is the error message? can you log in through psql from the commandline? – a_horse_with_no_name Jun 2 '12 at 10:32
up vote 165 down vote

From CLI:

$ su - postgres 
$ psql template1
template1=# CREATE USER tester WITH PASSWORD 'test_password';
template1=# GRANT ALL PRIVILEGES ON DATABASE "test_database" to tester;
template1=# \q

PHP (as tested on localhost, it works as expected):

  $connString = 'port=5432 dbname=test_database user=tester password=test_password';
  $connHandler = pg_connect($connString);
  echo 'Connected to '.pg_dbname($connHandler);
share|improve this answer
    
I have already tried this. – user925355 Jun 2 '12 at 9:56
7  
For Ubuntu it should be sudo su - postgres – Hengjie Jan 27 '13 at 11:36
25  
For Ubuntu it should really be: sudo -u postgres psql – Mario Jan 31 '14 at 7:59
    
I think the most portable way to issue the commands would be: psql -U postgres -c "CREATE ROLE..." – hugo_leonardo Mar 20 '14 at 14:24
13  
Just remember that all the stuff you type and run in the shell usually ends up in shell history, including your chosen password. – amn Jun 24 '14 at 15:57
up vote 17 down vote

Create the user with a password :

http://www.postgresql.org/docs/current/static/sql-createuser.html

CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

Then grant the user rights on a specific database :

http://www.postgresql.org/docs/current/static/sql-grant.html

Example :

grant all privileges on database db_name to someuser;
share|improve this answer
1  
I have already done this: create user ravi with password 'ravi'; grant all privileges on database nominatim to ravi; But I can not connect with following PHP code: <?php $connString = 'host=localhost port=5432 dbname=nominatim user=ravi password=ravi'; $connHandler = pg_connect($connString); echo 'Connected to '.pg_dbname($connHandler); ?> – user925355 Jun 2 '12 at 9:52
    
@Darji Krunal: what's the PHP error, how does it look like? – Vidul Jun 2 '12 at 10:02
    
grant all privileges* ... – Arpit Singh Apr 13 '15 at 7:35