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:
  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
$ psql -d template1 -U postgres

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


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


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


🐧 Get the latest tutorials on Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

🐧 47 comments so far... add one

CategoryList of Unix and Linux commands
Disk space analyzersdf duf ncdu pydf
File Managementcat cp mkdir tree
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Modern utilitiesbat exa
Network UtilitiesNetHogs dig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg glances gtop jobs killall kill pidof pstree pwdx time vtop
Searchingag grep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
47 comments… add one
  • Mike Jan 24, 2008 @ 1:19

    How can I do these same steps but on Windows?

    Many thanks,

    • parth Jan 3, 2011 @ 7:34

      write the same commands in cmd or just use “pgadmin” to do this stuff

      Njoy :)

  • windows Jul 17, 2008 @ 20:54

    Mike while running windows:

    open up a command window and run:
    format c:

    that will get rid of your windows issue nicely. ;) Before you do that you might want to have a linux cd handy recommending ubuntu.

    • Josh M. Feb 19, 2011 @ 4:29

      Funny thing is you wouldn’t even need instructions to do this stuff in Windows. But in a cryptic Linux OS nothing is obvious.

      • David Sep 19, 2011 @ 21:13

        Cryptic is your brain !!!

  • vishwas Aug 5, 2008 @ 9:41

    it is works thank u for helping me

  • roger Dec 1, 2008 @ 21:51

    Works! Awesome!

  • Allan Anderson Jan 12, 2009 @ 22:53

    This is the first (right on instructions) I have ever had the privilege to use and the only ones I have ever used that are right on.
    Where can I find more of these easy to use fully explained to the smallest detail instructions.

  • Xav Feb 18, 2009 @ 9:24

    Great help thanks, and excellent humor also. I wonder why Mike has not responded yet LOL…

  • semuta Feb 28, 2009 @ 0:40

    Thank you for the concise commands.

  • Arteal Mar 7, 2009 @ 21:34

    This is great beginner’s help, thanks :) But Will be great to explain here how to setup pqsql user without creating UNIX user, because i seem this as not so great resolution to create UNIX user for every pgsql user. Anyway, thanks for help once more :)

  • cam Mar 15, 2009 @ 23:59

    Awesome worked a treat thanks a lot. :D

  • joshg May 1, 2009 @ 15:57

    Great, simple and concise. Thanks a lot.

  • AfroSoft May 17, 2009 @ 21:55

    This is where windows informations can come useful.

  • SIFE Mar 13, 2010 @ 11:29

    go to:
    start->programs->postgreSQL 8.*->SQL shell
    login with your postgres user and password then type this commands in this order :
    CREATE USER win;

    change win to a defined user by you ,and test to a database you want .

  • John Jul 29, 2010 @ 6:51

    And to undo:

    DROP USER tom

    • drone115b Aug 20, 2010 @ 19:54

      So how do you query the permissions a user has? It seems naturaly that, after changing these settings a careful admin will want to verify them.

  • gnubala Sep 7, 2010 @ 13:19

    i need help
    i need to see how many users have created. is it possible?
    is any command is there for listing the users in postgres?

  • lakshmi Sep 13, 2010 @ 10:04

    this information is gud but their should be continuation to another relevant topic

  • gnubala Sep 14, 2010 @ 12:32

    using \du command we can see the list of users which we have created

  • Jaysunn Mar 5, 2011 @ 17:53

    I used this great quick tutorial to get me out of a place that I was unfamiliar with. Thanks!!!

    Question 1. what the heck does postgressql mean, the name?
    I see post, but gre? Maybe you know?


    • 🐧 nixCraft Mar 7, 2011 @ 12:45


      PostgreSQL[1] evolved from the Ingres project at the University of California, Berkeley. In 1982, the project leader, Michael Stonebraker, left Berkeley to make a proprietary version of Ingres. He returned to Berkeley in 1985 and started a post-Ingres project to address the problems with contemporary database systems that had become increasingly clear during the early 1980s. The new project, Postgres, aimed to add the fewest features needed to completely support types. These features included the ability to define types and to fully describe relationships – something used widely before but maintained entirely by the user. In Postgres, the database “understood” relationships, and could retrieve information in related tables in a natural way using rules. Postgres used many of the ideas of Ingres, but not its code.

      [1] Source: Wikipedia


  • Jeff Mar 9, 2011 @ 18:30

    Great instructions. Thank you very much! On to figure out how to login to the database via PHP…

  • suhail Mar 11, 2011 @ 9:16

    just wanted to add

    to create with UTF8 :-


  • JACOB OBINGUAR Mar 28, 2011 @ 0:43

    Hi! I am new to PostgreSQL. This page has so much help! Thanks a lot!!! Just one more question, how do give additional roles to the user you have created like (create role, create db etc..)? Once again, thanks!

  • Ralph Siegler Sep 13, 2011 @ 16:43

    Nonsense, GNU/Linux admin is much easier than Windows. I speak as former Windows and Metaframe admin. The Windows admins at my employer spend more time by a factor of three to admin each windows box as I do with all the Linux boxes together. The command line admin of postgresql in windows is the same, or you can use GUI tools to admin it the same way in Windows as Linux. What you are really saying is you only want to stay with one way of doing things without learning any better way. Windows is totally crippled and featureless compared to what GNU/Linux can do, and is slower and requires twice the RAM for a given job. And to show my objectiveness, there are even better OS than Linux, the BSD for example like FreeBSD or OpenBSD are of even higher quality, or OpenSolaris. All those can also run Postgresql, though FreeBSD would probably be the fastest.

  • Arseny May 23, 2012 @ 15:34

    Is it necessary to create UNIX-account? Is it possible to connect to PostgreSQL without UNIX-accounts?

    • jamey Oct 1, 2012 @ 12:07

      I didn’t find it necessary to do so, that’s why you have the alternative command of psql -U

      I think it is to make it easier for some or if you wanted to have a separate linux account to handle these things on the system.

  • Rohan Jul 4, 2012 @ 5:10

    hay last step doesent work..
    it gives me error massage
    psql: FATAL: no pg_hba.conf entry for host “[local]”, user “rohan”, database “shree1”, SSL off

  • Infoholico Aug 2, 2012 @ 13:03

    Hello, thanks for this “how to”, but when adding “su – postgres” command line in my Terminal it asks for a password, I’ve introduced my password, the MySQL Password and the Unix Password it asked when creating gnuhealth user but none of them work what am I doing wrong?

  • Ali Ardestani Aug 17, 2012 @ 23:08

    $ psql -d myDb -U username -W

    needs to change to

    $ psql -h localhost -d myDb -U username -W

    The first one gave me IDENT error

    • Mike Aug 23, 2012 @ 4:38

      That depends. If you have authentication set to “peer” in your /etc/postgresql/9.1/main/pg_hba.conf file, then you can either change that to “md5”, or use the “-h localhost” or “-h” approach. If you already are using “md5” for authentication in that file, then the command is fine as it is, and you don’t need to use “-h localhost”.

    • max Aug 25, 2012 @ 19:36

      Thank you for this! Was wondering where the password was supposed to be entered.

    • jamey Oct 1, 2012 @ 12:09

      As I’m only accessing from localhost I have to specify -h localhost

      I enjoy this default behavior as it is better defaults from a security standpoint.

  • renoidooizawa Aug 24, 2012 @ 9:00

    Great! as newbie I need instruction as simple as this!

  • Sowbi Sep 7, 2012 @ 7:08

    When i found critical to do this task. your commands helps out to do fast..
    Many thanks for your work.. Kepp on going..

  • peter Nov 29, 2012 @ 22:03

    Thanks you so much!
    What a nightmare to get this going. I think postgres could be much more popular if they just added a default user with a default password with a default database or the power to create one, and also allowed connections from other computers without having to dig through a million documents. I got a 1000 page book on postgres and still couldn’t get it to work. It looks like a good database, but they really try hard to keep the learning curve really steep.
    In any case, thanks again!

  • name Feb 18, 2013 @ 9:01

    And again it saved my day.

    Thank you very much!

  • amir Sep 29, 2013 @ 11:55

    copy privileges from one user to another:

    GRANT user1 TO user2;

  • 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 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?

    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 Appreciate your sharing this one. A must read post!

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre> for code samples. Still have questions? Post it on our forum