Postgres Utils

clone_user_privileges.sql

CREATE OR REPLACE PROCEDURE clone_user_privileges(existing_user TEXT, new_user TEXT, new_password TEXT)
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    -- Crear el nuevo usuario con contraseña, manejando comillas adecuadamente
    BEGIN
        EXECUTE format('CREATE USER %I WITH PASSWORD %L', new_user, quote_literal(new_password));
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'Error creating user %: %', new_user, SQLERRM;
        RETURN;
    END;

    -- Clonar privilegios de base de datos
    FOR r IN (SELECT datname FROM pg_database WHERE datistemplate = false) LOOP
        BEGIN
            EXECUTE format('GRANT ALL PRIVILEGES ON DATABASE %I TO %I', r.datname, new_user);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Error granting privileges on database %I to %I: %', r.datname, new_user, SQLERRM;
        END;
    END LOOP;

    -- Clonar privilegios de esquema
    FOR r IN (SELECT nspname FROM pg_namespace WHERE nspname NOT IN ('information_schema', 'pg_catalog')) LOOP
        BEGIN
            EXECUTE format('GRANT ALL PRIVILEGES ON SCHEMA %I TO %I', r.nspname, new_user);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Error granting privileges on schema %I to %I: %', r.nspname, new_user, SQLERRM;
        END;
    END LOOP;

    -- Clonar privilegios de tabla
    FOR r IN (SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN ('information_schema', 'pg_catalog')) LOOP
        BEGIN
            EXECUTE format('GRANT ALL PRIVILEGES ON TABLE %I.%I TO %I', r.schemaname, r.tablename, new_user);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Error granting privileges on table %I.%I to %I: %', r.schemaname, r.tablename, new_user, SQLERRM;
        END;
    END LOOP;

    -- Clonar privilegios de secuencia
    FOR r IN (SELECT schemaname, sequencename FROM pg_sequences WHERE schemaname NOT IN ('information_schema', 'pg_catalog')) LOOP
        BEGIN
            EXECUTE format('GRANT ALL PRIVILEGES ON SEQUENCE %I.%I TO %I', r.schemaname, r.sequencename, new_user);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Error granting privileges on sequence %I.%I to %I: %', r.schemaname, r.sequencename, new_user, SQLERRM;
        END;
    END LOOP;

    -- Clonar privilegios de vista
    FOR r IN (SELECT schemaname, viewname FROM pg_views WHERE schemaname NOT IN ('information_schema', 'pg_catalog')) LOOP
        BEGIN
            EXECUTE format('GRANT ALL PRIVILEGES ON VIEW %I.%I TO %I', r.schemaname, r.viewname, new_user);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Error granting privileges on view %I.%I to %I: %', r.schemaname, r.viewname, new_user, SQLERRM;
        END;
    END LOOP;

    -- Clonar privilegios de función
    FOR r IN (SELECT n.nspname, p.proname
              FROM pg_proc p
              JOIN pg_namespace n ON p.pronamespace = n.oid
              WHERE n.nspname NOT IN ('information_schema', 'pg_catalog')) LOOP
        BEGIN
            EXECUTE format('GRANT ALL PRIVILEGES ON FUNCTION %I.%I TO %I', r.nspname, r.proname, new_user);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Error granting privileges on function %I.%I to %I: %', r.nspname, r.proname, new_user, SQLERRM;
        END;
    END LOOP;
END;
$$;

create_user_with_dml_privileges.sql

CREATE USER new_user WITH PASSWORD 'your_password';
GRANT CONNECT ON DATABASE db TO new_user;
GRANT USAGE ON SCHEMA public TO new_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO new_user;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO new_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO new_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO new_user;

drop_user_with_privileges.sql

CREATE OR REPLACE PROCEDURE drop_user_with_privileges(user_name TEXT)
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
    db RECORD;
    user_exists BOOLEAN;
