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

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

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools