Categoria: Postgresql

A. You need to use following commands.
=> adduser – UNIX/Linux adduser command to add a user to /etc/passwd file

=> psql => It is a terminal-based front-end to PostgreSQL.

=> CREATE USER – Adds a new user to a PostgreSQL database cluster.

=> CREATE DATABASE – create a new database

=> GRANT ALL PRIVILEGES – define access privileges

Procedure to add a user to PostgreSQL database

To create a normal user and an associated database you need to type the following commands. The easiest way to use is to create a Linux / UNUX IDENT authentication i.e. add user tom to UNIX or Linux system first.

Step # 1: Add a Linux/UNIX user called tom

Type the following commands to create a UNIX/Linux user called tom:
# adduser tom
# passwd tom


Step # 2: Becoming a superuser

You need to login as database super user under postgresql server. Again the simplest way to connect as the postgres user is to change to the postgres unix user on the database server using su command as follows:
# su - postgres

Step #3: Now connect to database server

Type the following command
$ psql template1
OR
$ psql -d template1 -U postgres
Output:

Welcome to psql 7.4.16, the PostgreSQL interactive terminal.
Type:  copyright for distribution terms
       h for help with SQL commands
       ? for help on internal slash commands
       g or terminate with semicolon to execute query
       q to quit
template1=#

Step #4: Add a user called tom

Type the following command to create a user called tom with a password called myPassword (you need to type command highlighted with red color):
template1=# CREATE USER tom WITH PASSWORD 'myPassword';

Step #5: Add a database called jerry

Type the following command (you need to type command highlighted with red color):
template1=# CREATE DATABASE jerry;
Now grant all privileges on database
template1=# GRANT ALL PRIVILEGES ON DATABASE jerry to tom;
Type q to quit:
template1=# q

Step #6: Test tom user login

In order to login as tom you need to type following commands. Login as tom or use su command:
$ su - tom
$ psql -d jerry -U tom

Output:

Welcome to psql 7.4.16, the PostgreSQL interactive terminal.
Type:  copyright for distribution terms
       h for help with SQL commands
       ? for help on internal slash commands
       g or terminate with semicolon to execute query
       q to quit
jerry=>

source:http://www.cyberciti.biz/faq/howto-add-postgresql-user-account/

By default, connection via TCP/IP is disabled. PostgreSQL supports multiple client authentication methods. By default, IDENT authentication method is used for postgres and local users. Please refer the PostgreSQL Administrator’s Guide.

The following discussion assumes that you wish to enable TCP/IP connections and use the MD5 method for client authentication. PostgreSQL configuration files are stored in the /etc/postgresql/<version>/main directory. For example, if you install PostgreSQL 8.4, the configuration files are stored in the /etc/postgresql/8.4/main directory.

To enable TCP/IP connections, edit the file /etc/postgresql/8.4/main/postgresql.conf

Locate the line #listen_addresses = ‘localhost’ and change it to:

listen_addresses = 'localhost'

You may also edit all other parameters, if you know what you are doing! For details, refer to the configuration file or to the PostgreSQL documentation.

Now that we can connect to our PostgreSQL server, the next step is to set a password for the postgres user. Run the following command at a terminal prompt to connect to the default PostgreSQL template database:

sudo -u postgres psql template1

The above command connects to PostgreSQL database template1 as user postgres. Once you connect to the PostgreSQL server, you will be at a SQL prompt. You can run the following SQL command at the psql prompt to configure the password for the user postgres.

ALTER USER postgres with encrypted password 'your_password';

After configuring the password, edit the file /etc/postgresql/8.4/main/pg_hba.conf to use MD5 authentication with the postgres user:

local   all         postgres                          md5

Finally, you should restart the PostgreSQL service to initialize the new configuration. From a terminal prompt enter the following to restart PostgreSQL:

sudo /etc/init.d/postgresql-8.4 restart

 

source:https://help.ubuntu.com/10.04/serverguide/postgresql.html