How to create a user account called tom and grant permission for database called jerry?
You need to use following commands.
=> adduser - UNIX/Linux adduser command to add a user to /etc/passwd file
=> 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 tomStep # 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 - postgresStep #3: Now connect to database server
Type the following command
OR
Output:
$ psql template1OR
$ psql -d template1 -U postgresOutput:
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):
Now grant all privileges on database
Type \q to quit:
template1=# CREATE DATABASE jerry;Now grant all privileges on database
template1=# GRANT ALL PRIVILEGES ON DATABASE jerry to tom;Type \q to quit:
template1=# \qStep #6: Test tom user login
In order to login as tom you need to type following commands. Login as tom or use su command:
Output:
$ su - tom
$ psql -d jerry -U tomOutput:
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=>
