Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL User Creation Using Utility

  • createuser creates a new PostgreSQL user. Only superusers (users with usesuper set in the pg_shadow table) can create new PostgreSQL users, so createuser must be invoked by someone who can connect as a PostgreSQL superuser.
  • Being a superuser also implies the ability to bypass access permission checks within the database, so superuserdom should not be granted lightly.
SYNTAX:
createuser [option...] [username]
OPTION
EXPLANATION
username
name of the PostgreSQL user
-a
--adduser
The new user is allowed to create other users. (Note: Actually, this makes the new user a superuser. The option is poorly named.)
-A
--no-adduser
The new user is not allowed to create other users (i.e., the new user is a regular user, not a superuser). This is the default.
-d
--createdb
The new user is allowed to create databases.
-D
--no-createdb
The new user is not allowed to create databases. This is the default.
-e
--echo
Echo the commands that createuser generates and sends to the server.
-E
--encrypted
Encrypts the user's password stored in the database. If not specified, the default password behavior is used. (ENCRYPTED-means password stored as encrypted model so you can’t see actual password but  you can see some encrypted value like “md5df2c887bcb2c49b903aa33bdbc5c2984
-i number
--sysid number
Allows you to pick a non-default user ID for the new user. This is not necessary, but some people like it.
-N
--unencrypted
Does not encrypt the user's password stored in the database. If not specified, the default password behavior is used. (you can see password using PG-SHADOW VIEWS
-P
--pwprompt
(caps letter P)
If given, createuser will issue a prompt for the password of the new user. This is not necessary if you do not plan on using password authentication.
-q
--quiet
Do not display a response.          
-h host
--host host
Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.
-p port
--port port
Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.
-U username
--username username
User name to connect as (not the user name to create).
-W
--password
Force password prompt (to connect to the server, not for the password of the new user).

WHEN you create user on command line mode The database server must be running at the targeted host
--create a u5 user on command mode
[root@p1 bin]# cd /opt/PostgreSQL/9.3/bin/
[root@p1 bin]# ./createuser u5
Password:
postgres=# select usename,passwd,valuntil,usecreatedb from PG_SHADOW;
usename | passwd | valuntil | usecreatedb
----------+-------------------------------------+------------------------+-------------
postgres | md505ea766c2bc9e19f34b66114ace97598 | | t
rep | md5df2c887bcb2c49b903aa33bdbc5c2984 | | f
u1 | | | f
u2 | u2 | | f
u3 | md5dad1ef51b879799793dc38d714b97063 | 2017-06-06 00:00:00-04 | f
nijam | | | f
u4 | md54af10c3137cf79c12265e8d288070711 | | t
u5 | | | f
(8 rows)
----To create the  user u7 using the server on host eden, port 5432, avoiding the prompts and taking a look at the underlying command:
-bash-3.2$ hostname
p1

-bash-3.2$ ./createuser -h p1 -p 5432 -D -A -e u7
Password:
CREATE ROLE u7 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
--Check the user whether created or not
postgres=# \du 
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
nijam | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
rep | Replication +| {}
| 1 connection |
u1 | | {}
u2 | | {}
u3 | Password valid until 2017-06-06 00:00:00-04 | {}
u4 | Create DB | {}
u5 | | {}
u6 | | {}
u7 | | {}
postgres=# select usename,passwd,valuntil,usecreatedb from PG_SHADOW;                          
usename | passwd | valuntil | usecreatedb
----------+-------------------------------------+------------------------+-------------
postgres | md505ea766c2bc9e19f34b66114ace97598 | | t
rep | md5df2c887bcb2c49b903aa33bdbc5c2984 | | f
u1 | | | f
u2 | u2 | | f
u3 | md5dad1ef51b879799793dc38d714b97063 | 2017-06-06 00:00:00-04 | f
nijam | | | f
u4 | md54af10c3137cf79c12265e8d288070711 | | t
u5 | | | f
u6 | | | f
u7 | | | f
(10 rows)
--To create the user joe as a superuser, and assign a password immediately:
-bash-3.2$ ./createuser -P -d -a -e u8
Enter password for new role:
Enter it again:
Password:
CREATE ROLE u8 PASSWORD 'md5b9f930ae0484417a1883fd3f7cdb490e' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;
Note:Here first asking password for new user second is confirmation third is super user password
--let us check the new user roles
postgres=# \du                                                       
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
nijam | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
rep | Replication +| {}
| 1 connection |
u1 | | {}
u2 | | {}
u3 | Password valid until 2017-06-06 00:00:00-04 | {}
u4 | Create DB | {}
u5 | | {}
u6 | | {}
u7 | | {}
u8 | Superuser, Create role, Create DB | {}

Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL