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
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_USERExample: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 | {}
Comments
Post a Comment