4

I want to create user or change password with a timestamp calculated over current time. example:

# CREATE USER user WITH PASSWORD 'password12345678' VALID UNTIL '(NOW() + interval 1 month)';

of course it's not valid:

ERROR: invalid input syntax for type timestamp with time zone: "(NOW() + interval 1 month)"

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Mario L
  • 63
  • 5

2 Answers2

3

Utility commands like CREATE USER do not accept expressions, only literals. You need dynamic SQL.

DO
$do$
BEGIN
   EXECUTE format($$CREATE USER myuser WITH PASSWORD 'password12345678' VALID UNTIL %L$$, NOW() + interval '1 month');
END
$do$;

Detailed explanation:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

I am not familiar with postgresql but from a quick google search it appears you want the following:

CURRENT_DATE + INTERVAL '1 month';
haag1
  • 352
  • 1
  • 13
  • yes, but "VALID UNTIL" doesn't accept this kind of input. # CREATE USER cdb WITH PASSWORD 'password12345678' VALID UNTIL 'CURRENT_DATE + INTERVAL '1 month''; ERROR: syntax error at or near "1" LINE 1: ...rd12345678' VALID UNTIL 'CURRENT_DATE + INTERVAL '1 month''; – Mario L Feb 01 '19 at 16:29