-
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
-
Alter PostgreSQL User/Role/group
- ALTER ROLE changes the attributes of a PostgreSQL role.
- If you want to change a user you can use the ALTER USER SQL command, which is similar to the CREATE USER command except you can’t change the sysid
Syntax:
ALTER USER role_specification [ WITH ] option [ ... ] where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' ALTER USER name RENAME TO new_name ALTER USER role_specification SET configuration_parameter { TO | = } { value | DEFAULT } ALTER USER role_specification SET configuration_parameter FROM CURRENT ALTER USER role_specification RESET configuration_parameter ALTER USER role_specification RESET ALL where role_specification can be: [ GROUP ] role_name | CURRENT_USER | SESSION_USER
Example:
practical 1.Listing users
- You can see the users on the server by selecting from the pg_shadow & pg_authid system table. If you are not a super user, you will not have permission to access this table and will have to access the pg_user view instead, which is identical, but displays the password as stars.
db2=# select * from pg_shadow; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig ----------+----------+-------------+----------+-----------+---------+-------------------------------------+-----------------------------------+--- postgres | 10 | t | t | t | t | md505ea766c2bc9e19f34b66114ace97598 | | rep | 24576 | f | f | f | t | md5df2c887bcb2c49b903aa33bdbc5c2984 | | nijam | 24586 | f | f | f | f | | | u8 | 24591 | t | t | t | f | md5b9f930ae0484417a1883fd3f7cdb490e |2015-05-09 12:00:00-04| u9 | 24640 | f | f | f | f | md531f95351422eab63b8b270c140f60c2a |2015-05-04 12:00:00-04| u1 | 24648 | f | f | f | f | md58026a39c502750413402a90d9d8bae3c | | u2 | 24649 | f | f | f | f | md5a76d8c8015643c6a837661a10142016e | | u4 | 24657 | f | f | f | f | md54af10c3137cf79c12265e8d288070711 | | u5 | 24658 | f | f | f | f | md507a832ae72c9e818c5297f366284fb8a | | u3 | 24660 | f | f | f | f | | | (10 rows)
db2=# select * from pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig ----------+----------+-------------+----------+-----------+---------+----------+----------------------+----------- postgres | 10 | t | t | t | t | ******** | | rep | 24576 | f | f | f | t | ******** | | nijam | 24586 | f | f | f | f | ******** | | u8 | 24591 | t | t | t | f | ******** |2015-05-09 12:00:00-04| u9 | 24640 | f | f | f | f | ******** |2015-05-04 12:00:00-04| u1 | 24648 | f | f | f | f | ******** | | u2 | 24649 | f | f | f | f | ******** | | u4 | 24657 | f | f | f | f | ******** | | u5 | 24658 | f | f | f | f | ******** | | u3 | 24660 | f | f | f | f | ******** | | (10 rows)
db2=# select * from pg_authid; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil ----------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------------------------------+--------------- postgres | t | t | t | t | t | t | t | -1 | md505ea766c2bc9e19f34b66114ace97598 | rep | f | t | f | f | f | t | t | 1 | md5df2c887bcb2c49b903aa33bdbc5c2984 | nijam | f | t | f | f | f | t | f | -1 | | u8 | t | t | t | t | t | t | f | -1 | md5b9f930ae0484417a1883fd3f7cdb490e |2015-05-09 12:00:00-04 u9 | f | t | f | f | f | t | f | -1 | md531f95351422eab63b8b270c140f60c2a |2015-05-04 12:00:00-04 u1 | f | t | f | f | f | t | f | -1 | md58026a39c502750413402a90d9d8bae3c | u2 | f | t | f | f | f | t | f | -1 | md5a76d8c8015643c6a837661a10142016e | u4 | f | t | f | f | f | t | f | -1 | md54af10c3137cf79c12265e8d288070711 | u5 | f | t | f | f | f | t | f | -1 | md507a832ae72c9e818c5297f366284fb8a | u3 | f | t | f | f | f | t | f | -1 | | (10 rows)
–Changing a user password
One of the most common reasons for wanting to alter a user is to change the user’s password
db2=# alter user u2 with password 'u23'; ALTER ROLE
–Remove a role’s password
db2=# ALTER ROLE u5 WITH PASSWORD NULL; ALTER ROLE
–Change a password expiration date from “May 4 12:00:00 2015” to “May 8 12:00:00 2018”
db2=# ALTER ROLE u9 VALID UNTIL 'May 8 12:00:00 2018'; ALTER ROLE
–Make a password valid forever:
db2=# ALTER ROLE u8 VALID UNTIL 'infinity'; ALTER ROLE
–Say we wanted to allow u1 user to create databases and create role
db2=# ALTER ROLE u1 CREATEROLE CREATEDB; ALTER ROLE
–Checking pg_shadow again, we can see the changes highligted in red color
db2=# select * from pg_shadow; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig ----------+----------+-------------+----------+-----------+---------+-------------------------------------+------------------------+----------- postgres | 10 | t | t | t | t | md505ea766c2bc9e19f34b66114ace97598 | | rep | 24576 | f | f | f | t | md5df2c887bcb2c49b903aa33bdbc5c2984 | | nijam | 24586 | f | f | f | f | | | u4 | 24657 | f | f | f | f | md54af10c3137cf79c12265e8d288070711 | | u1 | 24648 | t | f | f | f | md53ac33e1b7b89b332aef1b757828dc8eb | | u2 | 24649 | f | f | f | f | md57a7c3259d1ebc74d9119be56686b591a | | u3 | 24660 | f | f | f | f | | | u5 | 24658 | f | f | f | f | | | u9 | 24640 | f | f | f | f | md531f95351422eab63b8b270c140f60c2a | 2018-05-08 12:00:00-04 | u8 | 24591 | t | t | t | f | md5b9f930ae0484417a1883fd3f7cdb490e | infinity | (10 rows)
–Rename the user from nijam to nijamutheen
db2=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------+----------- nijam | | {} postgres | Superuser, Create role, Create DB, Replication | {} rep | Replication +| {} | 1 connection | u1 | Create role, Create DB | {} u2 | | {} u3 | | {} u4 | | {} u5 | | {} u8 | Superuser, Create role, Create DB +| {} | Password valid until infinity | u9 | Password valid until 2018-05-08 12:00:00-04 | {}
db2=# alter user nijam rename to nijamutheen; ALTER ROLE
db2=# \du List of roles Role name | Attributes | Member of -------------+------------------------------------------------+----------- nijamutheen | | {} postgres | Superuser, Create role, Create DB, Replication | {} rep | Replication +| {} | 1 connection | u1 | Create role, Create DB | {} u2 | | {} u3 | | {} u4 | | {} u5 | | {} u8 | Superuser, Create role, Create DB +| {} | Password valid until infinity | u9 | Password valid until 2018-05-08 12:00:00-04 | {}
Next
PostgreSQL Schema