How to Create User in PostgreSQL

November 1, 2019

In this PostgreSQL tutorial, we will learn abut PostgreSQL create user command which is used to create a database user. Super user in postgres is “postgres” which is the default user and is being created while installation.

A PostgreSQL server contains one or more databases. Users and groups of users are shared across the entire cluster, but no other data is shared across the databases. Any client connection to the server can access only the data in a single database, the one specified in the connection request.

How to check index on a Table and its Size in Oracle

Super user account in postgres is : postgres which is the database user and also OS user having all the access like :Superuser, Create role, Create DB, Replication, Bypass RLS etc.

ROLES: PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up.

To check list of users or roles and the privilege assigned to it: 

 postgres=# \du
                                    List of roles
  Role name |                         Attributes                         | Member of
 -----------+------------------------------------------------------------+-----------
  postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

As you can see, there is only one default role [postgres], and it has many powerful privileges.

Steps to Create User in PostgreSQL

STEP 1: Login to the Linux server using postgres OS User

# su- postgres

STEP 2: connect to the database.

If you will not specify any database name then it will connect to postgres default database.

$ psql
 postgres=#

Get list of all database in postgresql 
postgres=# select datname from pg_database;
   datname
 postgres
  template1
  template0
 (3 rows)

STEP 3: Creating PostgreSQL user.

postgres=# create user test with password 'test123';

STEP 4: Create one database so that user can login to the database.

If you want you can connect to any of your database and grant access to the user on that database.

 postgres=# create database testdb

You can also create database from the Linux terminal using postgres user.
bash-4.1$ createdb pgdb

Step 5: Grant database access to the new user.

 postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb to test;

STEP 6: Create Linux user account at OS Level for the same database user.

Login to Linux using root and create same user “test” at OS Level to allow user to login to database without password.

# adduser test
# passwd test

STEP 7: Test user login to the database without password.

[root@orahow postgres_db]# su test
 [test@orahow postgres_db]$ psql -d pgdb
 psql (9.6.0)
 Type "help" for help.
 pgdb=>

You can also login to the database using database name.

$ psql -d pgdb -U test -w test123

Synopsis:

CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:
    
      SYSID uid 
    | CREATEDB | NOCREATEDB
    | CREATEUSER | NOCREATEUSER
    | IN GROUP groupname [, ...]
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'abstime'

Examples:

create user test with password 'test123';

Create a user with with create database privilege.

CREATE USER devdemo WITH PASSWORD 'P0stgResq1' CREATEDB;

You can also add a user to an existing group and specify a date when the user’s password will expire. 

template1=# CREATE USER testuser WITH PASSWORD 'pAssw0rd';
 CREATE USER
 template1=# CREATE USER testuser VALID UNTIL 'Jan 31 2030';
 CREATE USER
PostgreSQL Download

Must Read: