Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will discuss PostgreSQL USER EXPIRE. In PostgreSQL, a password must expire to prevent a user from logging in after a specified period.
This mechanism is essential for enhancing security and managing user access according to defined policies.
You can increase or decrease the expiration period by +/- days for all users or increase or decrease only one user by -/+ days with the SQL statement below.
The expiredate function below adds a specified number of days to the specific user or all users.
If no expiration time is specified when creating a user, additions or removals are made based on the time the function is run.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | create or replace function expiredate(deger text,sayi int ,userr text = NULL ) RETURNS text AS $$ declare user_name text; BEGIN IF userr is not null and deger ='+' THEN FOR user_name IN select case when valuntil is null then 'ALTER USER ' || usename|| ' VALID UNTIL '''|| TO_CHAR(now(),'YYYY,MM,DD,HH24:MI')::date+sayi ||''';' when valuntil='infinity' then 'ALTER USER ' || usename|| ' VALID UNTIL '''|| TO_CHAR(now(),'YYYY,MM,DD,HH24:MI')::date+sayi ||''';' when valuntil is not null then 'ALTER USER ' || usename|| ' VALID UNTIL '''|| TO_CHAR(valuntil,'YYYY,MM,DD,HH24:MI')::date+sayi ||''';' end as expiredate from pg_user where usename <> 'postgres'and usename=userr LOOP EXECUTE user_name ; END LOOP; END IF; IF userr is not null and deger ='-' THEN FOR user_name IN select case when valuntil is null then 'ALTER USER ' || usename|| ' VALID UNTIL '''|| TO_CHAR(now(),'YYYY,MM,DD,HH24:MI')::date-sayi ||''';' when valuntil='infinity' then 'ALTER USER ' || usename|| ' VALID UNTIL '''|| TO_CHAR(now(),'YYYY,MM,DD,HH24:MI')::date-sayi ||''';' when valuntil is not null then 'ALTER USER ' || usename|| ' VALID UNTIL '''|| TO_CHAR(valuntil,'YYYY,MM,DD,HH24:MI')::date-sayi ||''';' end as expiredate from pg_user where usename <> 'postgres' and usename=userr LOOP EXECUTE user_name ; END LOOP; END IF; IF userr is null and deger ='+' THEN FOR user_name IN select case when valuntil is null then 'ALTER USER ' || usename|| ' VALID UNTIL '''|| TO_CHAR(now(),'YYYY,MM,DD,HH24:MI')::date+sayi ||''';' when valuntil='infinity' then 'ALTER USER ' || usename|| ' VALID UNTIL '''|| TO_CHAR(now(),'YYYY,MM,DD,HH24:MI')::date+sayi ||''';' when valuntil is not null then 'ALTER USER ' || usename|| ' VALID UNTIL '''|| TO_CHAR(valuntil,'YYYY,MM,DD,HH24:MI')::date+sayi ||''';' end as expiredate from pg_user where usename <> 'postgres' LOOP EXECUTE user_name ; END LOOP; END IF; IF userr is null and deger ='-' THEN FOR user_name IN select case when valuntil is null then 'ALTER USER ' || usename|| ' VALID UNTIL '''|| TO_CHAR(now(),'YYYY,MM,DD,HH24:MI')::date-sayi ||''';' when valuntil='infinity' then 'ALTER USER ' || usename|| ' VALID UNTIL '''|| TO_CHAR(now(),'YYYY,MM,DD,HH24:MI')::date-sayi ||''';' when valuntil is not null then 'ALTER USER ' || usename|| ' VALID UNTIL '''|| TO_CHAR(valuntil,'YYYY,MM,DD,HH24:MI')::date-sayi ||''';' end as expiredate from pg_user where usename <> 'postgres'LOOP EXECUTE user_name ; END LOOP; END IF; return ' Expire süresi ' ||deger||''||sayi|| ' gün olarak değiştirilmiştir.'; end; $$ language plpgsql VOLATILE ; |
With the help of the following SQL statement, we change the password of all users on PostgreSQL to -1.
1 | select expiredate('-', 1); |
We can use the following command to increase the expiration time of all users by 10 days.
1 | select expiredate('+', 10); |
You can use it as follows to increase or decrease the time for a specific user only.
1 | select expiredate('-', 1,'farukerdem') |