BEGIN
    -- Verificar si el usuario existe
    SELECT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = user_name) INTO user_exists;

    IF NOT user_exists THEN
        RAISE NOTICE 'User % does not exist.', user_name;
        RETURN;
    END IF;

    -- Revocar todos los privilegios del usuario en todas las bases de datos
    FOR db IN (SELECT datname FROM pg_database WHERE datistemplate = false) LOOP
        BEGIN
            EXECUTE format('REVOKE ALL PRIVILEGES ON DATABASE %I FROM %I', db.datname, user_name);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Error revoking privileges on database %I for user %I: %', db.datname, user_name, SQLERRM;
        END;
    END LOOP;

    -- Revocar los privilegios predeterminados otorgados por el rol 'postgres'
    EXECUTE format('ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE ALL ON TABLES FROM %I', user_name);
    EXECUTE format('ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE ALL ON SEQUENCES FROM %I', user_name);
    EXECUTE format('ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM %I', user_name);

    -- Revocar todos los privilegios específicos en el esquema public
    EXECUTE format('REVOKE ALL PRIVILEGES ON SCHEMA public FROM %I', user_name);

    -- Revocar privilegios sobre todas las tablas en el esquema public
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
        BEGIN
            EXECUTE format('REVOKE ALL PRIVILEGES ON TABLE public.%I FROM %I', r.tablename, user_name);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Error revoking privileges on table %I', r.tablename;
        END;
    END LOOP;

    -- Revocar privilegios sobre todas las vistas en el esquema public
    FOR r IN (SELECT table_name FROM information_schema.views WHERE table_schema = 'public') LOOP
        BEGIN
            EXECUTE format('REVOKE ALL PRIVILEGES ON TABLE public.%I FROM %I', r.table_name, user_name);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Error revoking privileges on view %I', r.table_name;
        END;
    END LOOP;

    -- Revocar privilegios sobre todas las secuencias en el esquema public
    FOR r IN (SELECT relname FROM pg_class WHERE relkind = 'S' AND relnamespace = 'public'::regnamespace) LOOP
        BEGIN
            EXECUTE format('REVOKE ALL PRIVILEGES ON SEQUENCE public.%I FROM %I', r.relname, user_name);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Error revoking privileges on sequence %I', r.relname;
        END;
    END LOOP;

    -- Revocar privilegios sobre todos los esquemas
    FOR r IN (SELECT nspname FROM pg_namespace WHERE nspname NOT IN ('information_schema', 'pg_catalog')) LOOP
        BEGIN
            EXECUTE format('REVOKE ALL PRIVILEGES ON SCHEMA %I FROM %I', r.nspname, user_name);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Error revoking privileges on schema %I', r.nspname;
        END;
    END LOOP;

    -- Cambiar el propietario de las tablas a otro usuario si es necesario
    FOR r IN (SELECT tablename FROM pg_tables WHERE tableowner = user_name) LOOP
        BEGIN
            EXECUTE format('ALTER TABLE public.%I OWNER TO postgres', r.tablename);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Error changing owner of table %I', r.tablename;
        END;
    END LOOP;

    -- Cambiar el propietario de las secuencias a otro usuario si es necesario
    FOR r IN (SELECT relname FROM pg_class WHERE relkind = 'S' AND relnamespace = 'public'::regnamespace AND relowner = (SELECT oid FROM pg_roles WHERE rolname = user_name)) LOOP
        BEGIN
            EXECUTE format('ALTER SEQUENCE public.%I OWNER TO postgres', r.relname);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Error changing owner of sequence %I', r.relname;
        END;
    END LOOP;

    -- Cambiar el propietario de las funciones a otro usuario si es necesario
    FOR r IN (SELECT proname FROM pg_proc WHERE proowner = (SELECT oid FROM pg_roles WHERE rolname = user_name)) LOOP
        BEGIN
            EXECUTE format('ALTER FUNCTION public.%I OWNER TO postgres', r.proname);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Error changing owner of function %I', r.proname;
        END;
    END LOOP;

    -- Cambiar el propietario de las vistas a otro usuario si es necesario
    FOR r IN (SELECT relname FROM pg_class WHERE relkind = 'v' AND relnamespace = 'public'::regnamespace AND relowner = (SELECT oid FROM pg_roles WHERE rolname = user_name)) LOOP
        BEGIN
            EXECUTE format('ALTER VIEW public.%I OWNER TO postgres', r.relname);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Error changing owner of view %I', r.relname;
        END;
    END LOOP;

    -- Eliminar el usuario
    EXECUTE format('DROP USER %I', user_name);
END;
$$;