-
BASIC POSTGRESQL
6-
Lecture1.1
-
Lecture1.2
-
Lecture1.3
-
Lecture1.4
-
Lecture1.5
-
Lecture1.6
-
-
VMWARE & POSTGRESQL INSTALLATION
9-
Lecture2.1
-
Lecture2.2
-
Lecture2.3
-
Lecture2.4
-
Lecture2.5
-
Lecture2.6
-
Lecture2.7
-
Lecture2.8
-
Lecture2.9
-
-
POSTGRESQL DATABASE
6-
Lecture3.1
-
Lecture3.2
-
Lecture3.3
-
Lecture3.4
-
Lecture3.5
-
Lecture3.6
-
-
POSTGRESQL TABLE
16-
Lecture4.1
-
Lecture4.2
-
Lecture4.3
-
Lecture4.4
-
Lecture4.5
-
Lecture4.6
-
Lecture4.7
-
Lecture4.8
-
Lecture4.9
-
Lecture4.10
-
Lecture4.11
-
Lecture4.12
-
Lecture4.13
-
Lecture4.14
-
Lecture4.15
-
Lecture4.16
-
-
USER/OBJECTS PRIVILEGE AND ROLES ASIGNING
3-
Lecture5.1
-
Lecture5.2
-
Lecture5.3
-
-
TRANSACTIONS - MVCC
3-
Lecture6.1
-
Lecture6.2
-
Lecture6.3
-
-
POSTGRESQL USER/SCHEMA MANAGEMENT
9-
Lecture7.1
-
Lecture7.2
-
Lecture7.3
-
Lecture7.4
-
Lecture7.5
-
Lecture7.6
-
Lecture7.7
-
Lecture7.8
-
Lecture7.9
-
-
POSTGRESQL CONSTRAINTS
6-
Lecture8.1
-
Lecture8.2
-
Lecture8.3
-
Lecture8.4
-
Lecture8.5
-
Lecture8.6
-
-
POSTGRESQL ADVANCE DATA TYPE
5-
Lecture9.1
-
Lecture9.2
-
Lecture9.3
-
Lecture9.4
-
Lecture9.5
-
-
POSTGRESQL VIEWS
1-
Lecture10.1
-
-
POSTGRESQL MONITORING OBJECT USUAGE/SIZE
1 -
POSTGRESQL DATABASE ARCHITECTURE
4-
Lecture12.1
-
Lecture12.2
-
Lecture12.3
-
Lecture12.4
-
-
POSTGRESQL BACKUP AND RECOVERY
13-
Lecture13.1
-
Lecture13.2
-
Lecture13.3
-
Lecture13.4
-
Lecture13.5
-
Lecture13.6
-
Lecture13.7
-
Lecture13.8
-
Lecture13.9
-
Lecture13.10
-
Lecture13.11
-
Lecture13.12
-
Lecture13.13
-
-
POSTGRESQL PERFORMANCE TUNING
5-
Lecture14.1
-
Lecture14.2
-
Lecture14.3
-
Lecture14.4
-
Lecture14.5
-
-
HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION
11-
Lecture15.1
-
Lecture15.2
-
Lecture15.3
-
Lecture15.4
-
Lecture15.5
-
Lecture15.6
-
Lecture15.7
-
Lecture15.8
-
Lecture15.9
-
Lecture15.10
-
Lecture15.11
-
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
— Creating u5 user using createuser utility
[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 p1, 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 u7 whether successfully 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: when issuing the above utility you will get password prompt first prompt is u8 user password, second one is u8 user password confirmation and third prompt is super user password .
— let us check the new user roles and privileges whether u8 user having above mentioned privilege
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 | {}