2.sql

application/sql

Download

\c postgres postgres
-- create nonpriviliged user
create user xxx;
grant all on schema public to xxx;

-- create a table, user xxx is allowed to drop it.
-- Simplest way is owning
create table x (u text, ts timestamp);
alter table x owner  to xxx;

--create trriger on login
CREATE OR REPLACE FUNCTION init_session()
 RETURNS event_trigger SECURITY DEFINER
LANGUAGE plpgsql AS
$$
BEGIN
INSERT INTO x VALUES (session_user, current_timestamp);
END;
$$;

CREATE EVENT TRIGGER init_session ON login EXECUTE FUNCTION init_session();

\c postgres xxx

-- xxx drops the table
drop table x;

--voila - nobody can connect to db 
\c postgres postgres
\c postgres xxx
\c postgres postgres