PostgreSQL User Creation Using Postgres 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
Post a Comment