Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL User Alter

  • 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.
  • Do you want to know more about pg_shadow & pg_authid  click the link pg_shadow & pg_authid 
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    | {}

<<< 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