PostgreSQL User Creation
- CREATE USER adds a new user to a PostgreSQL database cluster
- when the user create a table, those table belong to the PUBLIC schema,Database can be split up between multiple users using schemas.
SYNTAX:
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'
Explanation:
PARAMETERS
|
EXPLANATION
|
name
|
The name of the new user
|
uid
|
user ID of the new user
|
CREATEDB|
NOCREATEDB
|
If CREATEDB is specified, the user being defined will be allowed to create his own databases. Using NOCREATEDB will deny a user the ability to create databases. If not specified, NOCREATEDB is the default.
|
CREATEUSER|
NOCREATEUSER
|
user will be permitted to create new users himself. If not specified, NOCREATEUSER is the default.
|
groupname
|
A name of an existing group into which to insert the user as a new member
|
password
|
Sets the user's password
|
ENCRYPTED
UNENCRYPTED
|
system cannot decrypt the specified encrypted password string DEFAULT is ENCRYPTED
(ENCRYPTED-means password stored as encrypted model so you can’t see actual password but you can see encrypted password like "md505ea766c2bc9e19f34b66114ace97598"
|
abstime
|
VALID UNTIL Password validation time. If this clause is omitted the password will be valid for all time.
|
Database User Creation:
--create simple user
postgres=# CREATE USER nijam; CREATE ROLE
postgres=# create user u2 WITH UNENCRYPTED PASSWORD 'u2'; CREATE ROLE
postgres=# CREATE USER u3 WITH PASSWORD 'u3' VALID UNTIL '2017-06-06'; CREATE ROLE
--Here createdb privillege having postgres(true values) user only so just createdb privilege u4 user
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 (6 rows)
--create u4 user
postgres=# CREATE user u4 WITH PASSWORD 'u4' CREATEDB; CREATE ROLE
--then ckeck the privileges
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 (7 rows)
Comments
Post a Comment