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

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