Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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"
UNENCRYPTED means you can see actual password in PG_SHADOW & PG_AUTHID Views)
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
--create the unencrypted user 
postgres=# create user u2 WITH UNENCRYPTED PASSWORD 'u2';
CREATE ROLE
--create the user with password validation time
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)

<<< Preview                                                                                                                 Next>>>

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

How to Enable/Disable autovacuum on PostgreSQL