I have one question that I cannot figure out 100% sure answer.
Lets say that in schema Main I have following table:
CREATE TABLE Accounts (
UID char(43) PRIMARY KEY CHECK ( UID <> '' ),
Login varchar(320) UNIQUE NOT NULL CHECK ( Login <> '' ),
Password char(32) NOT NULL,
Active boolean DEFAULT FALSE,
Valid boolean DEFAULT FALSE,
ExpirationTS timestamp without time zone DEFAULT 'infinity',
FirstName varchar(512) NOT NULL DEFAULT '',
LastName varchar(512) NOT NULL DEFAULT '',
DisplayName varchar(1024) NOT NULL DEFAULT '',
Mail varchar(320) NOT NULL CHECK ( Mail <> '' ),
Retired boolean DEFAULT FALSE,
LastUpdate timestamp without time zone DEFAULT current_timestamp
);
And in Schema Users:
CREATE FUNCTION UpdateAccount(varchar(512),varchar(512),varchar(1024),varchar(320)) RETURNS boolean AS'
DECLARE
UserFirstName ALIAS FOR $1;
UserLastName ALIAS FOR $2;
UserDisplayName ALIAS FOR $3;
UserMail ALIAS FOR $4;
Row RECORD;
BEGIN
UPDATE Main.Accounts
SET FirstName = UserFirstName,
LastName = UserLastName,
DisplayName = UserDisplayName,
Mail = UserMail,
LastUpdate = DEFAULT
WHERE Active = ''True'' AND
Valid = ''True'' AND
Retired = ''False'' AND
ExpirationTS > now() AND
''user_''||UID = session_user;
IF NOT FOUND THEN
RETURN ''False''; -- No Accessible Account
END IF;
RETURN ''True''; -- All ok
END;
'LANGUAGE plpgsql STABLE STRICT SECURITY DEFINER;
User has no direct access on schema Main but has right to execute UpdateAccount.
So normally user can only modify FirstName, LastName, DisplayName, Mail for single record (''user_''||UID = session_user) of table Accounts.
My question is: if we imagine that input of UpdateAccount has no filtration or this filtration incorrect does exist any way to modify other then authorized parameters of Accounts table or records of other user? Is there any injection technique possible? Should I still do something like quote_literal() systematically on each parameters and reverse conversation each time? Or even replace UPDATE.. by EXECUTE ''Update... ?
Thank you in advance, TBP