See all Postgresql related FAQ
How do I create a user account called tom and grant permission for database called jerry when using PostgreSQL database?

You need to use the following commands to add or create a user account and grant permission for database:
Advertisement

  1. adduser – Linux adduser command to add a user to /etc/passwd file
  2. psql – It is a terminal-based front-end to PostgreSQL
  3. CREATE USER – Adds a new user to a PostgreSQL database cluster
  4. CREATE DATABASE – create a new database
  5. 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=> 

🥺 Was this helpful? Please add a comment to show your appreciation or feedback.

nixCrat Tux Pixel Penguin
Hi! 🤠
I'm Vivek Gite, and I write about Linux, macOS, Unix, IT, programming, infosec, and open source. Subscribe to my RSS feed or email newsletter for updates.

47 comments… add one
  • John Varghese Nov 26, 2013 @ 18:21

    Awesome! Thanks.

  • Dtr Apr 11, 2014 @ 8:06

    Is it really necessary to create linux user?
    I followed only steps 4 and 5 and database, user and privileges was created successfully.

  • rubist May 8, 2014 @ 9:22

    Nice post.
    I just created psql user and grant all privileges to the uesr for existing database. Didn’t create any seprate user in Linux. Execution Successfull.
    What will cause if I just created psql user and no such user in linux?

  • j.murray Jun 12, 2014 @ 15:02

    Hi all,
    Firstly i know absolutely nothing about ‘postgre’ so I probably shouldn’t be trying to tinker with it.
    After running a scan I keep getting a mesg. saying;
    ‘The version detected of PostgreSQL 8.x was 8.4.20.14etc. while the latest version including one or more security fixes is 8.4.21.’ ~ when I click on ‘Install Solution’ & follow the instructions I get as far as ‘Please provide a password for service account (postgres).’

    Should I leave well enough alone & is there any any threat to my comp. in doing so?
    Thanks.

    Computers drive me insane!

  • Artro Aug 6, 2015 @ 15:20

    how to give access to just 1 table in the database for reading

  • Phil Oct 1, 2015 @ 11:47

    These days you must also grant connect, also remember that postgres will lowercase symbols like your table/database/user/field names, I think you can wrap them with “ to keep the cases. The standard generally seems to be to use underscores instead.

    GRANT CONNECT ON DATABASE db_name TO user_name;

    It would not work for me without that, for years now. Also remember to connect with -h localhost, or configure your pg_hba.conf (care for potential security risks).

  • Prasad Jan 11, 2016 @ 9:45

    Nice post. Solve my purpose.
    Gone through lots of posts on internet but didn’t work out for me. Finally follow all mentioned steps here and it worked for me.

  • Wally Pitcairn Jul 18, 2017 @ 6:28

    This is a great tip especially to those fresh to the blogosphere. Brief but very precise information

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre> for code samples. Your comment will appear only after approval by the site admin.