How to create user for a db in postgresql?

user925355 picture user925355 · Jun 2, 2012 · Viewed 307.5k times · Source

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?

Answer

Vidul picture Vidul · Jun 2, 2012

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);